GROUP BY MONTH+YEAR WITH subtotals and maintotal

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 2000 Forums

SQL Server Development (2000)

GROUP BY MONTH+YEAR WITH subtotals and maintotal

Author

Topic

Noulouk
Starting Member

2 Posts

Posted - 2004-12-23 : 17:30:30

Hello everybody,

Here is my table:
myTable (UserId uniqueidentifier,ActivityId uniqueidentifier PK , ActivityType, Amount, ActivityDate)

the sample output I'd like to get:
total previous months 4312.00 (for example)
UserId1,ActivityId1,Type1,200.00,12/01/2004
UserId1,ActivityId2,Type2,100.00,12/05/2004
UserId1,ActivityId3,Type1,100.00,12/15/2004
subtotal month 2004/dec 400.00
UserId1,ActivityId18,Type1,800.00,01/15/2005
UserId1,ActivityId32,Type4,100.00,01/30/2005
subtotal month 2005/jan 900.00
...
subtotal month 2005/oct 300.00
total 2004/dec to 2005/oct 1600.00
total all months 10500.00

I try to do a GROUP BY CONVERT(nvarchar, DATEPART(year, ActivityDate)) + N'/' + CONVERT(nvarchar, DATEPART(month, ActivityDate)) AS YearMonthGroup,ActivityId,...,ActivityDate WITH ROLLUP
But I can't get what I want.

Thanks in advance for helping me.

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-12-23 : 20:35:15

Use the Case Statement to Evaluate the Month Year in a separate column and include this in the group by that should do the trick.

Noulouk
Starting Member

2 Posts

Posted - 2004-12-24 : 05:48:24

Thanks clark,
could you help me more because I get too much data WITH ROLLUP.
I mean I get all subtotals for YearMonthGroup,ActivityId,...,ActivityDate.
I only need rows from current month and subtotal for this current month.
I don't need all subtotals.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-24 : 07:06:30

is this it?

select month(ActivityDate), year(ActivityDate), sum(Amount)
from MyTable
where ...
Group by month(ActivityDate), year(ActivityDate)

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources