Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reg: Date Requirements Expand / Collapse
Author
Message
Posted Thursday, November 8, 2012 6:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:25 PM
Points: 63, Visits: 419
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...



Post #1382469
Posted Thursday, November 8, 2012 7:24 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 20,857, Visits: 32,877
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






Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1382487
Posted Thursday, November 8, 2012 7:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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!"
(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 8, 2012 10:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:25 PM
Points: 63, Visits: 419
Thank You
Post #1382631
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse