GROUP ROLLUP Problem - SQL Server Forums

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

Transact-SQL (2012)

GROUP ROLLUP Problem

Author

Topic

alexander1
Starting Member

1 Post

Posted - 2013-11-16 : 17:09:38

I have looked at the examples of GROUP BY ROLLUP, but my application is
a little more complicated.
In the SQL below, I do a JOIN that destroys the ROLLUP because the ID fields are NULL in a ROLLUP.
Does anybody know how to make the ROLLUP work. Some re-structuring is obviously required. Please see the present results. They are correct, but don't have the ROLLUP.

Thx
Alex

SELECT

O.Id,
O.Wood_Type_Id,
O.Product_Id,

O.WeekVolume,
O.MonthVolume,
C.Est_Volume,
O.ToDateVolume

FROM

Cruise C

JOIN

(

SELECT

C.Id,
C.Wood_Type_Id,
C.Product_Id,

SUM(IIf(DATEPART(wk,T.Ticket_Date) = DATEPART(wk,GETDATE()) ,isnull(T.Volume,0),0)) AS WeekVolume,
SUM(IIf(DATEPART(mm,T.Ticket_Date) = DATEPART(mm,GETDATE()) ,isnull(T.Volume,0),0)) AS MonthVolume,
SUM(isnull(T.Volume,0)) AS ToDateVolume,

SUM(CT.Total_Volume) AS ClosedToDateVolume,
SUM(CT.Stumpage_Cost) AS ClosedStumpageCost,
SUM(CT.Total_Cost) AS ClosedTotalCost,
SUM(CT.income) AS ClosedToDateIncome

FROM

Cruise C

LEFT OUTER JOIN Tickets T ON C.Id = T.Tract_Id and C.Product_Id = T.Product_Id

LEFT OUTER JOIN Closed_Tracts CT ON C.Id = CT.Tract_Id and C.Product_Id = CT.Product_Id

GROUP BY ROLLUP (C.Id, C.Wood_Type_Id, C.Product_Id)

) O

ON C.Id = O.Id and C.Wood_Type_Id = O.Wood_Type_Id and C.Product_Id = O.Product_Id

Id Wood_Type_Id Product_Id WeekVolume MonthVolume Est_Volume ToDateVolume
3 0 0 0.00 0.00 300.00 40000.00
4 1 8 0.00 0.00 100.00 19740.00
5 1 9 0.00 0.00 200.00 0.00
6 2 5 0.00 0.00 750.00 0.00
7 2 6 0.00 0.00 600.00 0.00
8 3 2 0.00 0.00 800.00 0.00
9 3 3 0.00 0.00 750.00 0.00
10 3 3 0.00 0.00 750.00 0.00
11 3 3 0.00 0.00 750.00 0.00
12 1 9 0.00 0.00 500.00 0.00
13 1 8 0.00 0.00 400.00 0.00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-17 : 11:56:28

Add GROUPING function based column in inner query and add OR condition in the INNER JOIN to include that as well.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources