Generate calendar

How can I generate the dates for the entire range? Thanks

Answers


As Mitch Wheat suggests is his comment, probably the best way to improve the performance of this query is to use a numbers table in place of the recursive CTE used to generate the list of dates.

If you can't or won't use a numbers table, the performance of the date range CTE can be improved for large ranges using a method suggested by Itzik Ben-Gan:

DECLARE @t TABLE(startdate DATETIME , enddate DATETIME)
INSERT INTO @t 
    SELECT '8/01/2009','08/31/2009' UNION ALL
    SELECT '2/01/1900','02/28/1900' UNION ALL
    SELECT '10/01/1959','10/31/1959'

DECLARE @n INT
SET @n = DATEDIFF(dd,'19000201','20090831') + 1

;WITH base 
AS 
( 
        SELECT 1 AS n 
        UNION ALL 
        SELECT n+1 FROM base 
        WHERE n < CEILING(SQRT(@n)) 
) 
,cross_cte 
AS 
( 
        SELECT 0 AS c 
        FROM base AS b1 
        ,base AS b2 
) 
,dates_cte
AS
(
        SELECT TOP(@n) CAST('19000201' AS DATETIME) - 1 + ROW_NUMBER()  OVER(ORDER BY c) AS date
        FROM cross_cte 
)
SELECT DISTINCT d.DATE                  
FROM Dates_Cte d 
JOIN @t t 
ON d.DATE BETWEEN t.startdate AND t.enddate
OPTION ( MAXRECURSION 0);

Whilst the execution plan shows this version to be slightly less efficient than the original (=~ 1%), measuring with SET STATISTICS TIME on my system shows both the elapsed and CPU time for this version to be less than half that of yours.


Need Your Help

Three way relation in Laravel

php mysql laravel eloquent relation

Can someone tell me if I can do something like "three way relation", but one of three table would work as reference to another ?

Load URL by .png from a webview

android url webview

After I've filtered out the extension inside my webview, ".png". How can I then use the entire link to open a new activity?