with ROLLUP not working as expected.

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)

with ROLLUP not working as expected.

Author

Topic

Smitty200
Starting Member

11 Posts

Posted - 2006-05-15 : 13:57:37

SELECT i.ItemName, i.Description, i.ItemSKU, c.CurrentCount, c.MaximumOnHand, c.ReorderCount, c.LeadTime, isNull(SUM(od.Sent),0) AS Consumed, v.Company,
i.LastPO, i.LastPrice / i.ConversionQTY AS UnitCost, c.CurrentCount * (i.LastPrice / i.ConversionQTY) AS Total
FROM tItems i LEFT OUTER JOIN
tCounts c ON i.ItemSKU = c.ItemSKU LEFT OUTER JOIN
tPurchaseOrderMaster p ON i.LastPO = p.OrderID LEFT OUTER JOIN
tVendors v ON p.VendorID = v.VendorID LEFT OUTER JOIN
tOpenOrdersDetail od ON i.ItemSKU = od.ItemSKU
WHERE i.LastPO != 0 AND (od.D >= '4/1/2006' AND od.D<= '4/30/2006)
GROUP BY i.ItemSKU, i.ItemName, i.Description, c.CurrentCount, c.MaximumOnHand, c.ReorderCount, c.LeadTime, v.Company, i.LastPO, i.LastPrice,
i.ConversionQTY
ORDER BY i.ItemSKU

The above query works for me fine. I want to get subtotal for the Total column based on the itemSKU field. Its format is 'DC000001' where the DC can be one of five two letter combinations. I would like to get the subtotals based on those five categories. I thought the ROLLUP function would work for me but its not returning what I expect. I'm sure my syntax is screwed up somewhere. The following is my SQL with ROLLUP included.

SELECT
i.ItemName,

CASE
WHEN (Grouping(Substring(i.itemSKU,1,2))=1) THEN 'SubTotal'
ELSE i.itemSKU
END AS ItemSKU,

c.CurrentCount, c.MaximumOnHand, c.ReorderCount, isNull(SUM(od.Sent),0) AS Consumed,
i.LastPO, i.LastPrice / i.ConversionQTY AS UnitCost, SUM(c.CurrentCount * (i.LastPrice / i.ConversionQTY)) AS Total
FROM tItems i LEFT OUTER JOIN
tCounts c ON i.ItemSKU = c.ItemSKU LEFT OUTER JOIN
tPurchaseOrderMaster p ON i.LastPO = p.OrderID LEFT OUTER JOIN
tVendors v ON p.VendorID = v.VendorID LEFT OUTER JOIN
tOpenOrdersDetail od ON i.ItemSKU = od.ItemSKU
WHERE i.LastPO != 0 AND (od.D >='4/1/06' AND od.D<= '4/30/06')
GROUP BY Substring(i.itemSKU,1,2), i.ItemName, i.ItemSKU, i.ItemName, c.CurrentCount, c.MaximumOnHand, c.ReorderCount, i.LastPO, i.LastPrice, i.ConversionQTY WITH ROLLUP
ORDER BY i.ItemSKU

Can anyone point me in the write direction.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources