March 20, 2012 at 11:45 am
I've got a problem whereby I need to find the number of new contracts, the number of contract-type changes (where one contract has been replaced by one of another type), and the number of terminated contracts: i.e. starts, transfers (of certain types), ends.
My first step was to merge consecutive contracts of the same type, which I achieved with assistance in this thread.
However, despite experimenting with a few approaches, I'm making limited progress on working out starts, transfers, and ends.
Starting point:
CREATE TABLE Contracts(
ContractNo int,
refeGuid nvarchar(100),
ContractType nvarchar(100),
SP_ActualStartDate datetime,
SP_ActualEndDate datetime)
INSERT INTO Contracts
VALUES
('1', 'RefA', 'Con 1', '2011-03-26 00:00:00', '2011-04-15 00:00:00'),
('2', 'RefA', 'Con 5', '2011-04-16 00:00:00', NULL),
('1', 'RefB', 'Con 6', '2009-01-29 00:00:00', '2012-01-26 00:00:00'),
('2', 'RefB', 'Con 8', '2012-01-27 00:00:00', '2012-03-30 00:00:00'),
('1', 'RefC', 'Con 2', '2011-05-02 00:00:00', '2011-05-09 00:00:00'),
('2', 'RefC', 'Con 2', '2011-08-12 00:00:00', '2011-08-17 00:00:00'),
('3', 'RefC', 'Con 6', '2012-01-08 00:00:00', '2012-02-09 00:00:00'),
('1', 'RefD', 'Con 5', '2011-06-06 00:00:00', '2011-11-15 00:00:00'),
('2', 'RefD', 'Con 8', '2011-12-02 00:00:00', NULL),
('1', 'RefE', 'Con 5', '2010-05-12 00:00:00', '2011-05-12 00:00:00'),
('2', 'RefE', 'Con 6', '2011-05-13 00:00:00', '2012-03-19 00:00:00'),
('1', 'RefF', 'Con 6', '2011-05-15 00:00:00', '2012-01-23 00:00:00'),
('2', 'RefF', 'Con 8', '2012-01-24 00:00:00', '2012-03-26 00:00:00'),
('1', 'RefG', 'Con 6', '2011-06-25 00:00:00', '2011-06-27 00:00:00'),
('2', 'RefG', 'Con 6', '2011-06-30 00:00:00', '2011-07-02 00:00:00'),
('3', 'RefG', 'Con 6', '2011-07-05 00:00:00', '2012-02-01 00:00:00'),
('4', 'RefG', 'Con 6', '2012-02-14 00:00:00', NULL)
There are lots of different ways I could deliver the result (which could include just numbers of starts, transfers (from x to y), and ends), but the following is one way that hopefully shows how the counts should be put together:
Assume reporting period is 2011-04-01 to 2012-03-31
CREATE TABLE Result (
refeGuid nvarchar(100),
ContractNo_a int,
StartDate_a datetime,
EndDate_a datetime,
ContractType_a nvarchar(100),
ContractNo_b int,
StartDate_b datetime,
EndDate_b datetime,
ContractType_b nvarchar(100),
CountType nvarchar(100))
INSERT INTO Result
VALUES
('RefA', '1', '2011-03-26 00:00:00', '2011-04-15 00:00:00', 'Con 1', '2', '2011-04-16 00:00:00', NULL, 'Con 5', 'Con 1 to Con 5'),
('RefB', '1', '2009-01-29 00:00:00', '2012-01-26 00:00:00', 'Con 6', '2', '2012-01-27 00:00:00', '2012-03-30 00:00:00', 'Con 8', 'Con 6 to Con 8'),
('RefB', '2', '2012-01-27 00:00:00', '2012-03-30 00:00:00', 'Con 8', '1000', NULL, NULL, NULL, 'Con 8 End'),
('RefC', '0', NULL, NULL, NULL, '1', '2011-05-02 00:00:00', '2011-05-09 00:00:00', 'Con 2', 'Start Con 2'),
('RefC', '0', NULL, NULL, NULL, '2', '2011-08-12 00:00:00', '2011-08-17 00:00:00', 'Con 2', 'Start Con 2'),
('RefC', '0', NULL, NULL, NULL, '3', '2012-01-08 00:00:00', '2012-02-09 00:00:00', 'Con 6', 'Start Con 6'),
('RefC', '1', '2011-05-02 00:00:00', '2011-05-09 00:00:00', 'Con 2', '1000', NULL, NULL, NULL, 'Con 2 End'),
('RefC', '2', '2011-08-12 00:00:00', '2011-08-17 00:00:00', 'Con 2', '1000', NULL, NULL, NULL, 'Con 2 End'),
('RefC', '3', '2012-01-08 00:00:00', '2012-02-09 00:00:00', 'Con 6', '1000', NULL, NULL, NULL, 'Con 6 End'),
('RefD', '0', NULL, NULL, NULL, '1', '2011-06-06 00:00:00', '2011-11-15 00:00:00', 'Con 5', 'Start Con 5'),
('RefD', '0', NULL, NULL, NULL, '2', '2011-12-02 00:00:00', NULL, 'Con 8', 'Start Con 8'),
('RefD', '1', '2011-06-06 00:00:00', '2011-11-15 00:00:00', 'Con 5', '1000', NULL, NULL, NULL, 'Con 5 End'),
('RefE', '1', '2010-05-12 00:00:00', '2011-05-12 00:00:00', 'Con 5', '2', '2011-05-13 00:00:00', '2012-03-19 00:00:00', 'Con 6', 'Con 5 to Con 6'),
('RefE', '2', '2011-05-13 00:00:00', '2012-03-19 00:00:00', 'Con 6', '1000', NULL, NULL, NULL, 'Con 6 End'),
('RefF', '0', NULL, NULL, NULL, '1', '2011-05-15 00:00:00', '2012-01-23 00:00:00', 'Con 6', 'Start Con 6'),
('RefF', '1', '2011-05-15 00:00:00', '2012-01-23 00:00:00', 'Con 6', '2', '2012-01-24 00:00:00', '2012-03-26 00:00:00', 'Con 8', 'Con 6 to Con 8'),
('RefF', '2', '2012-01-24 00:00:00', '2012-03-26 00:00:00', 'Con 8', '1000', NULL, NULL, NULL, 'Con 8 End'),
('RefG', '0', NULL, NULL, NULL, '1', '2011-06-25 00:00:00', '2011-06-27 00:00:00', 'Con 6', 'Start Con 6'),
('RefG', '0', NULL, NULL, NULL, '2', '2011-06-30 00:00:00', '2011-07-02 00:00:00', 'Con 6', 'Start Con 6'),
('RefG', '0', NULL, NULL, NULL, '3', '2011-07-05 00:00:00', '2012-02-01 00:00:00', 'Con 6', 'Start Con 6'),
('RefG', '0', NULL, NULL, NULL, '4', '2012-02-14 00:00:00', NULL, 'Con 6', 'Start Con 6'),
('RefG', '1', '2011-06-25 00:00:00', '2011-06-27 00:00:00', 'Con 6', '1000', NULL, NULL, NULL, 'Con 6 End'),
('RefG', '2', '2011-06-30 00:00:00', '2011-07-02 00:00:00', 'Con 6', '1000', NULL, NULL, NULL, 'Con 6 End'),
('RefG', '3', '2011-07-05 00:00:00', '2012-02-01 00:00:00', 'Con 6', '1000', NULL, NULL, NULL, 'Con 6 End')
Notice how activity outside of the reporting period is ignored and gaps in a contract period of more than 1 day are treated as an end and a start.
I've tried a few things but am missing that 'killer code' that gives me just what I'm looking for. Suggestions on how to proceed?
Stuart
March 21, 2012 at 7:30 am
A set of strange dreams and some more work allowed me to find the answer I was looking for. It's long, slow, and could almost certainly be improved, but it works!
Looking at a set of contracts changes over a period of time, the following code allows me to identify new contracts, ending contracts, and transfers. A little more work and I can format it in a helpful report.
DECLARE @ReportStartDate as datetime
DECLARE @ReportEndDate as datetime
SET @ReportStartDate = '2011-04-01'
SET @ReportEndDate = '2012-03-31'
-- Also want slightly wider period as initial filter to cover services that start/end on same day as reporting period
DECLARE @ReportStartDate_Ext as datetime
DECLARE @ReportEndDateExt as datetime
SET @ReportStartDate_Ext = DATEADD(DAY,-1,@ReportStartDate)
SET @ReportEndDateExt = DATEADD(DAY,+1,@ReportEndDate);
-- Make sure that the memory does not contain any of the temp tables we're about to create
BEGIN TRY
DROP TABLE #TempContractTable1
DROP TABLE #TempContractTable2
DROP TABLE #TempContractTable3
DROP TABLE #TempContractTable4
END TRY
BEGIN CATCH
END CATCH;
-- Create a temporary table to work with
CREATE TABLE #TempContractTable1(
refeGuid nvarchar(100),
StartDate datetime,
EndDate datetime,
ContractType nvarchar(100));
--Populate temp table
INSERT INTO #TempContractTable1 (refeGuid, StartDate, EndDate, ContractType)
--Populate temp table with results from packing intervals
SELECT
refeGuid,
SP_ActualStartDate,
SP_ActualEndDate,
ContractType
FROM Contracts
UNION -- using UNION over UNION ALL will remove unnecessry duplicates
-- Populate temp table with empty service before real services
SELECT
refeGuid,
CONVERT(DATETIME, '1900-01-01 00:00:00', 102),
CONVERT(DATETIME, '1900-01-01 00:00:00', 102),
'Nothing'
FROM Contracts
UNION -- using UNION over UNION ALL will remove unnecessry duplicates
-- Populate temp table with empty service after real services
SELECT
refeGuid,
CONVERT(DATETIME, '2099-12-31 00:00:00', 102),
CONVERT(DATETIME, '2099-12-31 00:00:00', 102),
'Nothing'
FROM Contracts
; -- This colon should clear memory of packing now that we are finished with it
-- Create indexes to improve performance
CREATE INDEX #TempContractTable1_refeGuid ON #TempContractTable1(refeGuid)
CREATE INDEX #TempContractTable1_StartDate ON #TempContractTable1(StartDate)
CREATE INDEX #TempContractTable1_EndDate ON #TempContractTable1(EndDate)
-- Next we need to add row numbers
-- Create a temporary table to work with
CREATE TABLE #TempContractTable2(
ServiceNo int,
refeGuid nvarchar(100),
StartDate datetime,
EndDate datetime,
ContractType nvarchar(100))
-- Populate temp table
INSERT INTO #TempContractTable2 (ServiceNo, refeGuid, StartDate, EndDate, ContractType)
SELECT
ROW_NUMBER() OVER(PARTITION BY refeGuid ORDER BY StartDate, EndDate),
refeGuid,
StartDate,
EndDate,
ContractType
FROM #TempContractTable1
-- Create indexes to improve performance
CREATE INDEX #TempContractTable2_refeGuid ON #TempContractTable2(refeGuid)
CREATE INDEX #TempContractTable2_StartDate ON #TempContractTable2(StartDate)
CREATE INDEX #TempContractTable2_EndDate ON #TempContractTable2(EndDate)
-- No longer require #TempContractTable1, so do some cleanup
DROP TABLE #TempContractTable1
-- Cross join the table with itself
-- This will be used to show the 'from' and 'to' activity
-- Create a temporary table to work with
CREATE TABLE #TempContractTable3(
refeGuid nvarchar(100),
ServiceNo_a int,
StartDate_a datetime,
EndDate_a datetime,
ContractType_a nvarchar(100),
ServiceNo_b int,
StartDate_b datetime,
EndDate_b datetime,
ContractType_b nvarchar(100),
Diff int)
--Populate temp table
INSERT INTO #TempContractTable3 (refeGuid, ServiceNo_a, StartDate_a, EndDate_a, ContractType_a, ServiceNo_b, StartDate_b, EndDate_b, ContractType_b, Diff)
SELECT
ServiceTable_a.refeGuid,
ServiceTable_a.ServiceNo,
ServiceTable_a.StartDate,
ServiceTable_a.EndDate,
ServiceTable_a.ContractType,
ServiceTable_b.ServiceNo,
ServiceTable_b.StartDate,
ServiceTable_b.EndDate,
ServiceTable_b.ContractType,
CASE WHEN ServiceTable_a.EndDate >= ServiceTable_b.StartDate THEN 0 ELSE 1 END
FROM#TempContractTable2 AS ServiceTable_a INNER JOIN
#TempContractTable2 AS ServiceTable_b
ONServiceTable_a.refeGuid = ServiceTable_b.refeGuid AND
ServiceTable_a.ServiceNo + 1 = ServiceTable_b.ServiceNo
-- Create indexes to improve performance
CREATE INDEX #TempContractTable3_refeGuid ON #TempContractTable3(refeGuid)
CREATE INDEX #TempContractTable3_StartDate_a ON #TempContractTable3(StartDate_a)
CREATE INDEX #TempContractTable3_EndDate_a ON #TempContractTable3(EndDate_a)
CREATE INDEX #TempContractTable3_StartDate_b ON #TempContractTable3(StartDate_b)
CREATE INDEX #TempContractTable3_EndDate_b ON #TempContractTable3(EndDate_b)
-- No longer require #TempContractTable2, so do some cleanup
DROP TABLE #TempContractTable2
-- Next, decide if the service starts, ends, or transfers in the reporting period
-- Create a temporary table to work with
CREATE TABLE #TempContractTable4(
refeGuid nvarchar(100),
ServiceNo_a int,
StartDate_a datetime,
EndDate_a datetime,
ContractType_a nvarchar(100),
ServiceNo_b int,
StartDate_b datetime,
EndDate_b datetime,
ContractType_b nvarchar(100),
Diff int,
S_Start int,
S_End int,
S_Transfer int)
--Populate temp table
INSERT INTO #TempContractTable4 (refeGuid, ServiceNo_a, StartDate_a, EndDate_a, ContractType_a, ServiceNo_b, StartDate_b, EndDate_b, ContractType_b, Diff, S_Start, S_End, S_Transfer)
SELECT
refeGuid,
ServiceNo_a,
StartDate_a,
EndDate_a,
ContractType_a,
ServiceNo_b,
StartDate_b,
EndDate_b,
ContractType_b,
Diff,
CASE WHEN (StartDate_b BETWEEN @ReportStartDate AND @ReportEndDate) AND Diff =1 THEN 1 ELSE 0 END,
CASE WHEN (EndDate_a BETWEEN @ReportStartDate AND @ReportEndDate) AND Diff =1 THEN 1 ELSE 0 END,
CASE WHEN (StartDate_b BETWEEN @ReportStartDate AND @ReportEndDate) AND Diff = 0 THEN 1 ELSE 0 END
FROM #TempContractTable3
-- No longer require #TempContractTable3, so do some cleanup
DROP TABLE #TempContractTable3
SELECT * FROM #TempContractTable4
-- No longer require #TempContractTable4, so do some cleanup
DROP TABLE #TempContractTable4
Gives result:
CREATE TABLE Result (refeGuid nvarchar(100),ServiceNo_a int,StartDate_a datetime,EndDate_a datetime,ContractType_a nvarchar(100),ServiceNo_b int,StartDate_b datetime,EndDate_b datetime,ContractType_b nvarchar(100),Diff int,S_Start int,S_End int,S_Transfer int)
INSERT INTO Result
VALUES
('RefA', '1', '1899-12-31 00:00:00', '1899-12-31 00:00:00', 'Nothing', '2', '2011-03-26 00:00:00', '2011-04-15 00:00:00', 'Con 1', '1', '0', '0', '0'),
('RefA', '2', '2011-03-26 00:00:00', '2011-04-15 00:00:00', 'Con 1', '3', '2011-04-16 00:00:00', NULL, 'Con 5', '1', '1', '1', '0'),
('RefA', '3', '2011-04-16 00:00:00', NULL, 'Con 5', '4', '2099-12-31 00:00:00', '2099-12-31 00:00:00', 'Nothing', '1', '0', '0', '0'),
('RefB', '1', '1899-12-31 00:00:00', '1899-12-31 00:00:00', 'Nothing', '2', '2009-01-29 00:00:00', '2012-01-26 00:00:00', 'Con 6', '1', '0', '0', '0'),
('RefB', '2', '2009-01-29 00:00:00', '2012-01-26 00:00:00', 'Con 6', '3', '2012-01-27 00:00:00', '2012-03-30 00:00:00', 'Con 8', '1', '1', '1', '0'),
('RefB', '3', '2012-01-27 00:00:00', '2012-03-30 00:00:00', 'Con 8', '4', '2099-12-31 00:00:00', '2099-12-31 00:00:00', 'Nothing', '1', '0', '1', '0'),
('RefC', '1', '1899-12-31 00:00:00', '1899-12-31 00:00:00', 'Nothing', '2', '2011-05-02 00:00:00', '2011-05-09 00:00:00', 'Con 2', '1', '1', '0', '0'),
('RefC', '2', '2011-05-02 00:00:00', '2011-05-09 00:00:00', 'Con 2', '3', '2011-08-12 00:00:00', '2011-08-17 00:00:00', 'Con 2', '1', '1', '1', '0'),
('RefC', '3', '2011-08-12 00:00:00', '2011-08-17 00:00:00', 'Con 2', '4', '2012-01-08 00:00:00', '2012-02-09 00:00:00', 'Con 6', '1', '1', '1', '0'),
('RefC', '4', '2012-01-08 00:00:00', '2012-02-09 00:00:00', 'Con 6', '5', '2099-12-31 00:00:00', '2099-12-31 00:00:00', 'Nothing', '1', '0', '1', '0'),
('RefD', '1', '1899-12-31 00:00:00', '1899-12-31 00:00:00', 'Nothing', '2', '2011-06-06 00:00:00', '2011-11-15 00:00:00', 'Con 5', '1', '1', '0', '0'),
('RefD', '2', '2011-06-06 00:00:00', '2011-11-15 00:00:00', 'Con 5', '3', '2011-12-02 00:00:00', NULL, 'Con 8', '1', '1', '1', '0'),
('RefD', '3', '2011-12-02 00:00:00', NULL, 'Con 8', '4', '2099-12-31 00:00:00', '2099-12-31 00:00:00', 'Nothing', '1', '0', '0', '0'),
('RefE', '1', '1899-12-31 00:00:00', '1899-12-31 00:00:00', 'Nothing', '2', '2010-05-12 00:00:00', '2011-05-12 00:00:00', 'Con 5', '1', '0', '0', '0'),
('RefE', '2', '2010-05-12 00:00:00', '2011-05-12 00:00:00', 'Con 5', '3', '2011-05-13 00:00:00', '2012-03-19 00:00:00', 'Con 6', '1', '1', '1', '0'),
('RefE', '3', '2011-05-13 00:00:00', '2012-03-19 00:00:00', 'Con 6', '4', '2099-12-31 00:00:00', '2099-12-31 00:00:00', 'Nothing', '1', '0', '1', '0'),
('RefF', '1', '1899-12-31 00:00:00', '1899-12-31 00:00:00', 'Nothing', '2', '2011-05-15 00:00:00', '2012-01-23 00:00:00', 'Con 6', '1', '1', '0', '0'),
('RefF', '2', '2011-05-15 00:00:00', '2012-01-23 00:00:00', 'Con 6', '3', '2012-01-24 00:00:00', '2012-03-26 00:00:00', 'Con 8', '1', '1', '1', '0'),
('RefF', '3', '2012-01-24 00:00:00', '2012-03-26 00:00:00', 'Con 8', '4', '2099-12-31 00:00:00', '2099-12-31 00:00:00', 'Nothing', '1', '0', '1', '0'),
('RefG', '1', '1899-12-31 00:00:00', '1899-12-31 00:00:00', 'Nothing', '2', '2011-06-25 00:00:00', '2011-06-27 00:00:00', 'Con 6', '1', '1', '0', '0'),
('RefG', '2', '2011-06-25 00:00:00', '2011-06-27 00:00:00', 'Con 6', '3', '2011-06-30 00:00:00', '2011-07-02 00:00:00', 'Con 6', '1', '1', '1', '0'),
('RefG', '3', '2011-06-30 00:00:00', '2011-07-02 00:00:00', 'Con 6', '4', '2011-07-05 00:00:00', '2012-02-01 00:00:00', 'Con 6', '1', '1', '1', '0'),
('RefG', '4', '2011-07-05 00:00:00', '2012-02-01 00:00:00', 'Con 6', '5', '2012-02-14 00:00:00', NULL, 'Con 6', '1', '1', '1', '0'),
('RefG', '5', '2012-02-14 00:00:00', NULL, 'Con 6', '6', '2099-12-31 00:00:00', '2099-12-31 00:00:00', 'Nothing', '1', '0', '0', '0')
Stuart
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply