Group by financial year - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by financial year

Author

Topic

dotinf
Starting Member

6 Posts

Posted - 2009-12-31 : 20:14:53

Hi guys, this is the first time I have attempted date functions in SQL. I have tried many ways to solve a little issue I have with no success, so I thought I'd go back to basics and ask for help.

I have a simple table with 2 columns, one a date column and one a number column. I am trying to group the results into previous financial years without knowing what the starting year is. The following is a simple select statement and as you can see it is very simple, but I have not had any success with grouping. I am using SQL Server 2005 and the financial years start on the 1st of July and finish on the 30th of June.


SELECT     ReadingDate, ReadingValue
FROM         tblReadings

Results:

ReadingDate ReadingValue
20/02/2006 20
8/04/2006 40
30/08/2006 40
10/01/2007 22
14/01/2007 2
16/04/2007 8
18/08/2007 12
20/12/2007 80
2/02/2008 68
2/04/2008 6
20/08/2008 76
8/12/2008 8
1/01/2009 2
4/04/2009 8
12/10/2009 16
12/12/2009 12

Thanks guys

Sachin.Nand

2937 Posts

Posted - 2010-01-01 : 05:14:33

set dateformat dmy
declare @tbl as table(value int,dates datetime)
insert into @tbl
select 20,'20/02/2006' union all
Select 40,'8/04/2006' union all
Select 40,'30/08/2006' union all
Select 22,'10/01/2007' union all
Select 2,'14/01/2007' union all
Select 8,'16/04/2007' union all
Select 12,'16/04/2007' union all
Select 80,'20/12/2007' union all
Select 68,'2/02/2008' union all
Select 6,'2/04/2008' union all
Select 76,'20/08/2008' union all
Select 8,'8/12/2008' union all
Select 2,'1/01/2009' union all
Select 8,'4/04/2009' union all
Select 16,'12/10/2009' union all
Select 12,'12/12/2009'
select * from @tbl

select convert(varchar,yr) + '-' + convert(varchar,yr+1) as FinancialYear,value from
(

select DATEPART(yy,dateadd(mm,-7,dates))as yr,SUM(value) as value from @tbl
group by DATEPART(yy,dateadd(mm,-7,dates))
)t

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources