March 23, 2018 at 10:09 am
Here is what I need. I have s start date and a end date.
What I need is to break this down into several intervals ( monthly )
So I was able to just get the very first interval.
May you please help me get the rest of the intervals please.. I bet there is a stylish way to do this... instead of looping
Select @VStartDate = '20170110';
Select @VEndDate = '20171231';
IF OBJECT_ID('tempdb..#MonthlyIntervals') IS NOT NULL
DROP TABLE #MonthlyIntervals;
CREATE TABLE #MonthlyIntervals( StartDt VARCHAR(8) , EndDt VARCHAR(8) );
--INSERT INTO #MonthlyIntervals( StartDt , EndDt)
With A as
(
Select @VStartDate as StartDt, CONVERT(CHAR(8), DATEADD(MONTH, DATEDIFF(MONTH, -1, ((DateAdd(MM,1,@VStartDate )) ))-1, -1), 112 ) as EndDt
)
Select * FROM A;
March 23, 2018 at 10:13 am
You got a calendar or numbers table in your database? If not, can you create one?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2018 at 10:59 am
GilaMonster - Friday, March 23, 2018 10:13 AMYou got a calendar or numbers table in your database? If not, can you create one?
Not sure what that is.. Anyhow the DateRange can be varying. In this example the range starts on JAN 10 and could span for several years. I bet someone who knows recursion can modify that code and do the trick
March 23, 2018 at 11:01 am
Here's an explanation of what a Tally or Numbers table is...
http://www.sqlservercentral.com/articles/T-SQL/62867/
March 23, 2018 at 11:38 am
mw_sql_developer - Friday, March 23, 2018 10:59 AM. I bet someone who knows recursion can modify that code and do the trick
Probably, but if they do, you should not use it. Recursion is a poor approach SQL Server, there are far better ways to do this.
Take the numbers table, and use the sequential numbers in it as a number of months to add (via DATEADD) to both the start and end date as you computed them in your initial post, with a WHERE clause limiting the calculated end dates to the @VEndDate, and you should be set.
If you can't get the code right, post what you have and where you're struggling.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2018 at 1:51 pm
I used recursion.. Works Well ! Thanks for the help. See I knew it was easy
IF OBJECT_ID('tempdb..#MonthlyIntervals') IS NOT NULL
DROP TABLE #MonthlyIntervals;
CREATE TABLE #MonthlyIntervals( StartDt VARCHAR(8) , EndDt VARCHAR(8) );
Declare @VStartDate VARCHAR(10);
Declare @VEndDate VARCHAR(10);
Select @VStartDate = '20170110';
Select @VEndDate = '20181215';
Declare @i INT
Select @i=DATEDIFF(M, @VStartDate, @VEndDate ) + 1 -- How many distinct months does range cover
;
WITH NextNum (PrevN, N) AS
(
SELECT 1, 2
UNION ALL
SELECT N, N+ 1
FROM NextNum
WHERE N <= @i
)
,
IDX as
(
SELECT PrevN as IDX -- Gives me a sequence on integers starting from 1
FROM NextNum
)
INSERT INTO #MonthlyIntervals( StartDt , EndDt)
Select
CASE WHEN @VStartDate >= (CONVERT(CHAR(10),(DATEFROMPARTS(YEAR((DATEADD(M,IDX-1,@VStartDate))),MONTH((DATEADD(M,IDX-1,@VStartDate))),1)), 112 ) ) THEN
@VStartDate
ELSE
CONVERT(CHAR(10),(DATEFROMPARTS(YEAR((DATEADD(M,IDX-1,@VStartDate))),MONTH((DATEADD(M,IDX-1,@VStartDate))),1)), 112 )
END as StartDt
,
CASE WHEN @VEndDate <= (CONVERT(CHAR(8),DATEADD(MONTH,DATEDIFF(MONTH,-1,((DateAdd(MM,IDX,@VStartDate ))))-1, -1), 112)) THEN
@VEndDate
ELSE
CONVERT(CHAR(8),DATEADD(MONTH,DATEDIFF(MONTH,-1,((DateAdd(MM,IDX,@VStartDate ))))-1, -1), 112)
END as EndDt
FROM
IDX
Select * FROM #MonthlyIntervals;
March 23, 2018 at 2:27 pm
mw_sql_developer - Friday, March 23, 2018 10:09 AM
Here is what I need. I have s start date and a end date.
What I need is to break this down into several intervals ( monthly )
So I was able to just get the very first interval.May you please help me get the rest of the intervals please.. I bet there is a stylish way to do this... instead of looping
Select @VStartDate = '20170110';
Select @VEndDate = '20171231';IF OBJECT_ID('tempdb..#MonthlyIntervals') IS NOT NULL
DROP TABLE #MonthlyIntervals;CREATE TABLE #MonthlyIntervals( StartDt VARCHAR(8) , EndDt VARCHAR(8) );
--INSERT INTO #MonthlyIntervals( StartDt , EndDt)
With A as
(
Select @VStartDate as StartDt, CONVERT(CHAR(8), DATEADD(MONTH, DATEDIFF(MONTH, -1, ((DateAdd(MM,1,@VStartDate )) ))-1, -1), 112 ) as EndDt)
Select * FROM A;
Given your example dates, what are you expecting for the output? I ask because it could me any of a different number of outputs based on the dates and your definition of a "monthly" interval.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2018 at 2:30 pm
I strongly recommend you steer away from that inefficient method (and it is going to be really inefficient on larger date ranges), and use a numbers table to generate the intervals.
p.s. the semicolon is a row terminator. It belongs at the end of Select @i=DATEDIFF(M, @VStartDate, @VEndDate ) + 1;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2018 at 3:03 pm
Jeff Moden - Friday, March 23, 2018 2:27 PMmw_sql_developer - Friday, March 23, 2018 10:09 AM
Here is what I need. I have s start date and a end date.
What I need is to break this down into several intervals ( monthly )
So I was able to just get the very first interval.May you please help me get the rest of the intervals please.. I bet there is a stylish way to do this... instead of looping
Select @VStartDate = '20170110';
Select @VEndDate = '20171231';IF OBJECT_ID('tempdb..#MonthlyIntervals') IS NOT NULL
DROP TABLE #MonthlyIntervals;CREATE TABLE #MonthlyIntervals( StartDt VARCHAR(8) , EndDt VARCHAR(8) );
--INSERT INTO #MonthlyIntervals( StartDt , EndDt)
With A as
(
Select @VStartDate as StartDt, CONVERT(CHAR(8), DATEADD(MONTH, DATEDIFF(MONTH, -1, ((DateAdd(MM,1,@VStartDate )) ))-1, -1), 112 ) as EndDt)
Select * FROM A;Given your example dates, what are you expecting for the output? I ask because it could me any of a different number of outputs based on the dates and your definition of a "monthly" interval.
Run the code, you will see the output. What I need is for each month in the span the month start date and end date. works beautifully!
March 23, 2018 at 7:43 pm
mw_sql_developer - Friday, March 23, 2018 3:03 PMJeff Moden - Friday, March 23, 2018 2:27 PMmw_sql_developer - Friday, March 23, 2018 10:09 AM
Here is what I need. I have s start date and a end date.
What I need is to break this down into several intervals ( monthly )
So I was able to just get the very first interval.May you please help me get the rest of the intervals please.. I bet there is a stylish way to do this... instead of looping
Select @VStartDate = '20170110';
Select @VEndDate = '20171231';IF OBJECT_ID('tempdb..#MonthlyIntervals') IS NOT NULL
DROP TABLE #MonthlyIntervals;CREATE TABLE #MonthlyIntervals( StartDt VARCHAR(8) , EndDt VARCHAR(8) );
--INSERT INTO #MonthlyIntervals( StartDt , EndDt)
With A as
(
Select @VStartDate as StartDt, CONVERT(CHAR(8), DATEADD(MONTH, DATEDIFF(MONTH, -1, ((DateAdd(MM,1,@VStartDate )) ))-1, -1), 112 ) as EndDt)
Select * FROM A;Given your example dates, what are you expecting for the output? I ask because it could me any of a different number of outputs based on the dates and your definition of a "monthly" interval.
Run the code, you will see the output. What I need is for each month in the span the month start date and end date. works beautifully!
Here is a method that doesn't rely on string manipulation (slow) - and does not use a predefined Tally table (although - if you have one it would be better):
Declare @startDate date = '2017-01-10'
, @endDate date = '2018-12-15';
With monthlyIntervals (StartDate, EndDate)
As (
Select dateadd(day, 1, eomonth(@startDate, t.Number - 1))
, eomonth(@startDate, t.Number)
From (Select row_number() over(Order By ac.[object_id]) - 1 As Number From sys.all_columns ac) As t
Where t.Number <= datediff(month, @startDate, @endDate)
)
Select *
From monthlyIntervals;
This also uses the date data type - you really should not use strings to represent dates - that will cause all kinds of issues later on...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 24, 2018 at 2:38 pm
mw_sql_developer - Friday, March 23, 2018 3:03 PMJeff Moden - Friday, March 23, 2018 2:27 PMmw_sql_developer - Friday, March 23, 2018 10:09 AM
Here is what I need. I have s start date and a end date.
What I need is to break this down into several intervals ( monthly )
So I was able to just get the very first interval.May you please help me get the rest of the intervals please.. I bet there is a stylish way to do this... instead of looping
Select @VStartDate = '20170110';
Select @VEndDate = '20171231';IF OBJECT_ID('tempdb..#MonthlyIntervals') IS NOT NULL
DROP TABLE #MonthlyIntervals;CREATE TABLE #MonthlyIntervals( StartDt VARCHAR(8) , EndDt VARCHAR(8) );
--INSERT INTO #MonthlyIntervals( StartDt , EndDt)
With A as
(
Select @VStartDate as StartDt, CONVERT(CHAR(8), DATEADD(MONTH, DATEDIFF(MONTH, -1, ((DateAdd(MM,1,@VStartDate )) ))-1, -1), 112 ) as EndDt)
Select * FROM A;Given your example dates, what are you expecting for the output? I ask because it could me any of a different number of outputs based on the dates and your definition of a "monthly" interval.
Run the code, you will see the output. What I need is for each month in the span the month start date and end date. works beautifully!
Heh... why would you ever think that I didn't run your code? 😉 I'm the guy that wrote the article that says to do so.
You say you want the month start date but then you return the start of the date range defined by the variable instead of the start of the mode. So, if the date in the @VEndDate variable contains '2018-12-15' do you want to return '2018-12-15' or '2018-12-31'?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply