DATEADD Requirement

Question

Post reply

DATEADD Requirement

rahulme81

Newbie

Points: 6
More actions
July 22, 2013 at 4:24 am

#280696

I have a requirement like... Add a week to the initial date provided till end of current month of initial date... Once the date cross the current month then it goes in month calculation.
Sample Output
start date end date
01-07-2013 08-07-2013
08-07-2013 15-07-2013
15-07-2013 22-07-2013
22-07-2013 29-07-2013
29-07-2013 01-08-2013
01-08-2013 01-09-2013
01-09-2013 01-10-2013
01-11-2013 01-12-2013
SELECT @date_start = '20130701', @nb_period = 12, @nb_unit =1, @i = 1
TRUNCATE TABLE #dates
SELECT @date_end = CONVERT(VARCHAR(25),DATEADD(WEEK,@nb_unit,@date_start ),101)
INSERT INTO #dates (date_debut,date_end ) VALUES (@date_debut,@date_end )
WHILE (@i < @nb_period)
BEGIN
SELECT @date_start = @date_end
SELECT @date_end = CONVERT(VARCHAR(25),DATEADD(WEEK,@nb_unit,@date_start ),101)
INSERT INTO #dates (date_start ,date_end ) VALUES (@date_start ,@date_end )
SELECT @i = @i +1
END
SELECT * FROM #dates
I am not able to get the monthly flow once the week finish in current month. (Line no 5 onwards).
Please advice.

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Use an inline tally table as a row generator. Generate the week-based rows and the month-based rows separately:

DROP TABLE #dates

CREATE TABLE #dates (date_debut DATE,date_end DATE)

DECLARE @date_start DATE, @date_end DATE, @date_debut DATE,

@nb_period INT, @nb_unit INT, @i INT

SELECT @date_debut = '20130701'

SELECT @date_end = @date_debut, @nb_period = 12, @nb_unit =1, @i = 1

;WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

cteTally(N) AS (SELECT TOP (@nb_period) -1+ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)

INSERT INTO #dates

SELECT TOP (@nb_period) Startdate, Enddate

FROM (

SELECT

x.Startdate,

Enddate = CASE

WHEN DATEDIFF(MONTH,@date_debut,x.Enddate) = 0 THEN x.Enddate

ELSE DATEADD(MONTH,DATEDIFF(MONTH,0,x.Enddate),0) END

FROM cteTally

CROSS APPLY (

SELECT

Startdate = DATEADD(DAY,n*7,@date_debut),

Enddate = DATEADD(DAY,(N+1)*7,@date_debut)

) x

WHERE DATEDIFF(MONTH,@date_debut,Startdate) = 0

UNION ALL

SELECT

x.Startdate,

x.Enddate

FROM cteTally

CROSS APPLY (

SELECT

Startdate = DATEADD(MONTH,n+1+DATEDIFF(MONTH,0,@date_debut),0),

Enddate = DATEADD(MONTH,n+2+DATEDIFF(MONTH,0,@date_debut),-1)

) x

) d

SELECT * FROM #dates

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rahulme81 Newbie Points: 6 More actions · Answer 2

Thanks Chris for the needed and help.

Unfortunately I have to write a query in Sybase.

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

rahulme81 (7/22/2013)
Thanks Chris for the needed and help.
Unfortunately I have to write a query in Sybase.

Have you tried to google "sybase forum"? You may find more releavnt help there...

😉

_____________________________________________
"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]