March 30, 2018 at 10:23 am
I've only seen this used in MS documentation, and have yet to encounter it in the "real world", but I have a query now that might justify the usage. However, I want to better understand what both ROLLUP and GROUPING SETS do. I have a query that needs to GROUP on 3 columns, and the last 2 of them are never going to be anything but fixed pairs, meaning that if the store number is 1234, then the date is always going to be the same date for that store, guaranteed, and as I need to select that date, I have to include it in the GROUP BY. Here's the query:SELECT
@BusinessMonthChar AS BusinessMonth,
SDL.DeptID,
SDL.CostCenterID AS Store,
SDL.MaxBusinessDate,
SUM(I.CostEndingInventoryAmount) AS CostEndingInventoryAmount
FROM dbo.MyTableInv AS I WITH (NOLOCK)
INNER JOIN #StoreDateList AS SDL
ON I.BusinessDate = SDL.MaxBusinessDate
AND I.DeptID = SDL.DeptID
AND I.CostCenterID = SDL.CostCenterID
GROUP BY ROLLUP (
SDL.DeptID,
(
SDL.CostCenterID,
SDL.MaxBusinessDate
)
)
ORDER BY
SDL.DeptID,
SDL.CostCenterID;
There are two things I want to accomplish:
1.) Get totals at the DeptID level, that sort to the end of the DeptID set of rows
2.) Maintain the sub-second performance this query currently has
Here's the relevant index on the table:CREATE NONCLUSTERED INDEX NDX_MyTableInv_BusinessDate_DeptID_CostCenterID_CostEndingInventoryAmount ON dbo.MyTableInv
(
BusinessDate ASC,
DeptID ASC,
CostCenterID ASC
)
INCLUDE
(
CostEndingInventoryAmount
);
The BusinessDate is used as just a date but is smalldatetime (and won't be changing), and DeptID is varchar(3) and CostCenterID is char(7). There are 1.4 Million rows in the table. I'm getting the rows I want, I just hesitate to have to add another SELECT to get the ORDER that I want ... or should I just do it? This query will feed an SSRS report, but I don't want to do the totals in SSRS. This is an experiment of sorts, but I want to see how to make this occur without changing it's performance characteristics.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 2, 2018 at 8:13 am
Well, ... I'm guessing very few people mess with ROLLUP or with GROUPING SETS, so I've gone ahead and SELECTed from the query above as a sub-query, with an ORDER BY that uses ISNULL. I'm still sub-second for execution time, so performance isn't an issue at the moment.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply