Background
Still working on Utilization reports and needing to build a single report that has the flexibility to support different aggregation levels.
I know I can do so from my reporting tool, but wanted to explore how to I can accomplish same from the SQL Server Engine.
Test Environment
Let us use the Adventure works database:
Detail
use [AdventureWorks2014] go declare @separator varchar(10) declare @zzz varchar(10) declare @unknown varchar(30) set @separator = ', ' set @zzz = 'zzz' set @unknown = 'Unknown' /* List detail lines */ select [TerritoryID] = tblSP.TerritoryID , [CountryRegionCode] = tblST.[CountryRegionCode] , [Territory] = tblST.[Name] + @separator + [CountryRegionCode] , [fullname] = tblP.LastName + @separator + tblP.FirstName , tblSP.[SalesYTD] from [Person].[Person] tblP inner join [Sales].[SalesPerson] tblSP on tblP.BusinessEntityID = tblSP.BusinessEntityID left outer join [Sales].[SalesTerritory] tblST on tblSP.TerritoryID = tblST.TerritoryID order by [CountryRegionCode] , tblST.[Name] + @separator + [CountryRegionCode] , tblP.LastName + @separator + tblP.FirstName
Group By with Rollup
use [AdventureWorks2014] go declare @separator varchar(10) declare @zzz varchar(10) declare @unknown varchar(30) set @separator = ', ' set @zzz = 'zzz' set @unknown = '' /* List group by Rollup */ ;with cteDetail as ( select tblP.BusinessEntityID , [CountryRegionCode] = isNull(cast(tblST.[CountryRegionCode] as varchar(30)), @unknown) , [SalesTerritory] = tblST.[Name] , [SalesYTD] = isNull(tblST.[SalesYTD], 0) from [Person].[Person] tblP inner join [Sales].[SalesPerson] tblSP on tblP.BusinessEntityID = tblSP.BusinessEntityID left outer join [Sales].[SalesTerritory] tblST on tblSP.TerritoryID = tblST.TerritoryID ) , cteGroupRollup as ( select case when (GROUPING([CountryRegionCode]) = 1) then 'Region' when (GROUPING([SalesTerritory]) = 1) then 'Sales Territory' when (GROUPING([SalesTerritory]) = 0) then 'Detail' else '' -- for detail line end as [groupingHighest] , case when (GROUPING([CountryRegionCode]) = 1) then 2 when (GROUPING([SalesTerritory]) = 1) then 1 when (GROUPING([SalesTerritory]) = 0) then 0 else 3 end as [groupingHighestAsNumeric] , [CountryRegionCode] , [Territory] = [SalesTerritory] , [SalesYTD] = sum(isNull([SalesYTD], 0) ) from cteDetail cteD group by cteD.[CountryRegionCode] , cteD.[SalesTerritory] with rollup ) select [CountryRegionCode] = cteGR.[CountryRegionCode] , [Territory] = cteGR.[Territory] , [Level] = cteGR.[groupingHighest] , [LevelID] = cteGR.groupingHighestAsNumeric , cteGR.SalesYTD from cteGroupRollup cteGR order by isNull(cteGR.[CountryRegionCode], @zzz) , isNull(cteGR.[Territory], @zzz) , cteGR.groupingHighestAsNumeric
Explanation:
- Where did the first two rows with SalesYTD=0 come from?
- Honestly struggled with this mightily
- Found out that they are due to entries in our detail records where Territory ID is null
- I really like the fact that the Group By \ Rollup allows record by record visibility into each’s row hierarchy via the Grouping clause
- It is very, very useful for debugging
- Also, one can use it to filter based on where in the Hierarchy a record belongs
- Quite often one might need the order by \ isNull([data], ‘zzzzz’) to properly order things
- As the summary records have nulls, and nulls are smaller that comparable numbers, the summary row will usually precede the actual data that is being amalgamated if we yield the isNull \ ‘zzzz’
Listening
I was checking a friend’s YouTube library today and found this music.
It has been out for far too long (for me to just now discover it), but like that pass me down used car, it is new to me
Destiny’s Child – Bad Habit
https://www.youtube.com/watch?v=8H38A4VeKY0