January 19, 2010 at 2:07 am
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
CustomerID IncrementedDate MachineCount
00009956 2000/01/01 Populated Later
00009956 2000/01/02 Populated Later
00009956 2000/01/03 Populated Later
00009956 2000/01/04 Populated Later
00009956 2000/01/05 Populated Later
00009956 2000/01/06 Populated Later
00009956 2000/01/07 Populated Later
00009956 2000/01/08 Populated Later
00008888 2000/01/01 Populated Later
00008888 2000/01/02 Populated Later
00008888 2000/01/03 Populated Later
00008888 2000/01/04 Populated Later
00008888 2000/01/05 Populated Later
00008888 2000/01/06 Populated Later
00008888 2000/01/07 Populated Later
00008888 2000/01/08 Populated Later
Up to GetDate () today
January 19, 2010 at 2:16 am
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/
January 19, 2010 at 7:43 am
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
January 19, 2010 at 7:55 am
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.
January 21, 2010 at 2:03 am
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
January 21, 2010 at 11:18 am
Many thanks for the help sorted now 🙂
January 21, 2010 at 12:50 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply