A post on
SQL Central brought up an interesting question. It pointed out that
SQL Server 2005 does not allow a Discount Count to be computed when also using
Group by with Rollup, but wondered if it was possible to accomplish the result, anyway. This functionality is available in
SQL Server 2008.
I came up with a solution that created a derived table with the Rollups and used the
Coalesce to provide the proper join.
Based on this table:
Drop Table EmployeeLocation
go
Create Table EmployeeLocation(
RegionCode varchar(2) not null,
StateCode varchar(2) not null,
OfficeCode varchar(2) not null,
EmployeeID int not null
)
go
Insert EmployeeLocation values ( 'AA', 'NJ', '01', 1 )
Insert EmployeeLocation values ( 'AA', 'NJ', '01', 2 )
Insert EmployeeLocation values ( 'AA', 'NJ', '01', 3 )
Insert EmployeeLocation values ( 'AA', 'NJ', '02', 1 )
Insert EmployeeLocation values ( 'AA', 'PA', '03', 1 )
Insert EmployeeLocation values ( 'BB', 'IL', '04', 2 )
Insert EmployeeLocation values ( 'BB', 'IL', '04', 2 )
go
Here is the query with a derived table. This could be replaced by a CTE.
Select t1.RegionCode,
t1.StateCode,
t1.OfficeCode,
Count(Distinct t2.EmployeeID )
From (
Select RegionCode,
StateCode,
OfficeCode
From EmployeeLocation
Group
By RegionCode,
StateCode,
OfficeCode
With Cube
)t1
Join EmployeeLocation t2
On 1=1
And Coalesce( t1.RegionCode, t2.RegionCode ) = t2.RegionCode
And Coalesce( t1.StateCode, t2.StateCode ) = t2.StateCode
And Coalesce( t1.OfficeCode, t2.OfficeCode ) = t2.OfficeCode
Group
By t1.RegionCode,
t1.StateCode,
t1.OfficeCode
Order
By RegionCode ,
StateCode ,
OfficeCode
The Coalesce pattern can also be useful when checking a date against an open-ended range, i.e. one where one end is NULL.
The original question poster has not replied, which might indicate that this is of limited value.
