calculate months days for start and end date (each month)

Question

calculate months days for start and end date (each month)

spin

Hall of Fame

Points: 3274
More actions
November 25, 2013 at 11:06 am

#282846

Hi
i want to be able to return the number of days for each month in a given start/end date.
so, start date 15/01/13; end date 07/03/13
the result would be :
Jan = 16
Feb = 28
Mar = 7
I need to use the result to add a % uplift to a base forecast.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

autoexcrement SSCertifiable Points: 5951 More actions · Answer 1

This might be too low budget a solution for you, but what if you simply created another table that had every possible date in it? Then it would simply be a matter of selecting all the dates from that table that are between your start/end dates and grouping them by month.

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

spin Hall of Fame Points: 3274 More actions · Answer 2

spin

Hall of Fame

Points: 3274

November 25, 2013 at 11:23 am

#1668920

perfect thanks

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Or simply use a Tally table:

DECLARE @StartDatedate = '20130115',

@EndDatedate = '20130307';

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 (--==== This provides the "base" CTE and limits the number of rows right up front

-- for both a performance gain and prevention of accidental "overruns"

SELECT TOP (ISNULL(DATEDIFF( DAY, @StartDate, @EndDate),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

)

SELECT DATENAME( MONTH, DATEADD( DAY, N, @StartDate)), COUNT(*)

FROM cteTally

GROUP BY DATENAME( MONTH, DATEADD( DAY, N, @StartDate))

To understand what is a Tally table and how it replaces a loop, check the following article:http://www.sqlservercentral.com/articles/T-SQL/62867/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

autoexcrement SSCertifiable Points: 5951 More actions · Answer 4

I knew it'd just be a matter of time before someone posted some Cartesian tally table trickery. 🙂

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

Dwain Camps SSC Guru Points: 86908 More actions · Answer 5

autoexcrement (11/25/2013)
I knew it'd just be a matter of time before someone posted some Cartesian tally table trickery. 🙂

"Cartesian tally table trickery" makes it sounds like a bad thing. It is not.

Fortunately your 🙂 suggests that you agree with me.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

autoexcrement SSCertifiable Points: 5951 More actions · Answer 6

Perhaps "wizardry" would have been more appropriate, but I had a little alliteration thing going there with the "t's", so... 🙂 I'm certainly in awe of you cats and your tricke...wizardry. 🙂

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

Dwain Camps SSC Guru Points: 86908 More actions · Answer 7

autoexcrement (11/25/2013)
Perhaps "wizardry" would have been more appropriate, but I had a little alliteration thing going there with the "t's", so... 🙂 I'm certainly in awe of you cats and your tricke...wizardry. 🙂

Ah yes... wizardry!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

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

Luis Cazares (11/25/2013)
Or simply use a Tally table:
DECLARE @StartDatedate = '20130115',
@EndDatedate = '20130307';
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 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 (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATEDIFF( DAY, @StartDate, @EndDate),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
SELECT DATENAME( MONTH, DATEADD( DAY, N, @StartDate)), COUNT(*)
FROM cteTally
GROUP BY DATENAME( MONTH, DATEADD( DAY, N, @StartDate))
To understand what is a Tally table and how it replaces a loop, check the following article:http://www.sqlservercentral.com/articles/T-SQL/62867/

Luis, cleaned yours up for SQL Server 2008 and newer:

DECLARE @StartDate date = '20130115',

@EndDate date = '20130307';

WITH E1(N) AS (

SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(N)

), --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 (--==== This provides the "base" CTE and limits the number of rows right up front

-- for both a performance gain and prevention of accidental "overruns"

SELECT TOP (ISNULL(DATEDIFF( DAY, @StartDate, @EndDate),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

)

SELECT DATENAME( MONTH, DATEADD( DAY, N, @StartDate)), COUNT(*)

FROM cteTally

GROUP BY DATENAME( MONTH, DATEADD( DAY, N, @StartDate));

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

Luis, I noticed your output wasn't ordered by Month (Calendar order), so made one more change to the 2008 and newer version:

DECLARE @StartDate date = '20130115',

@EndDate date = '20130307';

WITH E1(N) AS (

SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(N)

), --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 (--==== This provides the "base" CTE and limits the number of rows right up front

-- for both a performance gain and prevention of accidental "overruns"

SELECT TOP (ISNULL(DATEDIFF( DAY, @StartDate, @EndDate),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

)

SELECT DATENAME( MONTH, DATEADD( DAY, N, @StartDate)), COUNT(*)

FROM cteTally

GROUP BY DATENAME( MONTH, DATEADD( DAY, N, @StartDate)),DATEPART( MONTH, DATEADD( DAY, N, @StartDate))

ORDER BY DATEPART( MONTH, DATEADD( DAY, N, @StartDate));

Edit: Sorry for the delay to re-post.