duplicating row

[vlitim76]

I have a table that has the following info:

name start date end date
entry1 08/01/2007 10/01/2007
entry2 09/01/2007 09/01/2007


I need to be able to split select multiple entries for the rows that have different dates, so for example I would get:

name start date end date
entry1 08/01/2007 10/01/2007
entry1 09/01/2007 10/01/2007
entry1 09/01/2007 10/01/2007
entry2 09/01/2007 09/01/2007

has anyone got any idea how I could achieve this?

cheers

Tim

 

[Catadmin]

We can't help without understanding your criteria for selecting and spliting the records.

For instance, why choose one record to duplicate over another? What are you looking for in the records you want to duplicate?

Your examples don't give us a lot to go off of.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[vlitim76]

basically any row that has a start and end date that differ I need to replicate for every date inbetween.

 

[r937]

so what you're saying is that you want to change this --

entry1 08/01/2007 10/01/2007

to this --

entry1 08/01/2007 08/02/2007
entry1 08/02/2007 08/03/2007
entry1 08/03/2007 08/04/2007
...
entry1 09/30/2007 10/01/2007

for a total of 62 rows, correct?

r937.com | rudy.ca

 

[vlitim76]

pretty much though I am using a date format of dd/mm/yyyy but the principal is the same.

 

[r937]

select name
     , dateadd(d,i,startdate) as [start date]
     , dateadd(d,i+1,startdate) as [end date]
  from daTable 
inner
  join integers  
     on dateadd(d,i+1,startdate) <= enddate

to make this work, you need an integers table consisting of a single column, i, which has values from 0 through to the largest difference you expect --

create table integers 
  (i integer not null primary key) 
insert into integers (i) values (0) 
insert into integers (i) values (1) 
insert into integers (i) values (2) 
insert into integers (i) values (3) 
insert into integers (i) values (4) 
insert into integers (i) values (5) 
insert into integers (i) values (6) 
insert into integers (i) values (7) 
insert into integers (i) values (8) 
insert into integers (i) values (9)

r937.com | rudy.ca