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

calculate months days for start and end date (each month) Expand / Collapse
Author
Message
Posted Monday, November 25, 2013 11:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:25 AM
Points: 116, Visits: 402
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
Post #1517382
Posted Monday, November 25, 2013 11:18 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:26 AM
Points: 77, Visits: 299
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
Post #1517389
Posted Monday, November 25, 2013 11:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:25 AM
Points: 116, Visits: 402
perfect thanks
Post #1517395
Posted Monday, November 25, 2013 11:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:00 PM
Points: 2,763, Visits: 5,905
Or simply use a Tally table:
DECLARE @StartDate	date = '20130115',
@EndDate date = '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 C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1517404
Posted Monday, November 25, 2013 4:25 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:26 AM
Points: 77, Visits: 299
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
Post #1517493
Posted Monday, November 25, 2013 5:56 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1517504
Posted Monday, November 25, 2013 6:00 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:26 AM
Points: 77, Visits: 299
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
Post #1517505
Posted Monday, November 25, 2013 6:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1517508
Posted Monday, November 25, 2013 11:22 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 8:41 PM
Points: 22,491, Visits: 30,185
Luis Cazares (11/25/2013)
Or simply use a Tally table:
DECLARE @StartDate	date = '20130115',
@EndDate date = '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

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 #1517538
Posted Monday, November 25, 2013 11:36 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 8:41 PM
Points: 22,491, Visits: 30,185
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.



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 #1517540
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse