October 16, 2012 at 4:57 am
How do I write a code that given a date range, prints the Year and Month for the months in that date range?
If I declare variables @StartDate and @Enddate and if the @EndDate is before the @StartDate, it would print an approprite error message.
For example, if the @StartDate is October 29, 2012 and the @EndDate is February 15, 2013, the block prints:
October 2012
November 2012
December 2012
January 2013
Is there any function that can do that and how? Can anybody show me?
October 16, 2012 at 5:20 am
There is no in-build "function" to do so, but you can write your own TVF function based on the following:
DECLARE @StartDate DATE = '29 October 2012'
,@EndDate DATE = '15 February 2013'
SELECT DATENAME(MONTH,MMM) + ' ' + DATENAME(YEAR,MMM) AS MonthYear
FROM (SELECT DATEADD(MONTH,M,@StartDate) AS MMM
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS M
FROM sys.columns) run_time_tally
WHERE M < DATEDIFF(MONTH,@StartDate, @EndDate)
) C
ORDER BY MMM
Please note:
1. You better to create permanent Tally table (you can search on this forum what it is about)
2. Do you really want to check if EndDate is greater than StartDate and raise an error or just return no records?
October 16, 2012 at 10:15 am
Haven't checked on Tally tables yet but I appreciate the help. Working with functions without reference from a table is new to me. lol So not my world.
October 17, 2012 at 1:20 am
If you are familiar with recursive CTE, you can use following SQL. You won't have to refer to any tables. Moreover this is much more efficient. Try it.
DECLARE @StartDate DATE = '29 October 2012'
,@EndDate DATE = '15 February 2013'
;WITH CTE_DATES AS
(
SELECT
D = @STARTDATE
UNION ALL
SELECT
D = DATEADD(MONTH, 1, D)
FROM CTE_DATES
WHERE D < DATEADD(MONTH, -1, @ENDDATE)
)
SELECT
DATENAME(MONTH, D) + ' ' + DATENAME(YEAR, D) AS MONTHYEAR
FROM CTE_DATES
October 17, 2012 at 3:41 am
shalinder.verma (10/17/2012)
If you are familiar with recursive CTE, you can use following SQL. You won't have to refer to any tables. Moreover this is much more efficient. Try it....
Any proves for that or just a guess?
Try it 😉
October 17, 2012 at 4:01 am
Eugene Elutin (10/17/2012)
shalinder.verma (10/17/2012)
If you are familiar with recursive CTE, you can use following SQL. You won't have to refer to any tables. Moreover this is much more efficient. Try it....
Any proves for that or just a guess?
Try it 😉
Compared both the SQLs Actual Execution plan
First one:
Cost = 0.0824766
Number of Rows = 921.838
With Recursive CTE:
Cost = 0.0000065
Number of Rows = 2
You can check on your computer as well and report the stats. Perhaps it's my laptop :Whistling:
October 17, 2012 at 5:27 am
shalinder.verma (10/17/2012)
Eugene Elutin (10/17/2012)
shalinder.verma (10/17/2012)
If you are familiar with recursive CTE, you can use following SQL. You won't have to refer to any tables. Moreover this is much more efficient. Try it....
Any proves for that or just a guess?
Try it 😉
Compared both the SQLs Actual Execution plan
First one:
Cost = 0.0824766
Number of Rows = 921.838
With Recursive CTE:
Cost = 0.0000065
Number of Rows = 2
You can check on your computer as well and report the stats. Perhaps it's my laptop :Whistling:
The execution plan doesn't prove anything. Put both to the test using using a million row table.
October 17, 2012 at 6:11 am
shalinder.verma (10/17/2012)
Eugene Elutin (10/17/2012)
shalinder.verma (10/17/2012)
If you are familiar with recursive CTE, you can use following SQL. You won't have to refer to any tables. Moreover this is much more efficient. Try it....
Any proves for that or just a guess?
Try it 😉
Compared both the SQLs Actual Execution plan
First one:
Cost = 0.0824766
Number of Rows = 921.838
With Recursive CTE:
Cost = 0.0000065
Number of Rows = 2
You can check on your computer as well and report the stats. Perhaps it's my laptop :Whistling:
Could you please advise what your numbers have to do with effectiveness and performance?
It has nothing to do with your laptop either...
Now let's test it properly.
1st. we need to create proper tally table (let say very small with just 1,000,000 rows) and table valued UDF's
-- 1. Create dedicated Tally table and index it appropriately
SELECT TOP 1000001 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS RN
INTO dbo.MyTally
FROM sys.columns s1, sys.columns s2, sys.columns s3
GO
CREATE UNIQUE CLUSTERED INDEX ITC_MyTally ON dbo.MyTally (RN)
GO
-- 2. Create function based on tally
CREATE FUNCTION dbo.f_GetMonthRange( @StartDate DATE
,@EndDate DATE)
RETURNS TABLE
AS
RETURN
(
SELECT DATENAME(MONTH,MMM) + ' ' + DATENAME(YEAR,MMM) AS MonthYear
FROM (SELECT DATEADD(MONTH,RN,@StartDate) AS MMM
FROM dbo.MyTally
WHERE RN < DATEDIFF(MONTH,@StartDate, @EndDate)
) C
)
GO
-- 3. Create function based on CTE
CREATE FUNCTION dbo.f_GetMonthRangeCTE( @StartDate DATE
,@EndDate DATE)
RETURNS TABLE
AS
RETURN
(
WITH CTE_DATES AS
(
SELECT
D = @STARTDATE
UNION ALL
SELECT
D = DATEADD(MONTH, 1, D)
FROM CTE_DATES
WHERE D < DATEADD(MONTH, -1, @ENDDATE)
)
SELECT
DATENAME(MONTH, D) + ' ' + DATENAME(YEAR, D) AS MONTHYEAR
FROM CTE_DATES
)
GO
Now lets test.
First of all, we need to mention the limitation of CTE based solution - it can only run for maximum period of 8 years and 4 months). Executing the following:
select * from dbo.f_GetMonthRangeCTE('20030101','20120101')
will produce error:
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Tally table solution can cover the full range of SQL datetime, returning all 119,987 months:
select * from dbo.f_GetMonthRange('00010101','99991231')
But anyway lets try maximum CTE supported range. We will need to clear cache before run to get non-zero timing figures:
SET NOCOUNT ON;
DECLARE @startdate datetime
DBCC FREEPROCCACHE
PRINT '======================================'
PRINT 'Test f_GetMonthRange on Tally'
SET @startdate = GETUTCDATE()
SELECT * FROM dbo.f_GetMonthRange('20040101','20120101') ORDER BY MonthYear
PRINT CAST(DATEDIFF(ms,@startdate,GETUTCDATE()) AS VARCHAR) + ' ms'
PRINT '======================================'
PRINT '======================================'
PRINT 'Test f_GetMonthRangeCTE on CTE'
SET @startdate = GETUTCDATE()
SELECT * FROM dbo.f_GetMonthRangeCTE('20040101','20120101') ORDER BY MonthYear
PRINT CAST(DATEDIFF(ms,@startdate,GETUTCDATE()) AS VARCHAR) + ' ms'
PRINT '======================================'
After few runs, the most common result I have is:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
======================================
Test f_GetMonthRange on Tally
0 ms
======================================
======================================
Test f_GetMonthRangeCTE on CTE
13 ms
======================================
The difference is negligible (still Tally wins over recursive CTE), but it is expected as we only have 96 rows to process. Also we may easily get for both methods 0ms - as it is too small data set!
So can we check "effectiveness" by other method? Yes, we can collect IO stats:
SET STATISTICS IO ON;
SELECT * FROM dbo.f_GetMonthRange('20040101','20120101') ORDER BY MonthYear
SELECT * FROM dbo.f_GetMonthRangeCTE('20040101','20120101') ORDER BY MonthYear
Here what we got:
Table 'MyTally'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 577, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Now, if you will try to use same functions in CROSS APPLY, then you will see how this difference will quite quickly become substantial (again not even talking about CTE limitation)
It will be very hard to find cases where recursive CTE will be more effective than other non-recursive CTE solution if such possible.
October 17, 2012 at 8:43 am
shalinder.verma (10/17/2012)
Eugene Elutin (10/17/2012)
shalinder.verma (10/17/2012)
If you are familiar with recursive CTE, you can use following SQL. You won't have to refer to any tables. Moreover this is much more efficient. Try it....
Any proves for that or just a guess?
Try it 😉
Compared both the SQLs Actual Execution plan
First one:
Cost = 0.0824766
Number of Rows = 921.838
With Recursive CTE:
Cost = 0.0000065
Number of Rows = 2
You can check on your computer as well and report the stats. Perhaps it's my laptop :Whistling:
I agree with Lynn, here. The % of Batch and other supposed performance indications like "Cost" mean squat in many, many cases. Even on the actual execution plan, they're just an estimate. In fact, I've seen the execution plan % of batch be 100% incorrect. If I think about it tonight, I'll try to find the example I gave Grant Fritchey for his book.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2012 at 8:55 am
Eugene,
Thanks for all the hard work.
First of all, I was comparing the "on the fly tally table" SQL with "Recursive CTE" SQL. Therefore, my phrase "more efficient" should be looked with in that context only.
Secondly, the work around CTE maximum 100 recursion is using "Option" hint "MAXRECURSION" to support upto 32,767 recursions. Infact with Maxrecursion = 0, one can remove all the limits.
Last but not least, I fail to understand why "Cost" of the query derived from Actual Execution Plan is not of any value. Why would Microsoft put it over there. Please explain.
Thanks once again.
October 17, 2012 at 9:59 am
shalinder.verma (10/17/2012)
Eugene,Thanks for all the hard work.
First of all, I was comparing the "on the fly tally table" SQL with "Recursive CTE" SQL. Therefore, my phrase "more efficient" should be looked with in that context only.
Secondly, the work around CTE maximum 100 recursion is using "Option" hint "MAXRECURSION" to support upto 32,767 recursions. Infact with Maxrecursion = 0, one can remove all the limits.
Last but not least, I fail to understand why "Cost" of the query derived from Actual Execution Plan is not of any value. Why would Microsoft put it over there. Please explain.
Thanks once again.
First of all I've said that it's better to use a proper Tally table.
Second: even in-line Tally will beat recursive CTE, and again, removing limit of recursion will even make it worse.
And the last: Cost is in % of the full query. You cannot really compare this number between two different queries.
October 17, 2012 at 10:40 am
shalinder.verma (10/17/2012)
Eugene,Thanks for all the hard work.
First of all, I was comparing the "on the fly tally table" SQL with "Recursive CTE" SQL. Therefore, my phrase "more efficient" should be looked with in that context only.
Secondly, the work around CTE maximum 100 recursion is using "Option" hint "MAXRECURSION" to support upto 32,767 recursions. Infact with Maxrecursion = 0, one can remove all the limits.
Last but not least, I fail to understand why "Cost" of the query derived from Actual Execution Plan is not of any value. Why would Microsoft put it over there. Please explain.
Thanks once again.
The "Cost" is an estimate that the compiler understands and isn't actually fit for human understanding in most cases. It's actually a shame that MS included it in the execution plans because it's terribly misleading for people that don't actually understand that.
So far as the "Tally Table on-the-fly" compared to a "Recursive CTE" goes, one of the things you may not understand is that the execution plan only shows the first iteration of the rCTE and the "costs" are based on that single iteration. Please see the following article for some real performance and resource usage comparisons of 3 different methods of producing sequenctial number compared to rCTEs that count. You'll be amazed at how ineffecient the rCTE version actually is.
http://www.sqlservercentral.com/articles/T-SQL/74118/
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2012 at 12:30 pm
lex9120,
I'm not sure what you are looking for regarding the circumstance where the end date is earlier than the start date. But here's some code I've used in cases where the front-end application allows that kind of error.
Declare @start_date datetime
Declare @end_date datetime
Declare @switcher datetime -- this is just a vehicle to handle the input error
If @end_date < @start_date -- user entered dates in wrong order
Begin
Set @switcher=@start_date
Set @start_date=@end_date
Set @end_date=@switcher
End
Elliott
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply