CODEWITHSUNDEEP
sqlsql-serverpostgresqldate-range
tsouchlarakis
tsouchlarakis

Reputation: 1619

How to Duplicate Records According to Start and End Dates

I have records in a SQL database that have a startDate and endDate that I need to expand.

| userName    | startDate  | endDate    | weekDay |  
| :---------: | :--------: | :--------: | :-----: |  
| Test User 1 | 2011-03-30 | 2011-04-05 | 1       |  
| Test User 2 | 2016-10-05 | 2016-10-07 | 5       |  
| Test User 3 | 2018-05-22 | 2018-05-26 | 4       |

In the table above, each record has information that covers more than one date. What I need is one record per one date per user. An example of what I'm looking for:

| userName    | startDate  | weekDay    |
| :---------: | :--------: | :--------: |
| Test User 1 | 2011-03-30 | 1          |
| Test User 1 | 2011-03-31 | 1          |
| Test User 1 | 2011-04-01 | 1          |
| Test User 1 | 2011-04-02 | 1          |
| Test User 1 | 2011-04-03 | 1          |
| Test User 1 | 2011-04-04 | 1          |
| Test User 1 | 2011-04-05 | 1          |
| Test User 2 | 2016-10-05 | 5          |
| Test User 2 | 2016-10-06 | 5          |
| Test User 2 | 2016-10-07 | 5          |
| Test User 3 | 2018-05-22 | 4          |
| Test User 3 | 2018-05-23 | 4          |
| Test User 3 | 2018-05-24 | 4          |
| Test User 3 | 2018-05-25 | 4          |
| Test User 3 | 2018-05-26 | 4          |

This answer has gotten me a step closer, specifying how to generate a sequence of dates in SQL. How can I duplicate tabular records according to start and end dates in SQL?

As a note, I need this solution to work in both MSSQL and PostgreSQL.

Upvotes: 2

Views: 1754

Answers (2)

Sahi
Sahi

Reputation: 1484

try below code. I used recursive common table expression.

;with cte
AS
(
  SELECT userName,startDate,startDate AS endDate,weekDay FROM tab1
    Union all
  SELECT t1.userName,DATEADD(d,1,t1.startdate) AS startDate,
  DATEADD(d,1,t1.startdate) AS startDate,t1.weekDay
  FROM cte t1
 JOIN tab1 t2 on t1.userName=t2.userName
 WHERE t2.endDate>t1.endDate
) 

 Select userName,startDate,weekDay from cte order by userName

SQL Fiddle: http://sqlfiddle.com/#!18/fa22a/3

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You can use a recursive CTE in both SQL Server and Postgres, but the syntax is slightly different. And, there is a simpler method in Postgres. So, in SQL Server, you can do:

with cte as (
      select username, startdate, weekday, enddate
      from t
      union all
      select username, dateadd(day, 1, startdate) weekday, enddate
      from cte
      where startdate < enddate
     )
select username, startdate, weekday
from cte
order by username, startdate;

You can adjust the date arithmetic and add the recursive keyword for Postgres.

The simpler method in Postgres is a lateral join:

select t.username, g.startdate, t.weekday
from t, lateral
     generate_series(start_date, end_date, interval '1 day') g(startdate);

If you need the same code to work in both, you need to generate a numbers table. Here is one (unpleasant) method:

with digits as (
      select v.n
      from (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(n)
     ),
     n as (
      select d1.n * 100 + d2.n * 10 + d3.n as n
      from digits d1 cross join digits d2 cross join digits d3
     )
select t.username, t.startdate + n.n, t.weekday
from t join
     n 
     on t.startdate + n.n <= t.enddate;

Note that for this to work startdate needs to be a datetime in SQL Server, but a date in Postgres.

Upvotes: 3

Related Questions