Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mr. P
Starting Member
2 Posts |
 Posted - 2004-09-28 : 01:50:07
|
| Hi,Im wondering if anyone has come across grouping by a financial year. I guess the problem comes where i want to group by a range - ie. GROUP BY (WHERE DATE BETWEEN '2003/07/01' AND '2004/06/30').Any help would be appreciated.Phil. |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-28 : 02:00:39
|
| how about using datediff? check BOL for a more info |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-09-28 : 08:58:49
|
| one way to do it is:group by Year(Date) + case when month(Date) < 7 then -1 else 0 endthat expression will take the Year of the date, then subtract one from it if it is between 1/1 and 6/30 -- effectively grouping 7/1/2003 - 6/30/2004 into the value 2003, 7/1/2004-6/30/2005 into the value 2004, etc.- Jeff |
|
|
|
Mr. P
Starting Member
2 Posts |
Posted - 2004-09-28 : 19:13:21
|
| I found out an easy way which works! Just use DateAdd, to add 6 months in the GROUP BY. ie. basically convert standard months to Financial year months.Jeff, your solution was the way i was thinking.. but just couldnt get to work. Looks like your solution would work too.Thanks. |
|
|
|
|
|
|
Grouping By Financial Year