December 10, 2016 at 7:48 pm
I suspect there is both a CTE and a tally table solution here but I would prefer the tally solution
Given a table of start and end dates I want to generate a distinct list of dates including the dates between the start and end date of each record
DECLARE @DateRanges TABLE
(
StartDate DATE,
EndDate DATE
)
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-01','2016-12-01')--2016-12-01
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-01','2016-12-02')--2016-12-01 and 2016-12-02
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-04','2016-12-06')--2016-12-04, 2016-12-05, 2016-12-06
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-08','2016-12-11')--2016-12-08, 2016-12-09, 2016-12-10 and 2016-12-11
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-10','2016-12-12')--2016-12-10, 2016-12-11 and 2016-12-12
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-15','2016-12-15')--2016-12-15
SELECT * FROM @DateRanges
--Desired results
--2016-12-01
--2016-12-02
--2016-12-04
--2016-12-05
--2016-12-06
--2016-12-08
--2016-12-09
--2016-12-10
--2016-12-11
--2016-12-12
--2016-12-15
Appreciate any assistance offered.
December 10, 2016 at 9:43 pm
Quick example of an inline calendar CTE, you should be able to adjust it to your requirements.
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @DateRanges TABLE
(
StartDate DATE,
EndDate DATE
)
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-01','2016-12-01')--2016-12-01
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-01','2016-12-02')--2016-12-01 and 2016-12-02
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-04','2016-12-06')--2016-12-04, 2016-12-05, 2016-12-06
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-08','2016-12-11')--2016-12-08, 2016-12-09, 2016-12-10 and 2016-12-11
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-10','2016-12-12')--2016-12-10, 2016-12-11 and 2016-12-12
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-15','2016-12-15')--2016-12-15
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
,BASE_CALENDAR_CONFIG AS
(
SELECT
MIN(T.StartDate) AS FIRST_DATE
,DATEDIFF(DAY,MIN(T.StartDate),MAX(EndDate)) AS NUM_DAYS
FROM @DateRanges T
)
,NUMS(N) AS (SELECT TOP((SELECT BCC.NUM_DAYS FROM BASE_CALENDAR_CONFIG BCC) + 1) ROW_NUMBER() OVER (ORDER BY (@@VERSION)) - 1 AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,INLINE_CALENDAR AS
(
SELECT
NM.N AS DATE_NO
,DATEADD(DAY,NM.N,BC.FIRST_DATE) AS DATE_VAL
FROM BASE_CALENDAR_CONFIG BC
CROSS APPLY NUMS NM
)
SELECT
IC.DATE_NO
,IC.DATE_VAL
,DR.StartDate
FROM INLINE_CALENDAR IC
LEFT OUTER JOIN @DateRanges DR
ON IC.DATE_VAL = DR.StartDate;
Output
DATE_NO DATE_VAL StartDate
-------- ---------- ----------
0 2016-12-01 2016-12-01
0 2016-12-01 2016-12-01
1 2016-12-02 NULL
2 2016-12-03 NULL
3 2016-12-04 2016-12-04
4 2016-12-05 NULL
5 2016-12-06 NULL
6 2016-12-07 NULL
7 2016-12-08 2016-12-08
8 2016-12-09 NULL
9 2016-12-10 2016-12-10
10 2016-12-11 NULL
11 2016-12-12 NULL
12 2016-12-13 NULL
13 2016-12-14 NULL
14 2016-12-15 2016-12-15
December 10, 2016 at 10:52 pm
Because of the overlapping date ranges, this is one of those nasty problems where, unless you're really careful, accidental Cartesian Products work their way into the code. I agree that it won't hurt much for a small number of rows with rather narrow date ranges, like the given problem, but you can easily generate 90 internal rows on one leg, which is the Cartesian Product of the 15 unfiltered dates in the Min/Max range and the original 6 rows. Imagine what's going to happen with wider date ranges and more rows, perhaps with much more overlapping.
To get around that, we need to reduce the overlapping ranges to just one row each and then calculate only the dates we need. To do that, we'll use a bit of Itzik Ben-Gan's lightning fast overlapping range reducer code and then apply an fnTally function to create only the dates that we need. Here's the code. Don't let the length of the code scare you. It's nasty fast even with a table variable.
--===== Create the test "table".
-- This is NOT a part of the solution.
DECLARE @DateRanges TABLE
(
StartDate DATE,
EndDate DATE
)
;
--===== Populate the test table with the originally posted data.
INSERT INTO @DateRanges (StartDate,EndDate)
VALUES ('2016-12-01','2016-12-01')
,('2016-12-01','2016-12-02')
,('2016-12-04','2016-12-06')
,('2016-12-08','2016-12-11')
,('2016-12-10','2016-12-12')
,('2016-12-15','2016-12-15')
;
--===== Solve the problem
WITH
C1 AS
( --=== Mark the starts as +1 and the ends as -1 and assign counters to both start and end.
-- This also unpivots the dates
SELECT TS = StartDate
,Type = +1
,E = NULL
,S = ROW_NUMBER() OVER (ORDER BY StartDate)
FROM @DateRanges
UNION ALL
SELECT TS = DATEADD(dd,1,EndDate)
,Type = -1
,E = ROW_NUMBER() OVER(ORDER BY EndDate)
,S = NULL
FROM @DateRanges
)
,C2 AS
( --=== Add a straight incremental column where the adds come first on date ties
SELECT c1.*
,SE = ROW_NUMBER() OVER (ORDER BY TS, type DESC)
FROM C1 c1
)
,C3 AS
( --=== Find the first and last date for each overlapping group of dates
SELECT TS
,GrpNum = FLOOR((ROW_NUMBER() OVER(ORDER BY TS)-1)/2+1)
FROM C2
WHERE COALESCE(S-(SE-S)-1, (SE-E)-E) = 0
)
,C4 AS
( --=== Get the start and end of each overlapping group
SELECT StartDate = MIN(TS)
,EndDate = DATEADD(dd,-1,MAX(TS))
FROM C3
GROUP BY GrpNum
) --=== Generate only the dates we need
SELECT IncludedDate = DATEADD(dd,t.N,c4.StartDate)
FROM C4 c4
CROSS APPLY dbo.fnTally(0,DATEDIFF(dd,c4.StartDate,c4.EndDate)) t
;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 12, 2016 at 10:51 am
We really need to turn what you posted into an actual table. That means keys, constraints, etc.
CREATE TABLE Date_Ranges
(start_date DATE NOT NULL,
end_date DATE NOT NULL,
PRIMARY KEY (start_date, end_date),
CHECK(start_date <= end_date));
I am going to assume you have a calendar table somewhere. It is so much easier to do it once, and consistently add any other temporal information you need to it instead of reconstructing it over and over and over every time you need it.
SELECT DISTINCT C.cal_date
FROM Calendar AS C
WHERE EXISTS
(SELECT *
FROM Date_Ranges AS R
WHERE C.cal_date BETWEEN R.start_date AND R.end_date);
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
December 12, 2016 at 1:21 pm
Thank you to all. I got all solutions to work, the CTE just needed a little adjustment as the poster suggested. The calendar table is quite simple and is probably what I will use. I am a tally table fan which is why I stated that was my preferred solution but I may have let my bias and assumptions prejudge the best solution to the problem.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply