how to group by academic/user-defined year (1 Viewer)

[john33john33]

[solved]how to group by academic/user-defined year

This is further application on: htt p://www[dot]access-programmers[dot]co[dot]uk/forums/showthread.php?t=279458

A quicker look, here is a query:

SELECT ElectricityUser.UserName, Year(ElectricityUsage.[Occurrence]) AS [Year], Round(Sum(ElectricityUsage.amount*(SELECT TOP 1 CO2Emission from ElectricityEmissionFactor
                    WHERE year(ElectricityEmissionFactor.[Occurrence]) <= year(Occurrence) order by ElectricityEmissionFactor.[Occurrence] desc
                    )),0) AS CO2
FROM ElectricityUser INNER JOIN ElectricityUsage ON ElectricityUser.UserID = ElectricityUsage.UserID
WHERE (((ElectricityUser.UserName)<>"Whole Campus"))
GROUP BY ElectricityUser.UserName, Year(ElectricityUsage.Occurrence);

And the result is grouped by normal year, Year(ElectricityUsage.Occurrence).
How to make it group by academic year, let say from july-june.
Then it would be 2011/2012, 2012/2013...

Thanks

 

[plog]

You should create a function that takes a date and then returns the Academic Year. If you search this forum for 'Fiscal Year' you should find many posts that deal with essentially the same thing.

Then once you are able to convert a date to an Academic Year you can group on that value.

 

[john33john33]

Thanks for giving such a important key word.
Considering doing it in a query only, is it possible?
Tried using Year(DateAdd("m", -6, ElectricityUsage.[RecordDate])) to change the date.
But the problem is ElectricityUsage.amount*CO2Factor must be correctly referenced by the same year.

A imaginary version would be:

SELECT ElectricityUser.UserName, Year(ElectricityUsage.[RecordDate]) AS [Academic Year], Round(Sum(ElectricityUsage.amount*                                         (SELECT TOP 1 CO2Emission from ElectricityEmissionFactor                     
WHERE year(ElectricityEmissionFactor.[FactorStartDate]) <= year(RecordDate) order by ElectricityEmissionFactor.[FactorStartDate] desc                     )),0) AS CO2
FROM ElectricityUser INNER JOIN ElectricityUsage ON ElectricityUser.UserID = ElectricityUsage.UserID
GROUP BY ElectricityUser.UserName, Year(DateAdd("m", -6, ElectricityUsage.[RecordDate])), Year(ElectricityUsage.[RecordDate]);

 

[plog]

But the problem is ElectricityUsage.amount*CO2Factor must be correctly referenced by the same year.

I don't see your issue. Perhaps demonstrate it with data.

 

[john33john33]

Let say
ElectricityEmissionFactor
Occurrence Emission factor
1/1/2011 0.1
1/1/2012 0.2

ElectricityUsage
Occurrence Amount
1/3/2012 10000

In this case, 10000 should be multiplied by 0.2 since it is the factor for the year.
But to show the data in academic year June-July, it should be in the 2011 year group.

 

[namliam]

Easiest way to do something like this is to use a dimension table which holds all possible dates and a mapping to your fiscal/academic year.

A little harder make a function to calculate the proper year for you
Something like:

dateserial(year(dateadd("M", -6, date())), 1,1)

 

[john33john33]

Easiest way to do something like this is to use a dimension table which holds all possible dates and a mapping to your fiscal/academic year.

A little harder make a function to calculate the proper year for you
Something like:

dateserial(year(dateadd("M", -6, date())), 1,1)

I want to have something like this:

SELECT ElectricityUser.UserName, Year(ElectricityUsage.[RecordDate]) AS [Academic Year], Round(Sum(ElectricityUsage.amount*
(SELECT TOP 1 CO2Emission from ElectricityEmissionFactor                     WHERE year(ElectricityEmissionFactor.[FactorStartDate]) <= year(RecordDate) order by ElectricityEmissionFactor.[FactorStartDate] desc                     )),0) AS CO2
FROM ElectricityUser INNER JOIN ElectricityUsage ON ElectricityUser.UserID = ElectricityUsage.UserID
GROUP BY ElectricityUser.UserName, dateserial(year(dateadd("M", -6, ElectricityUsage.[RecordDate])), 1,1);

However, because i had to group by ElectricityUsage.[RecordDate]

SELECT ElectricityUser.UserName, Year(ElectricityUsage.[RecordDate]) AS [Academic Year], Round(Sum(ElectricityUsage.amount*
(SELECT TOP 1 CO2Emission from ElectricityEmissionFactor                     WHERE year(ElectricityEmissionFactor.[FactorStartDate]) <= year(RecordDate) order by ElectricityEmissionFactor.[FactorStartDate] desc                     )),0) AS CO2
FROM ElectricityUser INNER JOIN ElectricityUsage ON ElectricityUser.UserID = ElectricityUsage.UserID
GROUP BY ElectricityUser.UserName, dateserial(year(dateadd("M", -6, ElectricityUsage.[RecordDate])), 1,1), ElectricityUsage.[RecordDate];

 

[namliam]

What is with people and splashing unreadable code :banghead:
Remember we dont know your database by heart...

Why would you group by recorddate? That doesnt much make sence....

Wouldnt you want something like:

SELECT ElectricityUser.UserName
     , year(dateadd("M", -6, ElectricityUsage.[RecordDate])) AS [Academic Year]
     , Round(Sum(ElectricityUsage.amount* CO2Emission     ),0) AS CO2
FROM ElectricityUser INNER 
JOIN ElectricityUsage ON ElectricityUser.UserID = ElectricityUsage.UserID
join ElectricityEmissionFactor on dateserial(year(dateadd("M", -6, ElectricityUsage.[RecordDate])), 1,1) = ElectricityEmissionFactor.[FactorStartDate]
GROUP BY ElectricityUser.UserName
       , year(dateadd("M", -6, ElectricityUsage.[RecordDate]))
;

Or better yet, in your EmissionFactor table add two columns "StartRealDate" and "EndRealDate" which will work from 1/jul/2014 thru 30/jun/2015
and you can simply join on those fields without all the trickery of the date.

 

[john33john33]

What is with people and splashing unreadable code :banghead:
Remember we dont know your database by heart...

Why would you group by recorddate? That doesnt much make sence....

Wouldnt you want something like:

SELECT ElectricityUser.UserName
     , year(dateadd("M", -6, ElectricityUsage.[RecordDate])) AS [Academic Year]
     , Round(Sum(ElectricityUsage.amount* CO2Emission     ),0) AS CO2
FROM ElectricityUser INNER 
JOIN ElectricityUsage ON ElectricityUser.UserID = ElectricityUsage.UserID
join ElectricityEmissionFactor on dateserial(year(dateadd("M", -6, ElectricityUsage.[RecordDate])), 1,1) = ElectricityEmissionFactor.[FactorStartDate]
GROUP BY ElectricityUser.UserName
       , year(dateadd("M", -6, ElectricityUsage.[RecordDate]))
;

Or better yet, in your EmissionFactor table add two columns "StartRealDate" and "EndRealDate" which will work from 1/jul/2014 thru 30/jun/2015
and you can simply join on those fields without all the trickery of the date.

sorry, i really didn't express myself well.
Let me re deliver the problem.

ElectricityUsage:
UserID Time Amount
1 1/7/2010 23230
1 8/10/2011 34340
1 8/1/2012 34300
1 2/3/2012 43430
1 4/2/2013 43560
1 3/2/2014 44540
2 3/7/2014 44000
DD/MM/YYYY

ElectricityEmissionFactor:
Time CO2Emission
1/1/2010 0.5
1/1/2011 0.55
1/1/2012 0.56
1/1/2013 0.57


Intended outcome is:
ID Year CO2
1 2010 11615
1 2011 62415
1 2012 24829.2
1 2013 25387.8
2 2014 25080

I guess the intended outcome is likely to be your answer.
But it says missing operator.
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=59421&d=1437635643
Together with the database

 

[namliam]

Try this,

