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

 calculate months days for start and end date (each month) Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, November 25, 2013 11:06 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, April 14, 2014 8:25 AM Points: 116, Visits: 402
 Hii 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/13the result would be :Jan = 16Feb = 28Mar = 7I 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 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 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 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 cteTallyGROUP 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 LeacockForum 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 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 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 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 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 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 cteTallyGROUP 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 cteTallyGROUP BY DATENAME( MONTH, DATEADD( DAY, N, @StartDate));`
Post #1517538
 Posted Monday, November 25, 2013 11:36 PM
 SSC-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 cteTallyGROUP 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.
Post #1517540

 Permissions