declare @tbl as table(id int identity(1,1),leavestartdate datetime,leaveenddate datetime)
insert into @tbl
select '10-jan-2010','15-jan-2010' union all
select '20-jan-2010','22-jan-2010' union all
select '5-feb-2010','10-feb-2010' 
;with cte
as
(
select id,leavestartdate,leaveenddate,leavestartdate as leavedates from @tbl t1
union all
select t2.id,t2.leavestartdate,t2.leaveenddate,c1.leavedates+1 from @tbl t2
inner join cte c1 on c1.leavedates+1<=t2.leaveenddate and c1.id=t2.id
)

select id,leavedates
 from cte order by id --- ur where condition will come here
option (maxrecursion 0)

quote:

---

Originally posted by ambily

Thanks for the immediate response.
Sorry that i didnt mention the version.I am using SQL 2000 server but 2005 interface and is not accepting the cte.

I am trying to replace the cte with corresponding sql 2000 feature.

Please let me know if you could help with any available solution in SQL 2000 solution.

Thanks
Ambily

---

declare @tbl as table(id int identity(1,1),leavestartdate datetime,leaveenddate datetime)
insert into @tbl
select '10-jan-2010','15-jan-2010' union all
select '20-jan-2010','22-jan-2010' union all
select '5-feb-2009','10-feb-2009' 

select id,dates from
(
select id,leavestartdate,leaveenddate, DATEADD(dd,datediff(dd,0,m1.number),leavestartdate)as dates from master.dbo.spt_values m1
left join @tbl t1 on t1.leavestartdate<=DATEADD(dd,datediff(dd,0,m1.number),leavestartdate)
where type='p'

)t where dates between leavestartdate and leaveenddate

quote:

---

Originally posted by ambily

Wow.Thanks lot.
That solution made my day. :)

Thank you once again.

---