Loop through two dates columns-CodePudding

I want to loop through table has two dates column

My table like

 id        from               to
 1       01/01/2021        03/01/2021
 1       06/01/2021        07/01/2021

I expect result as

id             date
1            01/01/2021
1            02/01/2021
1            03/01/2021
1            06/01/2021
1            07/01/2021

CodePudding user response：

Given a tally table (a table with a single integer column that goes up to a bazillion or whatever you need), you cross join this using the number of days between your two dates:

with n as (select * from (values(0),(1),(2),(3),(4),(5))x(n)) /* represents a tally table */

select id, DateAdd(day,n.n,[from]) [date]
from t
cross join n
where n.n <= DateDiff(day,[from],[to])

CodePudding user response：

Every database should have some tally table to solve those problems...

A general fact is that "in a RDBMS database you will only retrieve the data that you have inserted" You will never find data that have not been put inside the DB...

Pesonnaly I ever create a number table, and also a calendar table. All those tally table are filled once before the database exploitation with all the rows that will be needed for query purpose.

As an example for your problem, a simple table like :

CREATE TABLE T_CALENDAR (CLD_DATE    DATE PRIMARY KEY);
DECLARE @D DATE = '2020-01-01';
WHILE @D < '2050-12-31'
BEGIN
   INSERT INTO T_CALENDAR (CLD_DATE) VALUES (@D);
   SET @D = DATEADD(day, 1, @D);
END;

The finest way is to store this table into a readonly storage (filegroup in MS SQL Server) because dates won't change... This avoid locking!

Then, solving your query is a child work :

SELECT CLD_DATE
FROM   MyTable AS T
       JOIN T_CALENDAR AS C ON T.MyDate BETWEEN "from" AND "to";

By the way, the use of SQL keyword as SQL identifier (table name, colum name...) is a bad idea !

CodePudding user response：

You can unfold such date ranges via a recursive CTE query

DROP TABLE IF EXISTS #t
CREATE TABLE #t (Id int, Fromdate datetime , Todate datetime)
INSERT INTO #t (Id, Fromdate, Todate)
VALUES ( 1,'2021/01/01','2021/01/03'),
       ( 1,'2021/01/06','2021/01/07'), 
       ( 2,'2021/02/01','2021/02/02')

WITH RCTE_DATES AS (
 SELECT Id, FromDate, ToDate
 , 1 AS Lvl
 , FromDate AS Dt
 FROM #t
 UNION ALL
 SELECT Id, FromDate, ToDate
 , Lvl   1
 , DATEADD(day, 1, Dt)
 FROM RCTE_DATES 
 WHERE Dt < ToDate
) 
SELECT Id, Dt
FROM RCTE_DATES
ORDER BY Id, Dt

Id | Dt                     
-: | :----------------------
 1 | 2021-01-01 00:00:00.000
 1 | 2021-01-02 00:00:00.000
 1 | 2021-01-03 00:00:00.000
 1 | 2021-01-06 00:00:00.000
 1 | 2021-01-07 00:00:00.000
 2 | 2021-02-01 00:00:00.000
 2 | 2021-02-02 00:00:00.000

db<>fiddle here