vs.satheesh (11/8/2012)
HiIn my requirement
For example,
From date : 01-01-2012
To date : 30-12-2012
Intervel : 2 or 3 or 4 ....N
I want follwing result set based on the intervel.
suppose intervel 1 means
01-01-2012
03-01-2012
05-01-2012
.
.
.
.
30-12-2012
suppose intervel 2 means
01-01-2012
04-01-2012
07-01-2012
.
.
.
.
30-12-2012.
I want result based on intervel...
You may need to play with a little, but it seems to work based on your requirements. When I get more time, I look at it in more depth.
declare @StartDate date = '20120101',
@EndDate date = '20121230',
@Interval int = 2;
with e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), -- 10 rows
e2(n) as (select 1 from e1 a cross join e1 b), -- 100 rows
e4(n) as (select 1 from e2 a cross join e2 b), -- 10,000 rows
eTally(n) as (select row_number() over (order by (select null)) from e4 a cross join e2 b) -- 1,000,000 rows
select top ((datediff(dd, @StartDate, @EndDate) + @Interval + 1)/(@Interval + 1))
dateadd(dd, (1 + @Interval) * (n - 1), @StartDate)
from
eTally