# Query help

• 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.

• 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`

• 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`

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!
• 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.

• 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`

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• Thank you everyone. Queries passed successfully.

Viewing 6 posts - 1 through 5 (of 5 total)