Dear Lazyweb: SQL help - fiscal year?
Dear Lazyweb:
I can barely understand SQL, so please bear with me; I've researched the forums and found some close answers, but I'm too inexperienced to convert them to my needs. :(
I have a number of queries in Access 2007 which group by Year, and I'd like them to group by fiscal year instead (July-June). I can recognize that this is the section of the SQL that needs help:
My query’s SQL says:
GROUP BY Format$([CCU 24hr Totals].Date,'yyyy mm'), Year([CCU 24hr Totals].Date)*12+DatePart('m',[CCU 24hr Totals].Date)-1;
My SQL skills are not very good. But it seems to me that somewhere in the DatePart expression, or maybe the 12+, we could make our -6 adjustment? I tried it and didn’t see any change in the grouping on the report.
This is what the intertoobs told me, but I don't know how to plug it in and make it apply to fiscal year:
http://weblogs.sqlteam.com/jeffs/archive/2007/09/10/group-by-month-sql.aspx
"5. GROUP BY dateadd(month, datediff(month, 0, SomeDate),0) <-- Recommended
This technique will "round" your date to the first day of the month; thus, if you GROUP on those dates, you are grouping on months. This is nice because it combines the year and month together into one column for easy sorting and comparing and joining, if necessary. It also keeps your data as a true DATETIME value so that you can format it any way you'd like at your client, without the need to concatenate or convert to a DATETIME to present the months like "Jan-2007" or whatever you need. I strongly recommend this technique for all of these reasons and think you will find that overall it is the most flexible and easiest to work with."
Halp, please?
I can barely understand SQL, so please bear with me; I've researched the forums and found some close answers, but I'm too inexperienced to convert them to my needs. :(
I have a number of queries in Access 2007 which group by Year, and I'd like them to group by fiscal year instead (July-June). I can recognize that this is the section of the SQL that needs help:
My query’s SQL says:
GROUP BY Format$([CCU 24hr Totals].Date,'yyyy mm'), Year([CCU 24hr Totals].Date)*12+DatePart('m',[CCU 24hr Totals].Date)-1;
My SQL skills are not very good. But it seems to me that somewhere in the DatePart expression, or maybe the 12+, we could make our -6 adjustment? I tried it and didn’t see any change in the grouping on the report.
This is what the intertoobs told me, but I don't know how to plug it in and make it apply to fiscal year:
http://weblogs.sqlteam.com/jeffs/archive/2007/09/10/group-by-month-sql.aspx
"5. GROUP BY dateadd(month, datediff(month, 0, SomeDate),0) <-- Recommended
This technique will "round" your date to the first day of the month; thus, if you GROUP on those dates, you are grouping on months. This is nice because it combines the year and month together into one column for easy sorting and comparing and joining, if necessary. It also keeps your data as a true DATETIME value so that you can format it any way you'd like at your client, without the need to concatenate or convert to a DATETIME to present the months like "Jan-2007" or whatever you need. I strongly recommend this technique for all of these reasons and think you will find that overall it is the most flexible and easiest to work with."
Halp, please?