SELECT ElectricityUser.UserID
     , ElectricityUser.UserName
     , Year(DateAdd("m",-6,[electricityUsage].[Occurrence])) AS Expr1
     , Sum([amount]*[co2emission]) AS Expr3
FROM ElectricityEmissionFactor
   , ElectricityUser 
INNER JOIN ElectricityUsage ON ElectricityUser.UserID = ElectricityUsage.UserID
WHERE (((DateSerial(Year(DateAdd("m",-6,[electricityUsage].[Occurrence])),1,1))=[ElectricityEmissionFactor].[Occurrence]))
GROUP BY ElectricityUser.UserID
       , ElectricityUser.UserName
       , Year(DateAdd("m",-6,[electricityUsage].[Occurrence]));

 

[john33john33]

Try this,

SELECT ElectricityUser.UserID
     , ElectricityUser.UserName
     , Year(DateAdd("m",-6,[electricityUsage].[Occurrence])) AS Expr1
     , Sum([amount]*[co2emission]) AS Expr3
FROM ElectricityEmissionFactor
   , ElectricityUser 
INNER JOIN ElectricityUsage ON ElectricityUser.UserID = ElectricityUsage.UserID
WHERE (((DateSerial(Year(DateAdd("m",-6,[electricityUsage].[Occurrence])),1,1))=[ElectricityEmissionFactor].[Occurrence]))
GROUP BY ElectricityUser.UserID
       , ElectricityUser.UserName
       , Year(DateAdd("m",-6,[electricityUsage].[Occurrence]));

Thanks for the supportive comment.
But a issue is the wrongly referencing electricityUsage.amount to ElectricityEmissionFactor.co2emission.

Sum([amount]*[co2emission]) AS Expr3

For example amount within JUL/2010 to JUN/2011 is multiplied by co2emission of 2010.
The intended way is amount within JUL/2010 to DEC/2010 is multiplied by co2emission of 2010 while amount within JAN/2011 to JUN/2011 is multiplied by co2emission of 2011. Then the above is sum up to represent Year 2010.

 

[namliam]

The intended way is amount within JUL/2010 to DEC/2010 is multiplied by co2emission of 2010 while amount within JAN/2011 to JUN/2011 is multiplied by co2emission of 2011. Then the above is sum up to represent Year 2010.

Well simply removing some characters from the sql should fix that....

SELECT ElectricityUser.UserID
     , ElectricityUser.UserName
     , Year(DateAdd("m",-6,[electricityUsage].[Occurrence])) AS Expr1
     , Sum([amount]*[co2emission]) AS Expr3
FROM ElectricityEmissionFactor
   , ElectricityUser 
INNER JOIN ElectricityUsage ON ElectricityUser.UserID = ElectricityUsage.UserID
WHERE (((DateSerial(Year([electricityUsage].[Occurrence]),1,1))=[ElectricityEmissionFactor].[Occurrence]))
GROUP BY ElectricityUser.UserID
       , ElectricityUser.UserName
       , Year(DateAdd("m",-6,[electricityUsage].[Occurrence]));

 

[john33john33]

Just find out i missed to state one important point
The query #12 works fine in grouping by academic year.

But if you look at my query at first:

Sum(ElectricityUsage.amount*(SELECT TOP 1 CO2Emission from ElectricityEmissionFactor
                    WHERE year(ElectricityEmissionFactor.[Occurrence]) <= year(Occurrence) order by ElectricityEmissionFactor.[Occurrence] desc

This is done because for each electricity record in any time, there may not be corresponding ElectricityEmissionFactor.

Let say:
ElectricityUsage:
UserID Occurence Amount
1 1/7/2010 23230
1 8/10/2011 34340
1 4/2/2013 43560
1 3/2/2014 44540

ElectricityEmissionFactor:
Occurence CO2Emission
1/1/2010 0.5
1/1/2012 0.56
1/1/2013 0.57

23230*0.5 + 34340*0.5 + 43560*0.57 + 44540*0.57
The concept is if there is Factor in that year, reference to it.
If not, reference to the factor of nearest smaller year.
#12 would show no output if there is not a ElectricityEmissionFactor

I tried to alter the query like this:

SELECT ElectricityUser.UserID, ElectricityUser.UserName, Year(DateAdd("m",-6,[electricityUsage].[RecordDate])) AS [Year], 
Sum([amount]*
(
SELECT TOP 1 CO2Emission from ElectricityEmissionFactor
                    WHERE year(ElectricityEmissionFactor.[RecordDate]) <= year(RecordDate) order by ElectricityEmissionFactor.[FactorStartDate] desc
)
) AS CO2
FROM ElectricityEmissionFactor, ElectricityUser INNER JOIN ElectricityUsage ON ElectricityUser.UserID = ElectricityUsage.UserID
WHERE DateSerial(Year(DateAdd("m",-6,[electricityUsage].[RecordDate])),1,1)=[ElectricityEmissionFactor].[FactorStartDate] AND (ElectricityUser.UserName)<>"Whole Campus"
GROUP BY ElectricityUser.UserID, ElectricityUser.UserName, Year(DateAdd("m",-6,[electricityUsage].[RecordDate]));

But then I had to also group by Occurrence.
The above query not include occurrence to aggregate function
The result would be incorrect.

 

[namliam]

#12 would show no output if there is not a ElectricityEmissionFactor

Yes obviously, best, simplest, most controlable and constant way of fixing this is to .... actually have a figure for each year.... i.e. add 2011 in your above example, or add 2014 in your test database.

If you feel comfertable doing a fuzzy match, which I usually try to prevent... because NO figure is much more pronounced than a "mismatch" i.e. in your test database 2014 and 2015 are missing... the may be on purpose, but may also be an ommision not showing the data makes this painfully clear...

But if you like the fuzzy match...
make this query qryFuzzyYearMatch

SELECT DateSerial(Year(DateAdd("m",-6,[electricityUsage].[Occurrence])),1,1) AS UsageYear, Max(ElectricityEmissionFactor.Occurrence) AS MaxVanOccurrence
FROM ElectricityUsage, ElectricityEmissionFactor
WHERE (((ElectricityEmissionFactor.Occurrence)<=[electricityUsage].[Occurrence]))
GROUP BY DateSerial(Year(DateAdd("m",-6,[electricityUsage].[Occurrence])),1,1);

Now adjust the final query from #12 to

SELECT ElectricityUser.UserID
     , ElectricityUser.UserName
     , Year(DateAdd("m",-6,[electricityUsage].[Occurrence])) AS Expr1
     , Sum([amount]*[co2emission]) AS Expr3
FROM (((ElectricityUser 
INNER JOIN ElectricityUsage ON ElectricityUser.UserID = ElectricityUsage.UserID)
INNER JOIN qryFuzzyYearMatch on DateSerial(Year([electricityUsage].[Occurrence]),1,1) = qryFuzzyYearMatch.usageYear)
Inner join ElectricityEmissionFactor on qryFuzzyYearMatch.MaxVanOccurrence = [ElectricityEmissionFactor].[Occurrence])
GROUP BY ElectricityUser.UserID
       , ElectricityUser.UserName
       , Year(DateAdd("m",-6,[electricityUsage].[Occurrence]));

 

[john33john33]

best, simplest, most controlable and constant way of fixing this is to ....actually have a figure for each year.... i.e. add 2011 in your above example, or add 2014 in your test database.

I would like to do that as well as i think that would be a relational database supposed to be.
But I can't control the data.

 

[namliam]

Control the controler of the data

Did you try my new query(s)?

 

[john33john33]

Control the controler of the data

Did you try my new query(s)?

I tried and made correction.
new qryFuzzyYearMatch

SELECT DateSerial(Year([electricityUsage].[Occurrence]),1,1) AS UsageYear, Max(ElectricityEmissionFactor.Occurrence) AS MaxVanOccurrence
FROM ElectricityUsage, ElectricityEmissionFactor
WHERE (((ElectricityEmissionFactor.Occurrence)<=[electricityUsage].[Occurrence]))
GROUP BY DateSerial(Year([electricityUsage].[Occurrence]),1,1);

The change is because amount * co2emission is absolute referencing. No need to use dateadd to adjust the date.