0

Hi I have a table as below,

Id      StartDt          EndDt       
-----   --------         -------
123     01-Apr-2016      15-Apr-2016
456      02-Apr-2016     03-Apr-2016

I want to write a generic sql Insert statement that will work in all DB platforms.

So that I will insert a data similar to below into a new table from the above one

New Table value after Insert:

Id       Date           SeqNo
123      01-Apr-2016       1
123      02-Apr-2016       2
123      03-Apr-2016       3
123      04-Apr-2016       4
123      05-Apr-2016       5
123      06-Apr-2016       6
123      07-Apr-2016       7
123      08-Apr-2016       8
..
..
..
..
123      15-Apr-2016       15,


AssignmentID    Date    SeqNo
123 1-Apr   1
123 2-Apr   2
123 3-Apr   3
123 4-Apr   4
123 5-Apr   5
.       
.       
.       
.       
.       
123 15-Apr  15

So fifteen rows for the first row i.e (End Date - Start Date) and similarly two rows for the second row from the main table(End Date - Start Date).

sqluser
  • 5,502
  • 7
  • 36
  • 50
Saeem Ahamed
  • 1
  • You would have to write a procedure for this which would be slightly different depending on which type of SQL you are using. Do you have a specific type of SQL you would like this geared towards? – Nathan Bierema Apr 21 '16 at 03:45
  • Why all those dbms products? Are you really using all of MySQL, Oracle and Sybase here??? – jarlh Apr 21 '16 at 06:49

2 Answers2

0

You'd create an intervals table, containing all integers from 0 to 9999. You can do this programmatically:

CREATE TABLE intervals (period int);
INSERT INTO intervals (period) VALUES (0),(1);
DECLARE @rowCnt int;
SELECT @rowCnt = 2;
WHILE (SELECT Count(*) FROM intervals) < 10000 BEGIN
    INSERT INTO intervals (period) SELECT period + @rowCnt FROM intervals WHERE period + @rowCnt < 10000;
    SELECT @rowCnt = @rowCnt * 2
END;

The first time the loop runs, it inserts 2 rows with the values 2 & 3, then 4 rows etc, until the table is full.

Then you insert by cross joining this table with your table containing dates, and insert (start date + intervals (days)) where start date + interval <= finish date.

John Bingham
  • 1,996
  • 1
  • 12
  • 15
  • Hi John,The Start Date and End Date difference can reach upto 2000 i.e the Start Date can be 01Apr2016 and the end date be 01Apr2021. So do you think its feasible to create a Intervals table, and also that the Intervals table can be preset with a fixed number of rows. This can vary from few 100s to 2000 or even 3000. Also can you please let me know how I can create the Intervals table with SQL. – Saeem Ahamed Apr 21 '16 at 05:05
0

This will be best done in an UDTF. The UDTF will have the input 2 values and export the date range. This implementation will be similar to Explode.

user 923227
  • 2,528
  • 4
  • 27
  • 46