Constructing a view based on a fiscal year 3

[vcujackson]

Platform: MS SQL Server 2005

I am trying to construct an sql view based on a fiscal year starting with 7(July) and ending with 6(June). I will eventually use totals for a 4 year fiscal year. If the giftdate falls within the date range, I want to list the totals in the correct fiscal year. I want a total for pastdue,fy2008,2009,2010 and 2011

ie
Select sum(giftamount) as paymentsDue,giftkey
The output should look like this
pastdue $0.00 or amount
fy2008 $0.00 or amount
fy2009 $0.00 or amount
fy2010 $0.00 or amount
fy2011 $0.00 or amount

I am thinking I need a parameter based on a date range and several case statements

Any ideas?

 

[hmckillop]

It would be easier to output as seperate columns
e.g. output

Pastdue, FY2008, FY2009, FY2010, FY2011
0, 10.00, 11.12,0,0

SELECT 
sum(
CASE WHEN MyDatefield < '07-jul-2007' THEN giftamount else 0.0 END) as PastDue,
sum(
CASE WHEN MyDatefield < '07-jul-2008' and mydatefield >= '06-jun-2007' THEN giftamount else 0.0 END) as FY2008,
etc
FROM myTable
[code]

However does a fiscal year not require a full year, e.g. from july to july or june to june?



"I'm living so far beyond my income that we may almost be said to be living apart

 

[markros]

Something like (SQL Server 2005 and up)

with cte_dates as (select CASE WHEN myDateField< '07-jul-2007' THEN 'PastDate' WHEN myDateField < '07-jul-2008' and mydatefield >= '06-jun-2007' THEN 'FY2008' etc. END ) as FiscalYear, myDateField, myAmount from myTable) --added FY field

Now you can easily group.

 

[PDreyer]

Is this what you want?

[COLOR=blue]select[/color] [COLOR=red]'year'[/color]=[COLOR=#FF00FF]year[/color]([COLOR=#FF00FF]dateadd[/color](mm,-6,dt)), [COLOR=red]'amt'[/color]=sum(amt)
[COLOR=blue]from[/color] ([COLOR=blue]select[/color] 
[COLOR=green]-- test data start
[/color][COLOR=red]'20080701'[/color],1 union all [COLOR=blue]select[/color]
[COLOR=red]'20081231'[/color],2 union all [COLOR=blue]select[/color]
[COLOR=red]'20090630'[/color],4 union all [COLOR=blue]select[/color]
[COLOR=red]'20090701'[/color],10 union all [COLOR=blue]select[/color]
[COLOR=red]'20091231'[/color],20 union all [COLOR=blue]select[/color]
[COLOR=red]'20100630'[/color],40 union all [COLOR=blue]select[/color]
[COLOR=red]'20100701'[/color],100 union all [COLOR=blue]select[/color]
[COLOR=red]'20101231'[/color],200 union all [COLOR=blue]select[/color]
[COLOR=red]'20110630'[/color],400 union all [COLOR=blue]select[/color]
[COLOR=red]'20110701'[/color],1000)testdata(dt,amt)
[COLOR=green]-- test data end
[/color][COLOR=blue]group[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]year[/color]([COLOR=#FF00FF]dateadd[/color](mm,-6,dt))