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
