Most people when faced with a request such as that will respond similar to our Mikos (on the left) – with the “You want what? When?” look.
I had the person requesting this code twice demonstrate how he calculated the mutually inclusive set within Excel before taking on the project so I could be certain I understood the logic he was employing to calculate them.
The first demonstration included some “black magic”. During the second demonstration, I asked him to slow down and explain why he was doing what he did.
Terminology
Essentially, they had a many to many relationship between Project Milestones and Contracts for each Project and they needed to be able to accurately report the revenue for these items.Normally, there’s a one-to-one or one-to-many relationship between Milestones and Contracts for Projects (at least in the software that I have worked with), so this was to handle the exceptional relationship so they could calculate Project costs and revenue appropriately.
Project – A project – like “build a house”
Project Milestone – Completion of certain tasks for a project – like Painting or Install Carpeting
Contract – the agreement between Customer & the subcontractor doing the work – the painter who also installs carpeting
Reporting Groups – Mutually inclusive sets related to the Milestone & Contract combinations – one reporting group contains all milestones for each Contract as well as all Contracts for each milestone
Algorithm
Determining the correct Reporting Group value is a multi-step process calculated over all Contracts and Milestones associated to the Project.- Sort by Milestone
- Assign First-pass group value using DENSE_RANK() OVER (ORDER BY Milestone)
- Sort By Contract
- For each Contract with multiple Milestones, if the grouping values differ, assign all instances of the grouping value to the lowest value (Example: In Item 16, there are two Milestones. One has Grouping Value 4 & other has Grouping Value 6. Set all Grouping Value 6 instances = 4. Repeat for each Contract where a similar situation exists)
- Sort by Grouping Value
- Update Grouping Values so that any gaps in the sequence are closed (Example: Grouping Value 6 was removed in an earlier step, so all items with Grouping Value 7 become 6, then all the 8s become 7, etc. so that the Grouping Values are consecutive.)
Code
I found that the only way to reproduce the results was to loop through the data sets similar to the demonstrated method which includes suspiciously redundant code required to produce the “correct-according-to-the-customer” results.The following may not be ideal SQL, but I found that it returned results exactly matching several examples provided by the Customer. Yes, the seemingly redundant updates are necessary because without them the final output does not match his final results. I have not found any alternative method to replicate the same results more efficiently.
Please note that I’ve simplified the content:
- Only include what was directly relevant to the calculation of the mutually inclusive data sets
- The sample dataset that is entered into the @ContractMilestones table is not nearly close to the 100+ line examples given to me
- I have excluded any direct references to proprietary tables
DECLARE @ContractMilestones TABLE (
Co TINYINT
,Project VARCHAR(10)
,[Contract] VARCHAR(16)
,Milestone VARCHAR(20)
)
INSERT @ContractMilestones (Co, Project, [Contract], Milestone)
VALUES
( 102,'Test', '5','493')
,( 102,'Test', '1.01','111')
,( 102,'Test', '1.01', '211')
,( 102,'Test', '1.01', '311')
,( 102,'Test', '2.02', '111')
,( 102,'Test', '3.01', '211')
DECLARE @ReportingGroups TABLE (
Co TINYINT
,Project VARCHAR(10)
,Milestone VARCHAR(20)
,[Contract] VARCHAR(16)
,MilestoneRptGroup INT
,ContractGrouping INT
,FinalGroup INT
);
WITH preranked (
Co
,Project
,Milestone
,[Contract]
,MilestoneRptGroup
)
AS (
SELECT Co
,Project
,Milestone
,[Contract]
,DENSE_RANK() OVER (
PARTITION BY Co
,Project ORDER BY Co
,Project
,Milestone
) AS MilestoneRptGroup
FROM @ContractMilestones
)
,contractgroupd (
Co
,Project
,Milestone
,[Contract]
,MilestoneRptGroup
,ContractGrouping
)
AS (
SELECT Co
,Project
,Milestone
,[Contract]
,MilestoneRptGroup
,min(MilestoneRptGroup) OVER (
PARTITION BY [Contract] ORDER BY [Contract] DESC
) AS ContractGrouping
FROM preranked
)
INSERT @ReportingGroups (
Co
,Project
,Milestone
,[Contract]
,MilestoneRptGroup
,ContractGrouping
,FinalGroup
)
SELECT Co
,Project
,Milestone
,[Contract]
,MilestoneRptGroup
,ContractGrouping
,DENSE_RANK() OVER (
ORDER BY Co
,Project
,ContractGrouping
) AS FinalGroup
FROM contractgroupd
ORDER BY FinalGroup
DECLARE @ItemGrouping INT
DECLARE @MilestoneGroup TABLE (MilestoneRptGroup INT)
DECLARE @ContractGroup TABLE (ContractRptGroup INT)
SELECT @ItemGrouping = min(FinalGroup)
FROM @ReportingGroups
WHILE @ItemGrouping IS NOT NULL
BEGIN
INSERT @MilestoneGroup (MilestoneRptGroup)
SELECT DISTINCT MilestoneRptGroup
FROM @ReportingGroups
WHERE FinalGroup = @ItemGrouping
UPDATE @ReportingGroups
SET FinalGroup = @ItemGrouping
,MilestoneRptGroup = @ItemGrouping
WHERE MilestoneRptGroup IN (
SELECT MilestoneRptGroup
FROM @MilestoneGroup
)
DELETE
FROM @MilestoneGroup
INSERT @ContractGroup (ContractRptGroup)
SELECT DISTINCT ContractGrouping
FROM @ReportingGroups
WHERE FinalGroup = @ItemGrouping
UPDATE @ReportingGroups
SET FinalGroup = @ItemGrouping
,ContractGrouping = @ItemGrouping
WHERE ContractGrouping IN (
SELECT ContractRptGroup
FROM @ContractGroup
)
DELETE
FROM @ContractGroup
INSERT @MilestoneGroup (MilestoneRptGroup)
SELECT DISTINCT MilestoneRptGroup
FROM @ReportingGroups
WHERE FinalGroup = @ItemGrouping
UPDATE @ReportingGroups
SET FinalGroup = @ItemGrouping
,MilestoneRptGroup = @ItemGrouping
WHERE MilestoneRptGroup IN (
SELECT MilestoneRptGroup
FROM @MilestoneGroup
)
DELETE
FROM @MilestoneGroup
INSERT @ContractGroup (ContractRptGroup)
SELECT DISTINCT ContractGrouping
FROM @ReportingGroups
WHERE FinalGroup = @ItemGrouping
UPDATE @ReportingGroups
SET FinalGroup = @ItemGrouping
,ContractGrouping = @ItemGrouping
WHERE ContractGrouping IN (
SELECT ContractRptGroup
FROM @ContractGroup
)
DELETE
FROM @ContractGroup
SELECT @ItemGrouping = min(FinalGroup)
FROM @ReportingGroups
WHERE FinalGroup > @ItemGrouping
END;
-- This is the final table that holds the data we've retrieved
-- insert commented out so we can do a simple select.
-- INSERT FinalReportingGroups (
-- Co
-- ,Project
-- ,[Contract]
-- ,ReportingGroup
-- ,Milestone
-- )
SELECT Co
,Project
,[Contract]
,DENSE_RANK() OVER (
ORDER BY Co
,Project
,ContractGrouping
) AS ReportingGroup
,Milestone
FROM @ReportingGroups
ORDER BY Co
,Project
,[Contract]
,Milestone