CODEWITHSUNDEEP
sqldate
Jake
Jake

Reputation: 2912

SQL: Convert table with start-end dates to just dates retaining other columns

I understand that a number of similar questions have been answered, but I think none seems to be specific to this? I might be wrong as I am still quite new to SQL too. Hence, appreciate if anyone can kindly explain and point me the right direction.

A sample table is as shown below, with start and end dates, and one more column with some text details. Some of the end dates are NULL.

Start Date  End Date    Day
25-05-15    28-05-15    text1
28-05-15                text2
30-05-15                text3
01-12-15                text4
31-05-16    02-06-16    text5
07-07-16                text6

I want to convert this table to a new one just showing all the days while retaining the "Day" column specific to the dates.

Date        Day
25-05-15    text1
26-05-15    text1
27-05-15    text1
28-05-15    text1
28-05-15    text2
30-05-15    text3
01-12-15    text4
31-05-16    text5
01-06-16    text5
02-06-16    text5
07-07-16    text6

Can anyone help? Thanks in advance!

Upvotes: 0

Views: 162

Answers (3)

Paul Spiegel
Paul Spiegel

Reputation: 31812

If you have a helper table with all dates you would ever need (e.g. 30K dates from 1970-01-01), you can join it with your table on the given date range. This query is for MySQL:

select d.`Date`, t.`Day`
from mytable t
join `dates` d
  on  d.`Date` >= str_to_date(t.`Start Date`, '%d-%m-%y')
  and d.`Date` <= str_to_date(coalesce(`End Date`, `Start Date`), '%d-%m-%y')

http://sqlfiddle.com/#!9/2e3e1/1

You can create that helper table on the fly or store it in your db for later use. How to create that table depends on your RDBMS.

Upvotes: 1

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Try with the below script for SQL server.

DROP TABLE #T
GO

CREATE TABLE #T
(StartDAte DATETIME,
 EndDate DATETIME,
 Day VARCHAR(50))


 INSERT INTO #T
 VALUES ('05-25-15','05-28-15','text1'), ('05-28-15',NULL,'text2')
        ,('05-30-15',NULL,'text3'),('12-01-15',NULL,'text4')
        ,('05-31-16','06-02-16','text5'),('07-07-16',NULL,'text6')

Below script will give you the result set. 

 SELECT [Date],[Day] 
 FROM #T
   CROSS APPLY (SELECT DATEADD(DAY,number,StartDAte) [Date]
                FROM master..spt_values
                WHERE type = 'P'
                AND DATEADD(DAY,number,StartDAte) < =ISNULL(EndDate,StartDate))t

Upvotes: 0

Robert Kock
Robert Kock

Reputation: 6018

You could try to do this within a script (this one is Oracle):

DECLARE

  CURSOR dt_cursor IS
    SELECT StartDate               AS FromDate,
           NVL(EndDate, StartDate) AS ToDate,
           Day
    FROM   myTable;

  dt_val  dt_cursor%ROWTYPE;
  my_date DATE;

BEGIN

  FOR dt_val IN dt_cursor LOOP

    my_date = dt_val.FromDate;
    WHILE (my_date <= dt_val.ToDate) LOOP

      INSERT INTO newTable (Date, Day) VALUES (my_date, dt_val.Day);
      my_date := my_date + 1;

    END LOOP;

  END LOOP;

END;

Upvotes: 0

Related Questions