How do I Create a Temp table with Incr dates for each day

  • Hi All

    Sorry for being dim but it's been a long day!

    I need to create a temp table with 900 unique varchar(50) values from a table and give each one a date incremented by one day so for each value in the first table I should have the following

    CustomerIDIncrementedDate MachineCount

    000099562000/01/01Populated Later

    000099562000/01/02Populated Later

    000099562000/01/03Populated Later

    000099562000/01/04Populated Later

    000099562000/01/05Populated Later

    000099562000/01/06Populated Later

    000099562000/01/07Populated Later

    000099562000/01/08Populated Later

    000088882000/01/01Populated Later

    000088882000/01/02Populated Later

    000088882000/01/03Populated Later

    000088882000/01/04Populated Later

    000088882000/01/05Populated Later

    000088882000/01/06Populated Later

    000088882000/01/07Populated Later

    000088882000/01/08Populated Later

    Up to GetDate () today

  • You can create a table with the needed date and then use a cross join with your original table in order to get all the combinations between each date and each record in the original table.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Not a real elegant solution but would this work for you?

    declare @customer table (customerid varchar(8))

    declare @Tmp table (incrdate datetime)

    declare @maxdate datetime

    insert into @customer (customerid) values ('00009956')

    insert into @customer (customerid) values ('00008888')

    insert into @Tmp (incrdate) values ('1/1/2010')

    set @maxdate = dateadd(dd,5,'1/1/2010')

    while @maxdate != (select max(incrdate) from @Tmp)

    insert into @Tmp (incrdate) select dateadd(dd,1,max(incrdate)) from @Tmp

    select c.customerid, t.incrdate

    from @customer c cross join @Tmp t

    order by 1, 2

    _____________________________________________________________________
    - Nate

    @nate_hughes
  • I wouldn't really use a while loop for it nor would I order by ordinal column position.

    I'd rather go with a set based solution based on the Tally table concept (see the corresponding link in my signature for more details).

    declare @customer table (customerid varchar(8))

    declare @startdate datetime

    declare @maxdate int

    insert into @customer (customerid) values ('00009956')

    insert into @customer (customerid) values ('00008888')

    set @startdate='20100101'

    set @maxdate = 5

    select c.customerid, dateadd(dd,n,@startdate) as incrdate

    from @customer c

    cross join

    (select number n from master..spt_values where type ='P' and number < @maxdate) sub

    order by c.customerid, sub.n

    Edit: CROSS APPLY change to CROSS JOIN due to SQL2000 forum.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Just to take it one step further, the changes to Lutz's code below will bring it to the current date:

    declare @customer table (customerid varchar(8))

    declare @startdate datetime

    declare @maxdate INT

    insert into @customer (customerid) values ('00009956')

    insert into @customer (customerid) values ('00008888')

    set @startdate='20100101'

    set @maxdate = DATEDIFF(dd, @startdate, GETDATE())

    select c.customerid, dateadd(dd,n,@startdate) as incrdate

    from @customer c

    cross join

    (select number n from master..spt_values where type ='P' and number <= @maxdate) sub

    order by c.customerid, sub.n

  • Many thanks for the help sorted now 🙂

  • Glad we could help 🙂



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply