August 31, 2012 at 8:53 am
Hi Everyone,
I need a quarterly amount from the below data. For instance,for the first 3 months monthlyamount need to be calculated(Sum) as a quarterly amount for the particular group.
CREATE TABLE [dbo].[test1](
Ayear smallint NOT NULL ,
AMonth smallint NOT NULL,
AGroup varchar(3) NOT NULL ,
MonthlyAmt nvarchar(80) NOT NULL
)
INSERT INTO Test (AYear,AMonth,AGroup,MonthlyAmt)
VALUES (2011,1,120,'-27657.06'),
(2011,2,120,'12849.02'),
(2011,3,120,'236701.20'),
(2011,4,120,'5615.37'),
(2011,5,120,'18864.09'),
(2011,6,120,'30267.3'),
(2011,7,120,'-23744.31'),
(2011,8,120,'10502.22'),
(2011,9,120,'-3435.67'),
(2011,10,120,'7514.13'),
(2011,11,120,'10859.31'),
(2011,12,120,'32141.2'),
(2012,1,120,'15666.94'),
(2012,2,120,'3096.88'),
(2012,3,120,'8285.64'),
EXPECTED RESULT
AyearAmonthAgroupQuarterlyAmount
2011 312021893.16
2011 612054746.76
2011 9120-16677.8
2011 1212050514.64
2012 312027049.46
Any help will be greatly appreciated.
August 31, 2012 at 9:06 am
I don't understand how you arrive at your expected result from the data you've provided for 2011 months 1,2 and 3.
The below matches all the other outputs (I'm assuming your MonthlyAmt column isn't really a nvarchar, or you can't actually perform a SUM):
SELECT Ayear, CEILING(AMONTH/3.0)*3 AMonth,Agroup, SUM(MonthlyAmt) QuarterlyAmount FROM test1
GROUP BY Ayear, CEILING(AMONTH/3.0)*3 ,Agroup
August 31, 2012 at 10:00 am
if I understand, then
select ayear, trim, AGroup, sum(cast(MonthlyAmt as money)) sumTrimester
from (
select *,
trim =
case
when AMonth between 1 and 3 then 1
when AMonth between 4 and 6 then 2
when AMonth between 7 and 9 then 3
when AMonth between 10 and 12 then 4
end
from test1
) as analitycs
group by
ayear, trim, AGroup
August 31, 2012 at 11:30 am
Create table. This will be very simple if AMonth is a date column. I made this change.
CREATE TABLE [dbo].[test1](
Ayear smallint NOT NULL ,
AMonth date NOT NULL,
AGroup varchar(3) NOT NULL ,
MonthlyAmt numeric(10,2) NOT NULL
)
Insert sample data.
INSERT INTO Test1 (AYear,AMonth,AGroup,MonthlyAmt)
VALUES (2011,'2011-01-01',120,'-27657.06'),
(2011,'2011-02-01',120,'12849.02'),
(2011,'2011-03-01',120,'236701.20'),
(2011,'2011-04-01',120,'5615.37'),
(2011,'2011-05-01',120,'18864.09'),
(2011,'2011-06-01',120,'30267.3'),
(2011,'2011-07-01',120,'-23744.31'),
(2011,'2011-08-01',120,'10502.22'),
(2011,'2011-09-01',120,'-3435.67'),
(2011,'2011-10-01',120,'7514.13'),
(2011,'2011-11-01',120,'10859.31'),
(2011,'2011-12-01',120,'32141.2'),
(2012,'2012-01-01',120,'15666.94'),
(2012,'2012-02-01',120,'3096.88'),
(2012,'2012-03-01',120,'8285.64')
Query:
;
WITH c
AS (
SELECT *
,QrtAmount = SUM(cast(MonthlyAmt AS INT)) OVER (
PARTITION BY AYear
,datepart(q, AMonth)
)
,RowNum = ROW_NUMBER() OVER (
PARTITION BY AYear
,datepart(q, AMonth) ORDER BY Month(AMonth) DESC
)
FROM [test1]
)
SELECT Ayear
,Amonth
,Agroup
,QrtAmount
FROM c
WHERE RowNum = 1
I would think very carefully before using the 'Order By desc" in the row_number function, but i used that just in case if you want to show quarters even when they don't have all three months.
Sam Vanga
http://SamuelVanga.com
August 31, 2012 at 12:18 pm
This is very similar to Howard's with a slight difference in the Quarter calculation and making the conversion for MonthlyAmt.
You really should reconsider your data types.
SELECTAYear,
(((AMonth - 1) / 3) + 1)* 3 AS AQuarter,
AGroup,
SUM( CAST( MonthlyAmt AS decimal(18,2))) AS QuarterlyAmount
FROM @test1
GROUP BY AYear,
(((AMonth - 1) / 3) + 1)* 3,
AGroup
August 31, 2012 at 1:20 pm
Thank you everyone. Queries passed successfully.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply