SQLTeam.com | Weblogs | Forums

Exceptions to a Custom Grouper

Hi, I have a general design question regarding exceptions to grouping logic.

I have a view that drives custom grouping based on the distinct combinations of two fields, ProjectMain and Department. The grouper view uses a CTE and RowNumber = 1 to derive the first item in the group to be used as the group key and group description. This grouper is used in many queries, and it works well.

But now.. a new business requirement.

I need the ability code exceptions to that grouping logic. By exceptions, I mean an exception table that is a list of projects that should not be grouped.

As I type this (typing is a good way to think about a problem), I think the best way will be to add "where project not in (subselect from exception table)" to the grouper view to prevent them from being groupped, and then append (union) the exception table to the view result so any queries that join to the grouper view will pass detail records through.

Does that make sense?

I would love to hear how others have done something like this, or any other tips and suggestions.

This is what the current/existing code looks like (simplified):

CREATE TABLE [ProjectMaster](
	[ProjectNumber] [char](7) NULL,
	[ProjectDescription] [varchar](20) NULL,
	[ProjectDepartment] [char](3) NULL
) ON [PRIMARY]
go

CREATE TABLE [ProjectDetail](
	[ProjectNumber] [char](7) NULL,
	[Balance] [decimal](10, 2) NULL
)
go

CREATE VIEW [ProjectGrouper] as
WITH CTE AS
	(
	SELECT 	
		SUBSTRING(ProjectNumber,1,3) AS ProjectPrefix,
		ProjectNumber,
		ProjectDepartment,
		ProjectDescription, 
		ROW_NUMBER() OVER (PARTITION BY SUBSTRING(ProjectNumber,1,3)+ProjectDepartment ORDER BY ProjectNumber) AS RowNumber 
	FROM   ProjectMaster M 
	)
SELECT 
	ProjectPrefix, 
	ProjectDepartment,
	ProjectNumber as ProjectGroup,
	ProjectDescription as ProjectGroupDescription
FROM CTE 
WHERE RowNumber = 1
go

INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'D3A-101', CAST(71.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'D3A-110', CAST(17.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'D3A-106', CAST(57.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'D3A-104', CAST(76.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'D3A-115', CAST(32.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'D3A-127', CAST(42.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'ABC-141', CAST(24.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'ABC-155', CAST(12.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'ABC-182', CAST(68.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'ABC-105', CAST(22.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'ABC-149', CAST(97.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'ABC-103', CAST(69.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'D3A-101', CAST(6.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'D3A-110', CAST(49.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'D3A-106', CAST(43.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'D3A-104', CAST(61.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'D3A-115', CAST(51.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'D3A-127', CAST(60.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'ABC-141', CAST(1.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'ABC-155', CAST(59.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'ABC-182', CAST(17.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'ABC-105', CAST(38.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'ABC-149', CAST(84.00 AS Decimal(10, 2)))
INSERT [ProjectDetail] ([ProjectNumber], [Balance]) VALUES (N'ABC-103', CAST(24.00 AS Decimal(10, 2)))

INSERT [ProjectMaster] ([ProjectNumber], [ProjectDescription], [ProjectDepartment]) VALUES (N'D3A-101', N'Apple', N'A  ')
INSERT [ProjectMaster] ([ProjectNumber], [ProjectDescription], [ProjectDepartment]) VALUES (N'D3A-110', N'Banana', N'B  ')
INSERT [ProjectMaster] ([ProjectNumber], [ProjectDescription], [ProjectDepartment]) VALUES (N'D3A-106', N'Kiwi', N'C  ')
INSERT [ProjectMaster] ([ProjectNumber], [ProjectDescription], [ProjectDepartment]) VALUES (N'D3A-104', N'Lime', N'A  ')
INSERT [ProjectMaster] ([ProjectNumber], [ProjectDescription], [ProjectDepartment]) VALUES (N'D3A-115', N'Orange', N'A  ')
INSERT [ProjectMaster] ([ProjectNumber], [ProjectDescription], [ProjectDepartment]) VALUES (N'D3A-127', N'Peach', N'B  ')
INSERT [ProjectMaster] ([ProjectNumber], [ProjectDescription], [ProjectDepartment]) VALUES (N'ABC-141', N'Apricot', N'A  ')
INSERT [ProjectMaster] ([ProjectNumber], [ProjectDescription], [ProjectDepartment]) VALUES (N'ABC-155', N'Blueberry', N'A  ')
INSERT [ProjectMaster] ([ProjectNumber], [ProjectDescription], [ProjectDepartment]) VALUES (N'ABC-182', N'Strawberry', N'B  ')
INSERT [ProjectMaster] ([ProjectNumber], [ProjectDescription], [ProjectDepartment]) VALUES (N'ABC-105', N'Watermelon', N'A  ')
INSERT [ProjectMaster] ([ProjectNumber], [ProjectDescription], [ProjectDepartment]) VALUES (N'ABC-149', N'Plum', N'A  ')
INSERT [ProjectMaster] ([ProjectNumber], [ProjectDescription], [ProjectDepartment]) VALUES (N'ABC-103', N'Lemon', N'B  ')



-- Here is a sample query that uses the grouper
-- It groups detail by fistinct combinations of ProjectNumber (first 3 chars) and Department.
-- It uses the ProjectNumber from the first project in the group as the key for the group
select
	G.ProjectGroup,
	G.ProjectGroupDescription,
	Count(*) as GroupProjectCount,
	sum(D.Balance) as GroupBalance
from 
	ProjectDetail D
		left join ProjectMaster M 
			on D.ProjectNumber = M.ProjectNumber
		left join ProjectGrouper G 
			on substring(D.ProjectNumber,1,3) = G.ProjectPrefix 
			and M.ProjectDepartment = G.ProjectDepartment
group by 
	G.ProjectGroup,
	G.ProjectGroupDescription

-- Clean Up
-- drop table ProjectMaster
-- drop table ProjectDetail
-- drop view ProjectGrouper

If you want the exceptions to not be included in the view - then either a NOT IN or NOT EXISTS will work. I prefer NOT EXISTS for this type of exclusion.

If you want the exclusions to be included in the results - but not 'grouped' then it will be a bit more work.