Reg: Date Requirements

Question

Reg: Date Requirements

vs.satheesh

SSCrazy

Points: 2392
More actions
November 8, 2012 at 6:58 am

#269979

Hi
In 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...

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

vs.satheesh (11/8/2012)
Hi
In 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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

DECLARE @Fromdate DATE = '01 Jan 2012'

,@Todate DATE = '30 Dec 2012'

,@Interval INT = 2

SELECT DateC , N

FROM ( SELECT DATEADD(DAY, N, @Fromdate ) AS DateC, N

FROM (SELECT (ROW_NUMBER() OVER (ORDER BY [object_id]) - 1) * (@Interval + 1)

FROM sys.columns) dt(N)

) R

WHERE DateC <= @Todate

Note that I used sys.columns as in-run-time tally table. You may want to create and use proper tally table.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

vs.satheesh SSCrazy Points: 2392 More actions · Answer 3

vs.satheesh

SSCrazy

Points: 2392

November 8, 2012 at 10:46 am

#1557290

Thank You