Author 
Topic 

Vack
Constraint Violating Yak Guru
USA
496 Posts 
Posted  09/18/2013 : 14:48:55

I have the following script that calculates Sales by month and current year.
We run a Fiscal year from April 1st thru March 31st.
So April 2012 sales are considered Fiscal Year 2013.
Is there a way I can alter this script to get Fiscal Year Totals?
select ClassificationId, YEAR(inv_dt) as Year, cus_no,
isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr',
isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May',
isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun',
isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',
isnull(sum(case when month(inv_dt) = 8 then salesamt end),0) as 'Aug',
isnull(sum(case when month(inv_dt) = 9 then salesamt end),0) as 'Sept',
isnull(sum(case when month(inv_dt) = 10 then salesamt end),0) as 'Oct',
isnull(sum(case when month(inv_dt) = 11 then salesamt end),0) as 'Nov',
isnull(sum(case when month(inv_dt) = 12 then salesamt end),0) as 'Dec',
isnull(sum(case when month(inv_dt) = 1 then salesamt end),0) as 'Jan',
isnull(sum(case when month(inv_dt) = 2 then salesamt end),0) as 'Feb',
isnull(sum(case when month(inv_dt) = 3 then salesamt end),0) as 'Mar'
from QIVOEHDRHSTcR
group by ClassificationId, YEAR(inv_dt), cus_no
Data returned looks like the following.
ClassificationID Year Cus_no Apr May June ....
100 2012 100 $23 $30 $400
100 2013 100 $40 $45 $600
What I would need is anything greater than or equal to April to show in the next years row. 

Lamprey
Flowing Fount of Yak Knowledge
4614 Posts 

SwePeso
Patron Saint of Lost Yaks
Sweden
30212 Posts 
Posted  09/18/2013 : 15:06:19

 Prepare sample data
DECLARE @Sample TABLE
(
theDate DATETIME NOT NULL,
theAmount INT NOT NULL
);
INSERT @Sample
(
theDate,
theAmount
)
VALUES ('20120311', 1203),
('20120411', 1204),
('20120511', 1205),
('20120611', 1206),
('20120711', 1207),
('20120811', 1208),
('20120911', 1209),
('20121011', 1210),
('20121111', 1211),
('20121211', 1212),
('20130111', 1301),
('20130211', 1302),
('20130311', 1303),
('20130411', 1304);
 SwePeso
WITH cteSource(theFiscalMonth, theMonth, theAmount)
AS (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '18990401', theDate), '19000101') AS theFiscalMonth,
DATEPART(MONTH, theDate) AS theMonth,
theAmount
FROM @Sample
)
SELECT DATEPART(YEAR, theFiscalMonth) AS FiscalYear,
SUM(theAmount) AS Total,
SUM(CASE WHEN theMonth = 4 THEN theAmount ELSE 0 END) AS April,
SUM(CASE WHEN theMonth = 5 THEN theAmount ELSE 0 END) AS May,
SUM(CASE WHEN theMonth = 6 THEN theAmount ELSE 0 END) AS June,
SUM(CASE WHEN theMonth = 7 THEN theAmount ELSE 0 END) AS July,
SUM(CASE WHEN theMonth = 8 THEN theAmount ELSE 0 END) AS August,
SUM(CASE WHEN theMonth = 9 THEN theAmount ELSE 0 END) AS September,
SUM(CASE WHEN theMonth = 10 THEN theAmount ELSE 0 END) AS October,
SUM(CASE WHEN theMonth = 11 THEN theAmount ELSE 0 END) AS November,
SUM(CASE WHEN theMonth = 12 THEN theAmount ELSE 0 END) AS December,
SUM(CASE WHEN theMonth = 1 THEN theAmount ELSE 0 END) AS January,
SUM(CASE WHEN theMonth = 2 THEN theAmount ELSE 0 END) AS February,
SUM(CASE WHEN theMonth = 3 THEN theAmount ELSE 0 END) AS March
FROM cteSource
GROUP BY DATEPART(YEAR, theFiscalMonth);
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA 



Topic 


