none
Create List of Dates with Variable Start Date and Fixed End Date AND Duplicate Dates for Surrogate Key Combinations

    Question

  • Hi

    Apologies for sending gobbledygook last time.

    Hopefully this attempt will make more sense.

    I have the source data below and I need to create a consecutive date row for each instance of a unique LocationID and ProductID combination beginning from the shown StartDate to a fixed EndDate (31-May-13).

    RowNr  |              StartDate             |              LocationID           |              ProductID

    1              |              02/05/2013         |              234                         |              1

    2              |              18/05/2013         |              234                         |              2

    3              |              27/05/2013         |              235                         |              1

    4              |              20/05/2013         |              235                         |              2

    5              |              21/05/2013         |              235                         |              7

    The script needs to take the data above and produce the following output.

    Date                      |              LocationID           |              ProductID

    02 May 2013       |              234                         |              1

    03 May 2013       |              234                         |              1

    04 May 2013       |              234                         |              1

    05 May 2013       |              234                         |              1

    06 May 2013       |              234                         |              1

    07 May 2013       |              234                         |              1

    08 May 2013       |              234                         |              1

    09 May 2013       |              234                         |              1

    10 May 2013       |              234                         |              1

    11 May 2013       |              234                         |              1

    12 May 2013       |              234                         |              1

    13 May 2013       |              234                         |              1

    14 May 2013       |              234                         |              1

    15 May 2013       |              234                         |              1

    16 May 2013       |              234                         |              1

    17 May 2013       |              234                         |              1

    18 May 2013       |              234                         |              1

    18 May 2013       |              234                         |              2

    19 May 2013       |              234                         |              1

    19 May 2013       |              234                         |              2

    20 May 2013       |              234                         |              1

    20 May 2013       |              234                         |              2

    20 May 2013       |              235                         |              2

    21 May 2013       |              234                         |              1

    21 May 2013       |              234                         |              2

    21 May 2013       |              235                         |              2

    21 May 2013       |              235                         |              7

    22 May 2013       |              234                         |              1

    22 May 2013       |              234                         |              2

    22 May 2013       |              235                         |              2

    22 May 2013       |              235                         |              7

    23 May 2013       |              234                         |              1

    23 May 2013       |              234                         |              2

    23 May 2013       |              235                         |              2

    23 May 2013       |              235                         |              7

    24 May 2013       |              234                         |              1

    24 May 2013       |              234                         |              2

    24 May 2013       |              235                         |              2

    24 May 2013       |              235                         |              7

    25 May 2013       |              234                         |              1

    25 May 2013       |              234                         |              2

    25 May 2013       |              235                         |              2

    25 May 2013       |              235                         |              7

    26 May 2013       |              234                         |              1

    26 May 2013       |              234                         |              2

    26 May 2013       |              235                         |              2

    26 May 2013       |              235                         |              7

    27 May 2013       |              234                         |              1

    27 May 2013       |              234                         |              2

    27 May 2013       |              235                         |              1

    27 May 2013       |              235                         |              2

    27 May 2013       |              235                         |              7

    28 May 2013       |              234                         |              1

    28 May 2013       |              234                         |              2

    28 May 2013       |              235                         |              1

    28 May 2013       |              235                         |              2

    28 May 2013       |              235                         |              7

    29 May 2013       |              234                         |              1

    29 May 2013       |              234                         |              2

    29 May 2013       |              235                         |              1

    29 May 2013       |              235                         |              2

    29 May 2013       |              235                         |              7

    30 May 2013       |              234                         |              1

    30 May 2013       |              234                         |              2

    30 May 2013       |              235                         |              1

    30 May 2013       |              235                         |              2

    30 May 2013       |              235                         |              7

    31 May 2013       |              234                         |              1

    31 May 2013       |              234                         |              2

    31 May 2013       |              235                         |              1

    31 May 2013       |              235                         |              2

    31 May 2013       |              235                         |              7

    The above output is generated due to the following.

    RowNr | StartDate | LocationID | ProductID                        Nr of Date Entries            Span

    1 | 02/05/2013   |              234         |              1                              30                                           02-May to 31-May

    2 | 18/05/2013   |              234         |              2                              14                                           18-May to 31-May

    3 | 27/05/2013   |              235         |              1                              5                                              27-May to 31-May

    4 | 20/05/2013   |              235         |              2                              12                                           20-May to 31-May

    5 | 21/05/2013   |              235         |              7                              11                                           21-May to 31-May

    Any help will be much appreciated.

    Thanks

    Lucy

    /*GENERATE SOURCE DATA*/
    
    CREATE TABLE SourceData (RowNr int identity NOT NULL
    
    ,StartDate datetime NOT NULL
    
    ,LocationID int NOT NULL
    
    ,ProductID int NOT NULL); 
    
    
    INSERT INTO SourceData (StartDate, LocationID, ProductID) VALUES ('02-May-13', 234, 1)
    
    INSERT INTO SourceData (StartDate, LocationID, ProductID) VALUES ('18-May-13', 234, 2)
    
    INSERT INTO SourceData (StartDate, LocationID, ProductID) VALUES ('27-May-13', 235, 1)
    
    INSERT INTO SourceData (StartDate, LocationID, ProductID) VALUES ('20-May-13', 235, 2)
    
    INSERT INTO SourceData (StartDate, LocationID, ProductID) VALUES ('21-May-13', 235, 7);
    
    
    /*GENERATE REQ'D OUTPUT DATA*/
    
    CREATE TABLE RequiredOutputData ([Date] datetime NOT NULL
    
    ,LocationID int NOT NULL
    
    ,ProductID int NOT NULL); 
    
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('02-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('03-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('04-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('05-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('06-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('07-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('08-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('09-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('10-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('11-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('12-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('13-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('14-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('15-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('16-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('17-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('18-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('19-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('20-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('21-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('22-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('23-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('24-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('25-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('26-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('27-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('28-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('29-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('30-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('31-May-13',234,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('18-May-13',234,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('19-May-13',234,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('20-May-13',234,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('21-May-13',234,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('22-May-13',234,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('23-May-13',234,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('24-May-13',234,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('25-May-13',234,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('26-May-13',234,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('27-May-13',234,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('28-May-13',234,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('29-May-13',234,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('30-May-13',234,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('31-May-13',234,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('27-May-13',235,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('28-May-13',235,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('29-May-13',235,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('30-May-13',235,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('31-May-13',235,1)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('20-May-13',235,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('21-May-13',235,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('22-May-13',235,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('23-May-13',235,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('24-May-13',235,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('25-May-13',235,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('26-May-13',235,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('27-May-13',235,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('28-May-13',235,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('29-May-13',235,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('30-May-13',235,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('31-May-13',235,2)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('21-May-13',235,7)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('22-May-13',235,7)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('23-May-13',235,7)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('24-May-13',235,7)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('25-May-13',235,7)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('26-May-13',235,7)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('27-May-13',235,7)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('28-May-13',235,7)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('29-May-13',235,7)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('30-May-13',235,7)
    
    INSERT INTO RequiredOutputData ([Date], LocationID, ProductID) VALUES ('31-May-13',235,7);
    

    • Edited by LucyUK Tuesday, July 23, 2013 10:34 AM
    Monday, July 22, 2013 4:38 PM

All replies

  • First, it is helpful, and usually important, to post actual DDL and sample data in the form of insert statements (or in a form that can be used to demonstrate / test query logic). 

    It isn't clear what logic you use to determine the following bit:

    RowNr
    1 – Should have 13 Date rows
    2 – Should have 13 Date rows
    3 – Should have 5 Date rows  
    4 – Should have 4 Date rows
    5 – Should have 4 Date rows

    This directive seems to be quite random, nor does it identify what values are to be generated - only quantities.  It may be that you simply need to propagate your starting set of rows by joining it to a table of dates (where that table contains those dates from your starting point to the desired end point).  Something like:

    /*using http://gallery.technet.microsoft.com/scriptcenter/97fe6de5-ab27-40db-8565-637988f028a2
    for dates 
    */
    declare @startDate datetime, @endDate datetime; 
    declare @querystart date, @queryend date;
    set @querystart = '20130519';
    set @queryend = '20130531';
     
    SET @startDate = '20130501'; 
    SET @endDate   = '20130531'; 
     
    WITH [dates] ([Sequence], [date], [year], [month], [day]) AS 
       (SELECT 1 AS [Sequence] 
              ,@startDate AS [date] 
              ,YEAR(@startdate) AS [year] 
              ,MONTH(@startdate) AS [month] 
              ,DAY(@startdate) AS [day] 
        UNION ALL 
        SELECT Sequence + 1 AS Sequence 
              ,DATEADD(d, 1, [date]) AS [date] 
              ,YEAR(DATEADD(d, 1, [date])) AS [year] 
              ,MONTH(DATEADD(d, 1, [date])) AS [month] 
              ,DAY(DATEADD(d, 1, [date])) AS [day] 
        FROM [dates] 
        WHERE [date] < @endDate) 
    select [dates].date, x.* from [dates]
    cross join 
    (select 1 as rownr, cast('20130502' as date) as startdate, 234 as locationid, 1 as itemid union all 
    select 2, '20130502', 234, 2 union all 
    select 3, '20130527', 235, 1 union all 
    select 4, '20130505', 234, 1 union all 
    select 5, '20130505', 234, 7 ) as x
    where (dates.date between @querystart and @queryend)
    and x.startdate <= dates.date
    order by x.rownr, [dates].date, x.locationid, x.itemid
    ;

    Monday, July 22, 2013 5:50 PM
  • Hi Scott

    I've just amended my post so it hopefully makes sense this time.

    Sorry for wasting your time earlier but I'd really appreciate your help.

    Thanks

    Lucy

    Tuesday, July 23, 2013 10:37 AM