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

Question

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

Northern Monkey

Ten Centuries

Points: 1017
More actions
January 19, 2010 at 2:07 am

#73476

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

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

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

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/

RP_DBA SSCertifiable Points: 5116 More actions · Answer 2

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

LutzM SSC Guru Points: 107049 More actions · Answer 3

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]

Simon Liddle SSCrazy Points: 2580 More actions · Answer 4

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

Northern Monkey Ten Centuries Points: 1017 More actions · Answer 5

Northern Monkey

Ten Centuries

Points: 1017

January 21, 2010 at 11:18 am

#1106859

Many thanks for the help sorted now 🙂

LutzM SSC Guru Points: 107049 More actions · Answer 6

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]