April 5, 2018 at 12:11 pm
Assume I have the data contained in the attached example. I have a table of Serial Numbers with their contract number and the Start Date and Call Off Date. I also have a table that has every month (eg Period) I care to analyze. What I'm trying to determine is for each period, how many days was the serial number on rent for. See the attached sketch of what I see the possibilities are and what I'm trying to determine.
I've tried a CROSS APPLY and I'm getting tangled up in the logic needed to get the right combinations that are possible in order to determine the number of days for the period.
If you could lend a hand, I would appreciate it.
April 5, 2018 at 1:55 pm
Take a look at this to start:SET NOCOUNT ON;
CREATE TABLE #Data (
SerialNumber varchar(25) NOT NULL,
ContractNumber varchar(10) NOT NULL,
StartDate date NOT NULL,
CallOffDate date NOT NULL
);
INSERT INTO #Data (SerialNumber, ContractNumber, StartDate, CallOffDate) VALUES ('0SKR00237', 'L74679','2012-08-07','2012-12-20');
INSERT INTO #Data (SerialNumber, ContractNumber, StartDate, CallOffDate) VALUES ('0SKR00237', 'L91421','2013-05-22','2013-05-31');
INSERT INTO #Data (SerialNumber, ContractNumber, StartDate, CallOffDate) VALUES ('0SKR00237', 'L95458','2013-06-17','2013-06-17');
INSERT INTO #Data (SerialNumber, ContractNumber, StartDate, CallOffDate) VALUES ('0SKR00237', 'L00456','2013-07-16','2013-07-16');
INSERT INTO #Data (SerialNumber, ContractNumber, StartDate, CallOffDate) VALUES ('0SKR00237', 'L00965','2013-08-22','2013-10-01');
INSERT INTO #Data (SerialNumber, ContractNumber, StartDate, CallOffDate) VALUES ('0SKR00237', 'L02281','2013-11-27','2014-03-19');
INSERT INTO #Data (SerialNumber, ContractNumber, StartDate, CallOffDate) VALUES ('0SKR00237', 'L23319','2014-06-03','2014-07-14');
INSERT INTO #Data (SerialNumber, ContractNumber, StartDate, CallOffDate) VALUES ('0SKR00237', 'L24170','2014-07-22','2014-10-08');
INSERT INTO #Data (SerialNumber, ContractNumber, StartDate, CallOffDate) VALUES ('0SKR00237', 'L27166','2015-03-06','2015-03-09');
INSERT INTO #Data (SerialNumber, ContractNumber, StartDate, CallOffDate) VALUES ('0SKR00237', 'L27927','2015-04-29','2015-05-19');
INSERT INTO #Data (SerialNumber, ContractNumber, StartDate, CallOffDate) VALUES ('0SKR00237', 'L28445','2015-06-04','2015-06-10');
CREATE TABLE #DateTable (
PeriodStart DATE NOT NULL,
PeriodEnd DATE NOT NULL
);
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2012-08-01','2012-08-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2012-09-01','2012-09-30');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2012-10-01','2012-10-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2012-11-01','2012-11-30');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2012-12-01','2012-12-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2013-01-01','2013-01-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2013-02-01','2013-02-28');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2013-03-01','2013-03-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2013-04-01','2013-04-30');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2013-05-01','2013-05-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2013-06-01','2013-06-30');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2013-07-01','2013-07-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2013-08-01','2013-08-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2013-09-01','2013-09-30');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2013-10-01','2013-10-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2013-11-01','2013-11-30');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2013-12-01','2013-12-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2014-01-01','2014-01-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2014-02-01','2014-02-28');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2014-03-01','2014-03-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2014-04-01','2014-04-30');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2014-05-01','2014-05-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2014-06-01','2014-06-30');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2014-07-01','2014-07-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2014-08-01','2014-08-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2014-09-01','2014-09-30');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2014-10-01','2014-10-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2014-11-01','2014-11-30');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2014-12-01','2014-12-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2015-01-01','2015-01-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2015-02-01','2015-02-28');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2015-03-01','2015-03-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2015-04-01','2015-04-30');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2015-05-01','2015-05-31');
INSERT INTO #DateTable (PeriodStart, PeriodEnd) VALUES ('2015-06-01','2015-06-30');
--SELECT * FROM #Data;
--SELECT * FROM #DateTable;
WITH Numbers AS (
SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL
SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL SELECT 1 AS N
),
DateRanges AS (
SELECT
X.PeriodStart,
X.PeriodEnd,
X.TheDate,
DENSE_RANK() OVER(ORDER BY X.PeriodStart) AS PeriodGroup
FROM (
SELECT
DT.PeriodStart,
DT.PeriodEnd,
DATEADD(day, ROW_NUMBER() OVER(PARTITION BY DT.PeriodStart ORDER BY (SELECT NULL)) - 1, DT.PeriodStart) AS TheDate
FROM #DateTable AS DT
CROSS APPLY Numbers AS N1
CROSS APPLY Numbers AS N2
) AS X
WHERE X.TheDate <= X.PeriodEnd
),
ExpandedData AS (
SELECT
X.ContractNumber,
X.SerialNumber,
X.StartDate,
X.CallOffDate,
X.TheDate
FROM (
SELECT
D.ContractNumber,
D.SerialNumber,
D.StartDate,
D.CallOffDate,
DATEADD(day, N.Num, D.StartDate) AS TheDate
FROM #Data AS D
CROSS APPLY (
SELECT TOP (DATEDIFF(day, D.StartDate, D.CallOffDate) + 1) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS Num
FROM Numbers AS N1
CROSS APPLY Numbers AS N2
CROSS APPLY Numbers AS N3
CROSS APPLY Numbers AS N4
) AS N
) AS X
WHERE X.TheDate <= X.CallOffDate
)
SELECT
D.ContractNumber,
D.SerialNumber,
DR.PeriodGroup,
DR.PeriodStart,
DR.PeriodEnd,
MAX(D.StartDate) AS StartDate,
MAX(D.CallOffDate) AS CallOffDate,
COUNT(1) AS DayCount
FROM ExpandedData AS D
INNER JOIN DateRanges AS DR
ON D.TheDate = DR.TheDate
GROUP BY
D.ContractNumber,
D.SerialNumber,
DR.PeriodGroup,
DR.PeriodStart,
DR.PeriodEnd
ORDER BY
DR.PeriodGroup,
D.ContractNumber,
D.SerialNumber,
StartDate,
CallOffDate;
DROP TABLE #Data;
DROP TABLE #DateTable;
EDIT: posted new code... found it was broken.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 5, 2018 at 2:36 pm
Here's the results of the updated code:
Contract Serial Period Period PeriodEnd StartDate CallOff Day
Number Number Group Start Date Count
====== =========== ===== ========== ========== ========== ========== =====
L74679 0SKR00237 1 2012-08-01 2012-08-31 2012-08-07 2012-12-20 25
L74679 0SKR00237 2 2012-09-01 2012-09-30 2012-08-07 2012-12-20 30
L74679 0SKR00237 3 2012-10-01 2012-10-31 2012-08-07 2012-12-20 31
L74679 0SKR00237 4 2012-11-01 2012-11-30 2012-08-07 2012-12-20 30
L74679 0SKR00237 5 2012-12-01 2012-12-31 2012-08-07 2012-12-20 20
L91421 0SKR00237 10 2013-05-01 2013-05-31 2013-05-22 2013-05-31 10
L95458 0SKR00237 11 2013-06-01 2013-06-30 2013-06-17 2013-06-17 1
L00456 0SKR00237 12 2013-07-01 2013-07-31 2013-07-16 2013-07-16 1
L00965 0SKR00237 13 2013-08-01 2013-08-31 2013-08-22 2013-10-01 10
L00965 0SKR00237 14 2013-09-01 2013-09-30 2013-08-22 2013-10-01 30
L00965 0SKR00237 15 2013-10-01 2013-10-31 2013-08-22 2013-10-01 1
L02281 0SKR00237 16 2013-11-01 2013-11-30 2013-11-27 2014-03-19 4
L02281 0SKR00237 17 2013-12-01 2013-12-31 2013-11-27 2014-03-19 31
L02281 0SKR00237 18 2014-01-01 2014-01-31 2013-11-27 2014-03-19 31
L02281 0SKR00237 19 2014-02-01 2014-02-28 2013-11-27 2014-03-19 28
L02281 0SKR00237 20 2014-03-01 2014-03-31 2013-11-27 2014-03-19 19
L23319 0SKR00237 23 2014-06-01 2014-06-30 2014-06-03 2014-07-14 28
L23319 0SKR00237 24 2014-07-01 2014-07-31 2014-06-03 2014-07-14 14
L24170 0SKR00237 24 2014-07-01 2014-07-31 2014-07-22 2014-10-08 10
L24170 0SKR00237 25 2014-08-01 2014-08-31 2014-07-22 2014-10-08 31
L24170 0SKR00237 26 2014-09-01 2014-09-30 2014-07-22 2014-10-08 30
L24170 0SKR00237 27 2014-10-01 2014-10-31 2014-07-22 2014-10-08 8
L27166 0SKR00237 32 2015-03-01 2015-03-31 2015-03-06 2015-03-09 4
L27927 0SKR00237 33 2015-04-01 2015-04-30 2015-04-29 2015-05-19 2
L27927 0SKR00237 34 2015-05-01 2015-05-31 2015-04-29 2015-05-19 19
L28445 0SKR00237 35 2015-06-01 2015-06-30 2015-06-04 2015-06-10 7
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 5, 2018 at 3:56 pm
Thanks Steve. I will study this. I really appreciate you taking the time to put this together.
April 5, 2018 at 8:50 pm
If I'm understanding the problem correctly, this is a classic problem with overlapping and underlapping dates. Please see the following article...
http://www.sqlservercentral.com/articles/T-SQL/105968/
... to see why the following simplification works. (I think I did it right but please double check)
SELECT ContractNumber
,SerialNumber
,PeriodStart
,PeriodEnd
,StartDate
,CallOffDate
,PeriodDays = DATEDIFF(dd
,CASE WHEN p.PeriodStart >= d.StartDate THEN p.PeriodStart ELSE d.StartDate END
,CASE WHEN p.PeriodEnd <= d.CallOffDate THEN p.PeriodEnd ELSE d.CallOffDate END
)+1
FROM @DateTable p
JOIN @data d ON p.PeriodStart <= d.CallOffDate
AND p.PeriodEnd >= d.StartDate
ORDER BY d.ContractNumber, p.PeriodStart
;
To summarize the article, you only need two comparisons in the JOIN to resolve all of the following conditions (I added 3 more possibilities to graphically explain what's in the article).
| Desired |
Left "Outside"| Month |Right "OutSide"
<-------------|---------|-------------->
| |
S--1--E | S--3--E | S--2--E
| |
S--5--E S--4--E
| |
S--7--E |
| |
| S--8--E
| |
S----9----E
| |
S----------|----6----|----------E
| |
DS DE
LEGEND:
S = StartDate of a row.
E = EndDate of a row. (CallOffDate)
DS = StartDate of desired month (PeriodStart).
DE = EndDate of desired month (PeriodEnd).
Add a little decision making in the SELECT clause and Bob's your uncle. Here's the output of the code above...
ContractNumber SerialNumber PeriodStart PeriodEnd StartDate CallOffDate PeriodDays
-------------- ------------ ----------- ---------- ---------- ----------- -----------
L00456 0SKR00237 2013-07-01 2013-07-31 2013-07-16 2013-07-16 1
L00965 0SKR00237 2013-08-01 2013-08-31 2013-08-22 2013-10-01 10
L00965 0SKR00237 2013-09-01 2013-09-30 2013-08-22 2013-10-01 30
L00965 0SKR00237 2013-10-01 2013-10-31 2013-08-22 2013-10-01 1
L02281 0SKR00237 2013-11-01 2013-11-30 2013-11-27 2014-03-19 4
L02281 0SKR00237 2013-12-01 2013-12-31 2013-11-27 2014-03-19 31
L02281 0SKR00237 2014-01-01 2014-01-31 2013-11-27 2014-03-19 31
L02281 0SKR00237 2014-02-01 2014-02-28 2013-11-27 2014-03-19 28
L02281 0SKR00237 2014-03-01 2014-03-31 2013-11-27 2014-03-19 19
L23319 0SKR00237 2014-06-01 2014-06-30 2014-06-03 2014-07-14 28
L23319 0SKR00237 2014-07-01 2014-07-31 2014-06-03 2014-07-14 14
L24170 0SKR00237 2014-07-01 2014-07-31 2014-07-22 2014-10-08 10
L24170 0SKR00237 2014-08-01 2014-08-31 2014-07-22 2014-10-08 31
L24170 0SKR00237 2014-09-01 2014-09-30 2014-07-22 2014-10-08 30
L24170 0SKR00237 2014-10-01 2014-10-31 2014-07-22 2014-10-08 8
L27166 0SKR00237 2015-03-01 2015-03-31 2015-03-06 2015-03-09 4
L27927 0SKR00237 2015-04-01 2015-04-30 2015-04-29 2015-05-19 2
L27927 0SKR00237 2015-05-01 2015-05-31 2015-04-29 2015-05-19 19
L28445 0SKR00237 2015-06-01 2015-06-30 2015-06-04 2015-06-10 7
L74679 0SKR00237 2012-08-01 2012-08-31 2012-08-07 2012-12-20 25
L74679 0SKR00237 2012-09-01 2012-09-30 2012-08-07 2012-12-20 30
L74679 0SKR00237 2012-10-01 2012-10-31 2012-08-07 2012-12-20 31
L74679 0SKR00237 2012-11-01 2012-11-30 2012-08-07 2012-12-20 30
L74679 0SKR00237 2012-12-01 2012-12-31 2012-08-07 2012-12-20 20
L91421 0SKR00237 2013-05-01 2013-05-31 2013-05-22 2013-05-31 10
L95458 0SKR00237 2013-06-01 2013-06-30 2013-06-17 2013-06-17 1
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2018 at 9:07 pm
Ah... looking back at your original post, it looks like you don't care about anything but the period and the total days it may contain. The following code will do that using the same technique as above. If it's something else you're looking for, please clarify.
SELECT PeriodStart
,PeriodEnd
,PeriodDays = SUM(DATEDIFF(dd
,CASE WHEN p.PeriodStart >= d.StartDate THEN p.PeriodStart ELSE d.StartDate END
,CASE WHEN p.PeriodEnd <= d.CallOffDate THEN p.PeriodEnd ELSE d.CallOffDate END
)+1)
FROM @DateTable p
JOIN @data d ON p.PeriodStart <= d.CallOffDate
AND p.PeriodEnd >= d.StartDate
GROUP BY p.PeriodStart,PeriodEnd
;
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2018 at 6:04 am
Jeff, this is the method I was looking for and the results in are in line with what I wanted. Thank you so much for your help.
April 6, 2018 at 6:22 am
LeeFAR - Friday, April 6, 2018 6:04 AMJeff, this is the method I was looking for and the results in are in line with what I wanted. Thank you so much for your help.
Thank you for the feedback, Lee. The key here, though, is do you understand how and why it works?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2018 at 6:33 am
Jeff Moden - Friday, April 6, 2018 6:22 AMLeeFAR - Friday, April 6, 2018 6:04 AMJeff, this is the method I was looking for and the results in are in line with what I wanted. Thank you so much for your help.Thank you for the feedback, Lee. The key here, though, is do you understand how and why it works?
Yes, I do and it's close to what a colleague and I came up with late yesterday.
April 6, 2018 at 8:23 am
LeeFAR - Friday, April 6, 2018 6:33 AMJeff Moden - Friday, April 6, 2018 6:22 AMLeeFAR - Friday, April 6, 2018 6:04 AMJeff, this is the method I was looking for and the results in are in line with what I wanted. Thank you so much for your help.Thank you for the feedback, Lee. The key here, though, is do you understand how and why it works?
Yes, I do and it's close to what a colleague and I came up with late yesterday.
Perfect. Thanks again for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply