Date Range Intersections

Question

I have a BreakdownLog table with the following columns:

EquipmentID, ProblemID, BreakdownDate, IssueFixedDate

Each equipment can have multiple breakdowns, and more importantly, there could be overlapping date ranges for the same equipment!

For example, given below data:

EquipmentID|ProblemID|BreakdownDate|IssueFixedDate
1          |1        |01-Jun-2011  |01-Sep-2011
1          |2        |01-Jun-2011  |01-Oct-2011
2          |1        |01-Jun-2011  |01-Oct-2011
2          |2        |01-Jun-2011  |01-Oct-2011
3          |1        |15-Jun-2011  |01-Sep-2011
3          |2        |10-Jun-2011  |25-Aug-2011
4          |1        |01-Jun-2011  |01-Aug-2011
4          |2        |10-Sep-2011  |22-Oct-2011
5          |1        |01-Jun-2011  |15-Jun-2011
5          |2        |02-Jun-2011  |NULL

Now I want a query which can compute the number of days each equipment was defunct. If IssueFixedDate is NULL, we assume that the equipment is still defunct and therefore compute Defunct days upto current date.

The expected result set should be:

EquipmentID|DefunctDays
1          |122
2          |122
3          |83
4          |103
5          |143

I am using SQL Server 2008. So even CTEs, cursors etc are acceptable.

Thanks
Raghu

score 3 · Accepted Answer · edited May 23 '17 at 12:19

3

This uses master..spt_values as an adhoc auxiliary numbers table. You can create your own dedicated numbers table using one of the techniques here (start the numbering at zero)

;WITH Numbers
     AS (SELECT number
         FROM   master..spt_values
         WHERE  type = 'P')
SELECT EquipmentID,
       COUNT(DISTINCT number + DATEDIFF(DAY,0, BreakdownDate)) - 1 AS DefunctDays
FROM   BreakdownLog
       JOIN Numbers N
         ON number <= DATEDIFF(DAY, BreakdownDate,
                      ISNULL(IssueFixedDate, GETDATE()))
GROUP  BY EquipmentID

edited May 23 '17 at 12:19

Community

1
1

answered Oct 22 '11 at 11:02

Martin Smith

438,706
87
741
845

+1.... But aaaaah... It took me 15 minutes to comprehend it, and I'm not sure I would be able to rewrite it in the same way. – xanatos Oct 22 '11 at 11:48
I wouldn't use the spt_values table because of its undocumented nature. But a very simple query indeed when compared against the CTE given by xanatos. – Raghu Oct 23 '11 at 08:27
And I would give you another +1 for the link, because it's very interesting :-) – xanatos Oct 23 '11 at 08:28
Having said that, this is blazing fast compared to the CTE version. I am thinking of creating a numbers table myself instead of using the spt_values! – Raghu Oct 23 '11 at 08:48
@Raghu If you look at the benchmark in the example, you'll see that Itzik's CROSS JOINED CTE is quite inexpensive to use if you only need some thousand numbers. – xanatos Oct 23 '11 at 10:33
@xanatos - I would still create a non virtual indexed numbers table. I've found that if you try and use Itzik's one in Joins performance can suddenly plummet. – Martin Smith Oct 23 '11 at 10:36

xanatos · Answer 2 · 2011-10-22T11:52:20.737

It should be something like:

Test table initialization

DROP TABLE BreakdownLog 

CREATE TABLE BreakdownLog 
(
    EquipmentID INT,
    ProblemID INT,
    BreakdownDate DATETIME,
    IssueFixedDate DATETIME NULL
);

INSERT INTO BreakDownLog VALUES (1, 1, '01-Jun-2011', '01-Sep-2011')
INSERT INTO BreakDownLog VALUES (1, 2, '01-Jun-2011', '01-Oct-2011')
INSERT INTO BreakDownLog VALUES (2, 1, '01-Jun-2011', '01-Oct-2011')
INSERT INTO BreakDownLog VALUES (2, 2, '01-Jun-2011', '01-Oct-2011')
INSERT INTO BreakDownLog VALUES (3, 1, '15-Jun-2011', '01-Sep-2011')
INSERT INTO BreakDownLog VALUES (3, 2, '10-Jun-2011', '25-Aug-2011')
INSERT INTO BreakDownLog VALUES (4, 1, '01-Jun-2011', '01-Aug-2011')
INSERT INTO BreakDownLog VALUES (4, 2, '10-Sep-2011', '22-Oct-2011')
INSERT INTO BreakDownLog VALUES (5, 1, '01-Jun-2011', '15-Jun-2011')
INSERT INTO BreakDownLog VALUES (5, 2, '02-Jun-2011', NULL)

Real code

-- We exchange the NULLs in IssueFixedDate with the current date
; WITH Base AS (
    SELECT EquipmentID, ProblemID, BreakdownDate
        , ISNULL(IssueFixedDate
            , CONVERT(VARCHAR(10), GETDATE(), 101)) IssueFixedDate
    -- The previous line generates the current date without time
    FROM BreakDownLog
)

-- We generate a table with all the days the equipment was broken.
-- This is done through a recursive CTE
, BaseDays AS (
    SELECT EquipmentID, BreakdownDate AS DefunctDay, IssueFixedDate FROM Base
    UNION ALL
    SELECT EquipmentID, DefunctDay + 1 AS DefunctDay, IssueFixedDate
        FROM BaseDays   
        WHERE DefunctDay + 1 <= IssueFixedDate
    -- In T-SQL if you add 1 to a DateTime it's equivalent to adding a day
)

-- We make a distinct on the days where the equipment was broken, 
-- to delete days where the equipment was broken for two reasons
, BaseDaysDistinct AS (
    SELECT DISTINCT EquipmentID, DefunctDay 
        FROM BaseDays
)

-- We group the equipment's DefunctDays by EquipmentID
SELECT EquipmentID, COUNT(*) DefunctDays 
    FROM BaseDaysDistinct 
    GROUP BY EquipmentID

We could have changed the last two selects in:

SELECT EquipmentID, COUNT(DISTINCT DefunctDay) DefunctDays 
    FROM BaseDays
    GROUP BY EquipmentID

Simplifying I'm generating the list of days between BreakdownDate and IssueFixedDate using a recursive CTE, deleting the days that appear more than once and counting the days.

This works for now. However, I already have about 10k records in the table and it takes 8+ seconds to finish! The BreakdownLog table will continue to grow. Now the hunt is on to find a technique that will make this query faster. Anymore suggestions to make it are welcome. Thanks, Raghu — Raghu, Oct 23 '11 at 08:41
@Raghu If the repair time is always < 1027 you can use Martin Smith's solution, otherwise you can pre-generate a table of numbers like it's suggested in Martin Smith's link — xanatos, Oct 23 '11 at 08:45
You are right. 1027 days is a lot of time to fix an equipment! So, I am going to use Martin Smith's solution; only I am going to create a Numbers table myself instead of using the spt_values table. Thanks for the CTE based solution anyway. — Raghu, Oct 23 '11 at 09:22

Date Range Intersections

2 Answers2