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