January 15, 2009 at 2:20 pm
Hi there,
Stupid question of the moment but I was always one of these report wizard kind of guys who let the app do the subtotaling for me.
I have a SQL statement:
SELECTPeriod = CAST(YEAR(AsOfDate) AS VARCHAR(4)) +'-'
+ CASE WHEN MONTH(AsOfDate) IN ( 1, 2, 3) THEN '1'
WHEN MONTH(AsOfDate) IN ( 4, 5, 6) THEN '2'
WHEN MONTH(AsOfDate) IN ( 7, 8, 9) THEN '3'
WHEN MONTH(AsOfDate) IN (10, 11, 12) THEN '4' END,
Col2,
Col3,
SumCol4,
SumCol5,
SumCol6,
SumCol7
FROM#tblTemp
That will effectively return me
1990-115413263.61666671.80.04328269478048990.06658876120075360.002793424892049960.00429757675699993
1990-221189563.79333331.880.04349482087289890.06691510903522910.01767183432299990.0271874374199998
1990-319888155.27333332.60.04148062508847450.06381634628996080.01617368238199990.0248825882799999
1990-421974914.032.610.03923124289839440.06035575830522210.02561361884380.039405567452
1991-123334701.34333332.640.04281097197378920.06586303380582950.01347911824740010.0207371049960001
1991-221424236.372.630.04272554763300960.06573161174309170.01122681047019990.0172720161079998
1991-321123078.78333332.640.04279627472096950.06584042264764540.02894167781440.0445256581760001
1991-421712662.43333332.660.04284149451454120.06590999156083250.03019950552190.046460777726
What I am trying to do is get a SUM on columns 4-7 by the year
so I might come back with
1990-1 data...
1990-2 data...
1990-3 data...
1990-4 data...
--1990-- columns 4-7 summed for 1990
1991-1 data...
1991-2 data...
1991-3 data...
1991-4 data...
--1991-- columns 4-7 summed for 1991
I've gotten close to it but the GROUPING BY statement is not giving me what I expect. I think its because of how I am doing the case statement to return 'Period'.
any help would be appreciated.
January 16, 2009 at 1:58 am
January 16, 2009 at 11:21 am
I'll play with it some more, not giving me the breakout im looking for. I need to figure out how to return a different value for 'period' when grouping on year
thanks
EDIT:
I needed to use a derived table to make life a little easier, then it was just a matter of what/when to show things.
[Code]
SELECTPeriod,
Col2 = CASE G1 WHEN 0 THEN CASE G2 WHEN 1 THEN NULL ELSE Col2 END END,
Col3 = CASE G1 WHEN 0 THEN CASE G2 WHEN 1 THEN NULL ELSE Col3 END END,
SumCol4,
SumCol5,
SumCol6,
SumCol7
FROM(
SELECTDISTINCT
G1 = GROUPING(CAST(YEAR(AsOfDate) AS VARCHAR(4))),
G2 = GROUPING(CAST(YEAR(AsOfDate) AS VARCHAR(4)) +'-'
+ CASE WHEN MONTH(AsOfDate) IN ( 1, 2, 3) THEN '1'
WHEN MONTH(AsOfDate) IN ( 4, 5, 6) THEN '2'
WHEN MONTH(AsOfDate) IN ( 7, 8, 9) THEN '3'
WHEN MONTH(AsOfDate) IN (10, 11, 12) THEN '4' END),
Period = CASE GROUPING(CAST(YEAR(AsOfDate) AS VARCHAR(4)) +'-'
+ CASE WHEN MONTH(AsOfDate) IN ( 1, 2, 3) THEN '1'
WHEN MONTH(AsOfDate) IN ( 4, 5, 6) THEN '2'
WHEN MONTH(AsOfDate) IN ( 7, 8, 9) THEN '3'
WHEN MONTH(AsOfDate) IN (10, 11, 12) THEN '4' END) WHEN 1 THEN CAST(YEAR(AsOfDate) AS VARCHAR(4)) ELSE
CAST(YEAR(AsOfDate) AS VARCHAR(4)) +' Sums'
+ CASE WHEN MONTH(AsOfDate) IN ( 1, 2, 3) THEN '1'
WHEN MONTH(AsOfDate) IN ( 4, 5, 6) THEN '2'
WHEN MONTH(AsOfDate) IN ( 7, 8, 9) THEN '3'
WHEN MONTH(AsOfDate) IN (10, 11, 12) THEN '4' END END,
Col2= MIN(Col2),
Col3= MIN(Col3),
SumCol4= SUM(SumCol4),
SumCol5= SUM(SumCol5),
SumCol6= SUM(SumCol6),
SumCol7= SUM(SumCol7)
FROM #tblTemp
GROUP BY CAST(YEAR(AsOfDate) AS VARCHAR(4)),
CAST(YEAR(AsOfDate) AS VARCHAR(4)) +'-'
+ CASE WHEN MONTH(AsOfDate) IN ( 1, 2, 3) THEN '1'
WHEN MONTH(AsOfDate) IN ( 4, 5, 6) THEN '2'
WHEN MONTH(AsOfDate) IN ( 7, 8, 9) THEN '3'
WHEN MONTH(AsOfDate) IN (10, 11, 12) THEN '4' END
,SumCol4
,SumCol5
,SumCol6
,SumCol7
WITH ROLLUP
)aa
WHERE Period IS NOT NULL
ORDER BY Period
[/code]
results:
1990 SumsNULLNULL481216
1990-1100101234
1990-2100101234
1990-3100101234
1990-4100101234
1991 SumsNULLNULL20242832
1991-1200125678
1991-2200125678
1991-3200125678
1991-4200125678
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply