SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 GROUP ROLLUP Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alexander1
Starting Member

1 Posts

Posted - 11/16/2013 :  17:09:38  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/17/2013 :  11:56:28  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000