Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Reg: Date Requirements Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, November 08, 2012 6:58 AM
 Valued Member Group: General Forum Members Last Login: Monday, November 25, 2013 12:11 AM Points: 57, Visits: 336
 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...
Post #1382469
 Posted Thursday, November 08, 2012 7:24 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 8:12 PM Points: 22,092, Visits: 29,013
 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`
Post #1382487
 Posted Thursday, November 08, 2012 7:29 AM
 SSCrazy Group: General Forum Members Last Login: Wednesday, November 20, 2013 10:00 AM Points: 2,719, Visits: 4,724
 `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!"(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help
Post #1382492
 Posted Thursday, November 08, 2012 10:46 AM
 Valued Member Group: General Forum Members Last Login: Monday, November 25, 2013 12:11 AM Points: 57, Visits: 336
 Thank You
Post #1382631

 Permissions