Multiple computations and grouping

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

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple computations and grouping

Author

Topic

rum23
Yak Posting Veteran

77 Posts

Posted - 2009-05-27 : 06:02:02

Currently, I have table with following info

PaidDate Amount ID
-----------------------------------------
4/15/2008 100.00 1
4/20/2008 250.00 2
05/05/2008 50.00 3
5/17/2008 130.00 4
09/20/2008 175.00 5
10/29/2009 200.00 6
11/28/2009 500.00 7
12/15/2009 600.00 8
01/13/2010 200.00 9
01/25/2010 300.00 10
03/29/2010 400.00 11

Now I want to get the data in the following format.

FinancialYear QuarterNumber TotalAmount
------------------------------------------------------
FY07/08 Q3 530.00(ID's: 1+2+3+4)
FY07/08 Q4 175.00(ID's: 5)
FY09/10 Q1 1300.00(ID's: 6+7+8)
FY09/10 Q2 900.00 (ID's: 9+10+11)

Any given financial Year starts on October 1st and ends on Sept 30th of the following year. (e.g.: 10/1/2008 to 9/30/2009). Each financial year has 4 quarters (Q1, Q2,Q3,Q4).
Q1 - Oct, Nov, Dec
Q2 - Jan, Feb, Mar
Q3 - Apr, May, June
Q4 - July, Aug, Sep

The PaidDate determines the Financial year and the month in the paid date determines the quarter.

I want to sum the Amounts based on Financial year and then the quarters. Is this possible to do in SQL?

Thanks!

rum

nishita_s
Yak Posting Veteran

61 Posts

Posted - 2009-05-27 : 06:45:07

SELECT CASE WHEN Month(paiddate)>=10

THEN cast(YEAR(paiddate)as varchar(4))+'/'+cast(YEAR(paiddate)+1as varchar(4))
WHEN Month(paiddate)<11
THEN cast(YEAR(paiddate)-1as varchar(4))+'/'+cast(YEAR(paiddate)as varchar(4))
end FinancialYear ,
CASE WHEN Month(paiddate)=10 OR Month(paiddate)=12 OR Month(paiddate)=11
THEN 'Q1'
WHEN Month(paiddate)=9 OR Month(paiddate)=8 OR Month(paiddate)=7 THEN 'Q2'
WHEN Month(paiddate)=6 OR Month(paiddate)=5 OR Month(paiddate)=4 THEN 'Q3'
WHEN Month(paiddate)=1 OR Month(paiddate)=2 OR Month(paiddate)=3 THEN 'Q4'END
as QuarterNumber ,
SUM(ISNULL(Amount,0))TotalAmount
FROM #tmp
GROUP BY CASE WHEN Month(paiddate)>=10

THEN cast(YEAR(paiddate)as varchar(4))+'/'+cast(YEAR(paiddate)+1as varchar(4))
WHEN Month(paiddate)<11
THEN cast(YEAR(paiddate)-1as varchar(4))+'/'+cast(YEAR(paiddate)as varchar(4))
end ,
CASE WHEN Month(paiddate)=10 OR Month(paiddate)=12 OR Month(paiddate)=11
THEN 'Q1'
WHEN Month(paiddate)=9 OR Month(paiddate)=8 OR Month(paiddate)=7 THEN 'Q2'
WHEN Month(paiddate)=6 OR Month(paiddate)=5 OR Month(paiddate)=4 THEN 'Q3'
WHEN Month(paiddate)=1 OR Month(paiddate)=2 OR Month(paiddate)=3 THEN 'Q4'END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 06:50:02

[code]DECLARE @Sample TABLE
(
PaidDate DATETIME,
Amount MONEY,
ID INT
)

INSERT @Sample
SELECT '04/15/2008', 100.00, 1 UNION ALL
SELECT '04/20/2008', 250.00, 2 UNION ALL
SELECT '05/05/2008', 50.00, 3 UNION ALL
SELECT '05/17/2008', 130.00, 4 UNION ALL
SELECT '09/20/2008', 175.00, 5 UNION ALL
SELECT '10/29/2009', 200.00, 6 UNION ALL
SELECT '11/28/2009', 500.00, 7 UNION ALL
SELECT '12/15/2009', 600.00, 8 UNION ALL
SELECT '01/13/2010', 200.00, 9 UNION ALL
SELECT '01/25/2010', 300.00, 10 UNION ALL
SELECT '03/29/2010', 400.00, 11

SELECT 'FY' + y1 + '/' + y2 AS FinancialYear,
QuarterNumber,
SUM(Amount) AS TotalAmount
FROM (
SELECT RIGHT(DATEPART(YEAR, DATEADD(MONTH, -9, PaidDate)), 2) AS y1,
RIGHT(DATEPART(YEAR, DATEADD(MONTH, +3, PaidDate)), 2) AS y2,
DATEPART(QUARTER, DATEADD(MONTH, 3, PaidDate)) AS QuarterNumber,
Amount
FROM @Sample
) AS d
GROUP BY y1,
y2,
QuarterNumber
ORDER BY y1,
y2,
QuarterNumber[/code]

E 12°55'05.63"
N 56°04'39.26"

rum23
Yak Posting Veteran

77 Posts

Posted - 2009-05-28 : 01:35:47

Peso, your solution works like a charm!

Thanks a lot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources