## Reg: Date Requirements

 Author Message vs.satheesh SSC-Enthusiastic Group: General Forum Members Points: 187 Visits: 644 Hi In my requirementFor example,From date : 01-01-2012To date : 30-12-2012Intervel : 2 or 3 or 4 ....NI want follwing result set based on the intervel.suppose intervel 1 means01-01-201203-01-201205-01-2012....30-12-2012suppose intervel 2 means01-01-201204-01-201207-01-2012....30-12-2012.I want result based on intervel... Lynn Pettis SSC-Dedicated Group: General Forum Members Points: 39001 Visits: 38508 vs.satheesh (11/8/2012)Hi In my requirementFor example,From date : 01-01-2012To date : 30-12-2012Intervel : 2 or 3 or 4 ....NI want follwing result set based on the intervel.suppose intervel 1 means01-01-201203-01-201205-01-2012....30-12-2012suppose intervel 2 means01-01-201204-01-201207-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 rowsselect top ((datediff(dd, @StartDate, @EndDate) + @Interval + 1)/(@Interval + 1)) dateadd(dd, (1 + @Interval) * (n - 1), @StartDate)from eTally` Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) Eugene Elutin SSCarpal Tunnel Group: General Forum Members Points: 4960 Visits: 5478 `DECLARE @Fromdate DATE = '01 Jan 2012' ,@Todate DATE = '30 Dec 2012' ,@Interval INT = 2SELECT DateC , NFROM ( 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) ) RWHERE 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 vs.satheesh SSC-Enthusiastic Group: General Forum Members Points: 187 Visits: 644 Thank You