List Dates between Start and End Date
-
Hi
I have a very simple query as below
select Personid, StartDate, endDate
from table
where startdate >= '01-feb-21'
Example Data
223, 01-Feb-22, 04-Feb-22
354, 21,Feb-22, 22-Feb-22
I want to display it now as follows, so listing the days between the startdate and enddate for each personid
Data would be as follows
223, 01-Feb-22
223, 02-Feb-22
223, 03-Feb-22
223, 04-Feb-22
354, 21-Feb-22
354, 22-Feb-22
Any help would be appreciated
-
Like this? (With thanks to Jeff Moden for the dates generator.)
DROP TABLE IF EXISTS #t1;
CREATE TABLE #t1
(
PersonId INT NOT NULL
,StartDate DATE NOT NULL
,EndDate DATE NOT NULL
);
INSERT #t1
(
PersonId
,StartDate
,EndDate
)
VALUES
(223, '20220201', '20220204')
,(354, '20220221', '20220222');
DECLARE @StartDate DATE
,@EndDate DATE
,@Days INT;
SELECT @StartDate = MIN(t.StartDate)
,@EndDate = MAX(t.EndDate)
FROM #t1 t;
SET @Days = DATEDIFF(dd, @StartDate, @EndDate);
WITH dates
AS (SELECT TOP (@Days + 1)
TheDate = DATEADD(dd, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, @StartDate)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2)
SELECT t.PersonId
,d.TheDate
FROM #t1 t
JOIN dates d
ON t.StartDate <= d.TheDate
AND t.EndDate >= d.TheDate; -
Phil is using the technique from this article: https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1
-
Install the table valued function in this script: https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function
IF OBJECT_ID('[dbo].[DateRange]','IF') IS NULL BEGIN
PRINT 'CREATE FUNCTION [dbo].[DateRange]'
EXEC ('CREATE FUNCTION [dbo].[DateRange] () RETURNS TABLE AS RETURN SELECT 1 X')
END
GO
/*-- **********************************************************************
-- FUNCTION: DateRange
-- Returns a table of datetime values based on the parameters
-- Parameters:
-- @StartDate :Start date of the series
-- @EndDate :End date of the series
-- @DatePart :The time unit for @interval
-- ns : nanoseconds
-- mcs : microseconds
-- ms : milliseconds
-- ss : seconds
-- mi : minutes
-- hh : hours
-- dd : days
-- ww : weeks
-- mm : months
-- qq : quarters
-- yy : years
-- @Interval :The number of dateparts between each value returned
--
-- Sample Calls:
-- SELECT * FROM [dbo].[DateRange]('2011-01-01 12:24:35', '2011-02-01 12:24:35', 'ss', 2)
-- SELECT COUNT(*) FROM [dbo].[DateRange]('2018-01-01 00:00:00', '2018-01-25 20:31:23.646', 'ms', default)
-- SELECT * FROM [dbo].[DateRange]('2011-01-01', '2012-02-03', default, default)
-- SELECT * FROM [dbo].[DateRange]('2012-02-03', '2011-01-01', 'dd', 7)
-- SELECT DATEDIFF(ns,'2018-01-01 00:00:00.000', value),Value,* FROM [dbo].[DateRange]('2018-01-01 00:00:00.000', '2018-01-01 00:00:00.00001', 'ns', 100)
-- **********************************************************************/
ALTER FUNCTION [dbo].[DateRange]
(
@StartDate datetime2,
@EndDate datetime2,
@DatePart nvarchar(3)='dd',
@Interval int=1
)
RETURNS TABLE AS RETURN
WITH A(A) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) A(A)),
B(RowNum) AS (SELECT TOP(ABS(CASE @DatePart
WHEN 'ns' THEN DATEDIFF(ns, @EndDate, @StartDate)/@Interval
WHEN 'mcs' THEN DATEDIFF(mcs,@EndDate, @StartDate)/@Interval
WHEN 'ms' THEN DATEDIFF(ms, @EndDate, @StartDate)/@Interval
WHEN 'ss' THEN DATEDIFF(ss, @EndDate, @StartDate)/@Interval
WHEN 'mi' THEN DATEDIFF(mi, @EndDate, @StartDate)/@Interval
WHEN 'hh' THEN DATEDIFF(hh, @EndDate, @StartDate)/@Interval
WHEN 'dd' THEN DATEDIFF(dd, @EndDate, @StartDate)/@Interval
WHEN 'ww' THEN DATEDIFF(ww, @EndDate, @StartDate)/@Interval
WHEN 'mm' THEN DATEDIFF(mm, @EndDate, @StartDate)/@Interval
WHEN 'qq' THEN DATEDIFF(qq, @EndDate, @StartDate)/@Interval
WHEN 'yy' THEN DATEDIFF(yy, @EndDate, @StartDate)/@Interval
ELSE DATEDIFF(dd, IIF(@StartDate < @EndDate, @StartDate, @EndDate), IIF(@StartDate < @EndDate, @EndDate, @StartDate))/@Interval
END) + 1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM A A, A B, A C, A D, A E, A F, A G, A H) -- A maximum of 16^8 (or 2^32) rows could be returned from this inline tally
SELECT CASE @DatePart
WHEN 'ns' THEN DATEADD(ns, T.AddAmount, @StartDate)
WHEN 'mcs' THEN DATEADD(mcs,T.AddAmount, @StartDate)
WHEN 'ms' THEN DATEADD(ms, T.AddAmount, @StartDate)
WHEN 'ss' THEN DATEADD(ss, T.AddAmount, @StartDate)
WHEN 'mi' THEN DATEADD(mi, T.AddAmount, @StartDate)
WHEN 'hh' THEN DATEADD(hh, T.AddAmount, @StartDate)
WHEN 'dd' THEN DATEADD(dd, T.AddAmount, @StartDate)
WHEN 'ww' THEN DATEADD(ww, T.AddAmount, @StartDate)
WHEN 'mm' THEN DATEADD(mm, T.AddAmount, @StartDate)
WHEN 'qq' THEN DATEADD(qq, T.AddAmount, @StartDate)
WHEN 'yy' THEN DATEADD(yy, T.AddAmount, @StartDate)
ELSE DATEADD(dd, T.AddAmount, @StartDate)
END [Value]
FROM B
CROSS APPLY(VALUES (IIF(@StartDate<@EndDate, @interval*RowNum, @interval*-RowNum))) T(AddAmount)
GOThe just call it like this:
DROP TABLE IF EXISTS #t1
CREATE TABLE #t1
(
PersonId INT NOT NULL
,StartDate DATE NOT NULL
,EndDate DATE NOT NULL
);
INSERT #t1
(
PersonId
,StartDate
,EndDate
)
VALUES
(223, '20220201', '20220204')
,(354, '20220221', '20220222');
select t.PersonId, CONVERT(date, d.Value, 6) Date
from #t1 t
cross apply dbo.DateRange(t.StartDate, t.EndDate, 'dd', 1) d
- Jonathan AC Roberts wrote:
Install the table valued function in this script: https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function
I'll say it again... that's a killer general purpose function with a lot of uses. Thanks for publishing that, Jonathan.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
@Ohil and @steve-2... thank you both for the honorable mention.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Jonathan AC Roberts wrote:
Install the table valued function in this script: https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function
I'll say it again... that's a killer general purpose function with a lot of uses. Thanks for publishing that, Jonathan.
Thank you Jeff
Wow, nearly at 1 million points!
- Phil Parkin wrote:
I've been called many things, but it's a first for Ohil
The "O" is apparently because "OH! More coffee, please!" 4 Letters and I phat-phingered 25% of them <headdesk>
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply