Skip to main content
Database Administrators

Return to Answer

deleted 22 characters in body
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

Given the various simplifying assumptions given in the comments (Dates only and no overlapping intervals within a table) I might be minded to keep things simple.

First create an auxiliary numbers table (zero based)

CREATE TABLE dbo.SmallNumbers
(
Number SMALLINT PRIMARY KEY
)

INSERT INTO dbo.SmallNumbers
SELECT TOP 32768 ROW_NUMBER() OVER (ORDER BY @@SPID)-1 AS Number
FROM sys.all_columns c1, sys.all_columns c2

Then the code below expands out the ranges to their component dates, uses EXCEPT to find the difference and a gaps and islands technique to collapse the ranges back down. As each date will only be represented once we are likely only talking about expanding out to a few thousand dates per decade covered.

WITH UnmatchedDates(Date) AS
(
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table1
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
EXCEPT 
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table2
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
),
UnmatchedDatesWithGrp(Date, Grp) AS
(
SELECT Date, 
       DATEDIFF(DAY, 0, Date) - ROW_NUMBER() OVER (ORDER BY Date)
FROM UnmatchedDates
)
SELECT StartTs = MIN(Date), 
         EndTs = MAX(Date)
FROM UnmatchedDatesWithGrp
GROUP BY Grp

Given the various simplifying assumptions given in the comments (Dates only and no overlapping intervals within a table) I might be minded to keep things simple.

First create an auxiliary numbers table (zero based)

CREATE TABLE dbo.SmallNumbers
(
Number SMALLINT PRIMARY KEY
)

INSERT INTO dbo.SmallNumbers
SELECT TOP 32768 ROW_NUMBER() OVER (ORDER BY @@SPID)-1 AS Number
FROM sys.all_columns c1, sys.all_columns c2

Then the code below expands out the ranges to their component dates, uses EXCEPT to find the difference and a gaps and islands technique to collapse the ranges back down. As each date will only be represented once we are likely only talking about expanding out to a few thousand dates per decade covered.

WITH UnmatchedDates(Date) AS
(
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table1
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
EXCEPT 
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table2
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
),
UnmatchedDatesWithGrp(Date, Grp) AS
(
SELECT Date, 
       DATEDIFF(DAY, 0, Date) - ROW_NUMBER() OVER (ORDER BY Date)
FROM UnmatchedDates
)
SELECT StartTs = MIN(Date), 
         EndTs = MAX(Date)
FROM UnmatchedDatesWithGrp
GROUP BY Grp

Given the various simplifying assumptions (Dates only and no overlapping intervals within a table) I might be minded to keep things simple.

First create an auxiliary numbers table (zero based)

CREATE TABLE dbo.SmallNumbers
(
Number SMALLINT PRIMARY KEY
)

INSERT INTO dbo.SmallNumbers
SELECT TOP 32768 ROW_NUMBER() OVER (ORDER BY @@SPID)-1 AS Number
FROM sys.all_columns c1, sys.all_columns c2

Then the code below expands out the ranges to their component dates, uses EXCEPT to find the difference and a gaps and islands technique to collapse the ranges back down. As each date will only be represented once we are likely only talking about expanding out to a few thousand dates per decade covered.

WITH UnmatchedDates(Date) AS
(
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table1
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
EXCEPT 
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table2
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
),
UnmatchedDatesWithGrp(Date, Grp) AS
(
SELECT Date, 
       DATEDIFF(DAY, 0, Date) - ROW_NUMBER() OVER (ORDER BY Date)
FROM UnmatchedDates
)
SELECT StartTs = MIN(Date), 
         EndTs = MAX(Date)
FROM UnmatchedDatesWithGrp
GROUP BY Grp
deleted 2 characters in body
Source Link
Martin Smith
Martin Smith
  • 86.5k
  • 15
  • 252
  • 342

Given the various simplifying assumptions given in the comments (Dates only and no overlapping intervals within a table) I might be minded to keep things simple.

First create an auxiliary numbers table (zero based)

CREATE TABLE dbo.SmallNumbers
(
Number SMALLINT PRIMARY KEY
)

INSERT INTO dbo.SmallNumbers
SELECT TOP 32768 ROW_NUMBER() OVER (ORDER BY @@SPID)-1 AS Number
FROM sys.all_columns c1, sys.all_columns c2

Then the code below expands out the ranges to their component dates, uses EXCEPT to find the difference and a gaps and islands technique to collapse the ranges back down. As each date will only be represented once we are likely only talking about expanding out to a few thousand dates per decade covered.

WITH UnmatchedDates(Date) AS
(
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table1
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
EXCEPT 
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table2
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
),
UnmatchedDatesWithPrevUnmatchedDatesWithGrp(Date, Grp) AS
(
SELECT Date, 
       DATEDIFF(DAY, 0, Date) - ROW_NUMBER() OVER (ORDER BY Date)
FROM UnmatchedDates
)
SELECT StartTs = MIN(Date), 
         EndTs = MAX(Date)
FROM UnmatchedDatesWithPrevUnmatchedDatesWithGrp
GROUP BY Grp

Given the various simplifying assumptions given in the comments (Dates only and no overlapping intervals within a table) I might be minded to keep things simple.

First create an auxiliary numbers table (zero based)

CREATE TABLE dbo.SmallNumbers
(
Number SMALLINT PRIMARY KEY
)

INSERT INTO dbo.SmallNumbers
SELECT TOP 32768 ROW_NUMBER() OVER (ORDER BY @@SPID)-1 AS Number
FROM sys.all_columns c1, sys.all_columns c2

Then the code below expands out the ranges to their component dates, uses EXCEPT to find the difference and a gaps and islands technique to collapse the ranges back down. As each date will only be represented once we are likely only talking about expanding out to a few thousand dates per decade covered.

WITH UnmatchedDates(Date) AS
(
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table1
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
EXCEPT 
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table2
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
),
UnmatchedDatesWithPrev(Date, Grp) AS
(
SELECT Date, 
       DATEDIFF(DAY, 0, Date) - ROW_NUMBER() OVER (ORDER BY Date)
FROM UnmatchedDates
)
SELECT StartTs = MIN(Date), 
         EndTs = MAX(Date)
FROM UnmatchedDatesWithPrev
GROUP BY Grp

Given the various simplifying assumptions given in the comments (Dates only and no overlapping intervals within a table) I might be minded to keep things simple.

First create an auxiliary numbers table (zero based)

CREATE TABLE dbo.SmallNumbers
(
Number SMALLINT PRIMARY KEY
)

INSERT INTO dbo.SmallNumbers
SELECT TOP 32768 ROW_NUMBER() OVER (ORDER BY @@SPID)-1 AS Number
FROM sys.all_columns c1, sys.all_columns c2

Then the code below expands out the ranges to their component dates, uses EXCEPT to find the difference and a gaps and islands technique to collapse the ranges back down. As each date will only be represented once we are likely only talking about expanding out to a few thousand dates per decade covered.

WITH UnmatchedDates(Date) AS
(
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table1
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
EXCEPT 
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table2
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
),
UnmatchedDatesWithGrp(Date, Grp) AS
(
SELECT Date, 
       DATEDIFF(DAY, 0, Date) - ROW_NUMBER() OVER (ORDER BY Date)
FROM UnmatchedDates
)
SELECT StartTs = MIN(Date), 
         EndTs = MAX(Date)
FROM UnmatchedDatesWithGrp
GROUP BY Grp
Source Link
Martin Smith
Martin Smith
  • 86.5k
  • 15
  • 252
  • 342

Given the various simplifying assumptions given in the comments (Dates only and no overlapping intervals within a table) I might be minded to keep things simple.

First create an auxiliary numbers table (zero based)

CREATE TABLE dbo.SmallNumbers
(
Number SMALLINT PRIMARY KEY
)

INSERT INTO dbo.SmallNumbers
SELECT TOP 32768 ROW_NUMBER() OVER (ORDER BY @@SPID)-1 AS Number
FROM sys.all_columns c1, sys.all_columns c2

Then the code below expands out the ranges to their component dates, uses EXCEPT to find the difference and a gaps and islands technique to collapse the ranges back down. As each date will only be represented once we are likely only talking about expanding out to a few thousand dates per decade covered.

WITH UnmatchedDates(Date) AS
(
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table1
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
EXCEPT 
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table2
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
),
UnmatchedDatesWithPrev(Date, Grp) AS
(
SELECT Date, 
       DATEDIFF(DAY, 0, Date) - ROW_NUMBER() OVER (ORDER BY Date)
FROM UnmatchedDates
)
SELECT StartTs = MIN(Date), 
         EndTs = MAX(Date)
FROM UnmatchedDatesWithPrev
GROUP BY Grp