# Calendar Table Function

• ScottPletcher (3/31/2016)

Alan.B (3/30/2016)

Alan.B (3/30/2016)

ScottPletcher (3/30/2016)

Alan.B (3/30/2016)

ScottPletcher (3/30/2016)

Alan.B (3/29/2016)

Lastly, ditto the last Thursday of the month:

`SET STATISTICS IO ON;`

`PRINT 'Old way:';`

`SELECT DISTINCT MAX([Date]) OVER (PARTITION BY [Year],[MonthName],[WeekdayName]) AS [Date]`

`FROM dbo.itvf_CALENDAR('01/01/2014','12/31/2014')`

`WHERE [WeekdayName] = 'Thursday'`

`ORDER BY [Date]`

`PRINT CHAR(10)+'better way:';`

`SELECT MAX([Date])`

`FROM dbo.itvf_CALENDAR('01/01/2014','12/31/2014')`

`WHERE [Weekday] = 5`

`GROUP BY [Month]`

`SET STATISTICS IO OFF;`

One certainly doesn't need any calendar table at all just to get the last given day of a month(s). Just a standard tally table, to enumerate the months, and a simple mathematical calc to get the day:

His function is using a tally table so you and I are basically doing the same thing more or less. I was demonstrating a better way to get the last Thursday of the month than the example he included in his article (which uses the Calendar iTVF). His method uses DISTINCT MAX([Date]) OVER (PARTITION BY... whereas mine gets the job done with a simple GROUP BY and therefore no worktable, a fraction of the reads, etc...

On a separate note I used to use a tally table for these kinds of date calculations but have found that a nicely indexed calendar table (or dim_date table) outperforms a tally table.

Huh? How can I/O outperform NO I/O?? It's just arithmetic. The tally is used only for the number of months, NOT in the date/day of week calc at all.

Edit: I am using a physical tally table only because my filter at work does not allow "UNION ALL" in code (it assumes it's "sql injection" and rejects it(!)). A tally cte, esp. for a tiny number such as the number of months, would also be zero I/O.

Busy work day... I'll put together a performance test a little later tonight and you can tell if you think I'm missing something.

Argh! I had a longer reply and accidentally refreshed (CTRL+R is cool in SSMS, terrible in google Chrome). Anyhow, here's a couple tests I did with a bunch of dates. Not realistic to go back to the 1800's but I wanted enough rows to work with. The reads BTW is a red herring too because the calendar table generates virtually 0 reads. I have some better tests somewhere but could not find them so I through this together real quick. Note the superior calendar table execution plan (filter - free, all the action from non-clustered index seeks)

`USE tempdb`

`GO`

`/****************************************************************************************`

`(1) Create simplified calendar table`

`****************************************************************************************/`

`IF OBJECT_ID('dbo.calendar') IS NOT NULL DROP TABLE dbo.calendar;`

`CREATE TABLE dbo.calendar`

`(`

` DateTxt date primary key,`

` YearNbr smallint NOT NULL,`

` MonthNbr tinyint NOT NULL,`

` DayOfMonthNbr tinyint NOT NULL,`

` DayOfWeekNbr tinyint NOT NULL`

`);`

`-- On a real calendar table you want this index present with the required INCLUDE columns`

`CREATE UNIQUE NONCLUSTERED INDEX uq_xxx`

` ON dbo.calendar(YearNbr, MonthNbr, DateTxt);`

`CREATE NONCLUSTERED INDEX nc_xxx ON dbo.calendar (DayOfWeekNbr) INCLUDE (DateTxt,YearNbr,MonthNbr);`

`CREATE NONCLUSTERED INDEX nc_xxx2 ON dbo.calendar (DayOfMonthNbr) INCLUDE (DateTxt);`

`-- Populate the calendar table`

`WITH dates(DateTxt) AS`

`(`

` SELECT TOP (DATEDIFF(DAY,'18000101','22000101'))`

` CAST(DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1,'18000102') AS date)`

` FROM sys.all_columns a, sys.all_columns b`

`)`

`INSERT dbo.calendar`

`SELECT DateTxt, DATEPART(YEAR,DateTxt), DATEPART(MONTH,DateTxt), DATEPART(DAY,DateTxt), DATEPART(WEEKDAY,DateTxt)`

`FROM dates;`

`GO`

`/****************************************************************************************`

`(2) Create Tally Table function (using Jeff Moden's "fnTally")`

`****************************************************************************************/`

`CREATE FUNCTION dbo.fnTally`

`/**********************************************************************************************************************`

` Purpose:`

` Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Trillion.`

` As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.`

` Usage:`

`--===== Syntax example (Returns BIGINT)`

` SELECT t.N`

` FROM dbo.fnTally(@ZeroOrOne,@MaxN) t`

`;`

` Notes:`

` 1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.`

` Refer to the following URLs for how it works and introduction for how it replaces certain loops.`

` http://www.sqlservercentral.com/articles/T-SQL/62867/`

` http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers`

` 2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type`

` will cause the sequence to start at 1.`

` 3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.`

` 5. If @MaxN is negative or NULL, a "TOP" error will be returned.`

` 6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger`

` number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with`

` that many values, you should consider using a different tool. Wink`

` 7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending`

` sort is required, use code similar to the following. Performance will decrease by about 27% but it's still`

` very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.`

` If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.`

` DECLARE @MaxN BIGINT;`

` SELECT @MaxN = 1000;`

` SELECT DescendingN = @MaxN-N+1`

` FROM dbo.fnTally(1,@MaxN);`

` 8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by`

` ROW_NUMBER() OVER (ORDER BY (SELECT NULL))`

` Revision History:`

` Rev 00 - Unknown - Jeff Moden`

` - Initial creation with error handling for @MaxN.`

` Rev 01 - 09 Feb 2013 - Jeff Moden`

` - Modified to start at 0 or 1.`

` Rev 02 - 16 May 2013 - Jeff Moden`

` - Removed error handling for @MaxN because of exceptional cases.`

` Rev 03 - 22 Apr 2015 - Jeff Moden`

` - Modify to handle 1 Trillion rows for experimental purposes.`

`**********************************************************************************************************************/`

` (@ZeroOrOne BIT, @MaxN BIGINT)`

`RETURNS TABLE WITH SCHEMABINDING AS`

` RETURN 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) --10E1 or 10 rows`

`, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10E4 or 10 Thousand rows`

`,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c) --10E12 or 1 Trillion rows`

` SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.`

` UNION ALL`

` SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN`

`;`

`GO`

`--SELECT * FROM dbo.calendar`

`/****************************************************************************************`

`(3) Perf test1, just generate a bunch of dates`

`****************************************************************************************/`

`SET NOCOUNT ON;`

`PRINT 'Using calendar table:';`

`GO`

` DECLARE @x date, @st datetime = getdate();`

` SELECT @x = DateTxt FROM dbo.calendar WHERE DateTxt BETWEEN '18000101' AND '22000101' ;`

` PRINT DATEDIFF(MS,@st,getdate());`

`GO 6`

`PRINT 'Using tally function:';`

`GO`

` DECLARE @x date, @st datetime = getdate();`

` SELECT @x = CAST(DATEADD(DAY,N,'18000101') AS date)`

` FROM dbo.fnTally(0,DATEDIFF(DAY,'18000101','22000101')-1)`

` PRINT DATEDIFF(MS,@st,getdate());`

`GO 6`

`/*`

`Using calendar table:`

`Beginning execution loop`

`60`

`36`

`33`

`36`

`33`

`33`

`Batch execution completed 6 times.`

`Using tally function:`

`Beginning execution loop`

`53`

`53`

`50`

`50`

`48`

`50`

`Batch execution completed 6 times.`

`*/`

`/****************************************************************************************`

`(4) Perf test2, last day and last thursday`

`****************************************************************************************/`

`SET STATISTICS IO ON;`

`GO`

`DECLARE @start_date date = '18000101', @end_date date = '22000101', @st datetime = getdate(), @x1 date, @x2 date;`

`SELECT @x1 = DATEADD(DAY,-1,DateTxt), @x2 = DATEADD(DAY, -DATEDIFF(DAY, 3, DATEADD(DAY,-1,DateTxt)) % 7,DATEADD(DAY,-1,DateTxt))`

`FROM dbo.calendar`

`WHERE DayOfMonthNbr = 1`

`AND DateTxt BETWEEN DATEADD(MONTH,1,@start_date) AND DATEADD(MONTH,1,@end_date);`

`PRINT DATEDIFF(MS,@st,getdate());`

`SET STATISTICS IO OFF;`

`GO 6`

`DECLARE @start_date date = '18000101', @end_date date = '22000101', @st datetime = getdate(), @x1 date, @x2 date;`

`SELECT @x1 = last_day_of_month, @x2 = DATEADD(DAY, -DATEDIFF(DAY, Thursday, last_day_of_month) % 7, last_day_of_month)`

`FROM fnTally(0,DATEDIFF(MONTH, @start_date, @end_date)) t`

`CROSS APPLY`

`(`

` SELECT DATEADD(DAY, -1, DATEADD(MONTH, t.N + 1, @start_date)) AS last_day_of_month,`

` 3 AS Thursday --0 for Mon, 1 for Tue, etc..`

`) AS assign_alias_names`

`WHERE t.N BETWEEN 0 AND DATEDIFF(MONTH, @start_date, @end_date)`

`PRINT DATEDIFF(MS,@st,getdate());`

`GO 6`

`/*`

`Beginning execution loop`

`Table 'calendar'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.`

`120`

`16`

`10`

`10`

`13`

`10`

`Batch execution completed 6 times.`

`Beginning execution loop`

`23`

`23`

`23`

`20`

`20`

`20`

`Batch execution completed 6 times.`

`*/`

Agian my experience has been that calendar table has outperformed a permanent or virtual tally table. This after lots of testing.

Recursion is known to be a poorly-performing method of generating numbers. Yeah, a crippled function probably will perform worse. Use an in-line cte and the performance is vastly better. Or even a properly clustered physical tally table.

Also, most people don't have custom nonclustered indexes on their calendar table, "because it's so small it doesn't matter anyway". I don't have any objection per se to adding them, it's just not commonly done.

1. Nothing I posted uses recursion. Look again.

2. People should put unique nonclustered indexes on their dim_date or calendar but dont. It's not like it gets modified all that often.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

• Alan.B (3/31/2016)

1. Nothing I posted uses recursion. Look again.

2. People should put unique nonclustered indexes on their dim_date or calendar but dont. It's not like it gets modified all that often.

I thought the pattern of E12 usage indicated a recursive query, maybe not.

[Edit: I can't repost all the code because the filter at work "thinks" any code with "UNION ALL" in it is "sql injection".]

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• ScottPletcher (3/31/2016)

Alan.B (3/30/2016)

Alan.B (3/30/2016)

ScottPletcher (3/30/2016)

Alan.B (3/30/2016)

ScottPletcher (3/30/2016)

Alan.B (3/29/2016)

Lastly, ditto the last Thursday of the month:

`SET STATISTICS IO ON;`

`PRINT 'Old way:';`

`SELECT DISTINCT MAX([Date]) OVER (PARTITION BY [Year],[MonthName],[WeekdayName]) AS [Date]`

`FROM dbo.itvf_CALENDAR('01/01/2014','12/31/2014')`

`WHERE [WeekdayName] = 'Thursday'`

`ORDER BY [Date]`

`PRINT CHAR(10)+'better way:';`

`SELECT MAX([Date])`

`FROM dbo.itvf_CALENDAR('01/01/2014','12/31/2014')`

`WHERE [Weekday] = 5`

`GROUP BY [Month]`

`SET STATISTICS IO OFF;`

One certainly doesn't need any calendar table at all just to get the last given day of a month(s). Just a standard tally table, to enumerate the months, and a simple mathematical calc to get the day:

His function is using a tally table so you and I are basically doing the same thing more or less. I was demonstrating a better way to get the last Thursday of the month than the example he included in his article (which uses the Calendar iTVF). His method uses DISTINCT MAX([Date]) OVER (PARTITION BY... whereas mine gets the job done with a simple GROUP BY and therefore no worktable, a fraction of the reads, etc...

On a separate note I used to use a tally table for these kinds of date calculations but have found that a nicely indexed calendar table (or dim_date table) outperforms a tally table.

Huh? How can I/O outperform NO I/O?? It's just arithmetic. The tally is used only for the number of months, NOT in the date/day of week calc at all.

Edit: I am using a physical tally table only because my filter at work does not allow "UNION ALL" in code (it assumes it's "sql injection" and rejects it(!)). A tally cte, esp. for a tiny number such as the number of months, would also be zero I/O.

Busy work day... I'll put together a performance test a little later tonight and you can tell if you think I'm missing something.

Argh! I had a longer reply and accidentally refreshed (CTRL+R is cool in SSMS, terrible in google Chrome). Anyhow, here's a couple tests I did with a bunch of dates. Not realistic to go back to the 1800's but I wanted enough rows to work with. The reads BTW is a red herring too because the calendar table generates virtually 0 reads. I have some better tests somewhere but could not find them so I through this together real quick. Note the superior calendar table execution plan (filter - free, all the action from non-clustered index seeks)

`USE tempdb`

`GO`

`/****************************************************************************************`

`(1) Create simplified calendar table`

`****************************************************************************************/`

`IF OBJECT_ID('dbo.calendar') IS NOT NULL DROP TABLE dbo.calendar;`

`CREATE TABLE dbo.calendar`

`(`

` DateTxt date primary key,`

` YearNbr smallint NOT NULL,`

` MonthNbr tinyint NOT NULL,`

` DayOfMonthNbr tinyint NOT NULL,`

` DayOfWeekNbr tinyint NOT NULL`

`);`

`-- On a real calendar table you want this index present with the required INCLUDE columns`

`CREATE UNIQUE NONCLUSTERED INDEX uq_xxx`

` ON dbo.calendar(YearNbr, MonthNbr, DateTxt);`

`CREATE NONCLUSTERED INDEX nc_xxx ON dbo.calendar (DayOfWeekNbr) INCLUDE (DateTxt,YearNbr,MonthNbr);`

`CREATE NONCLUSTERED INDEX nc_xxx2 ON dbo.calendar (DayOfMonthNbr) INCLUDE (DateTxt);`

`-- Populate the calendar table`

`WITH dates(DateTxt) AS`

`(`

` SELECT TOP (DATEDIFF(DAY,'18000101','22000101'))`

` CAST(DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1,'18000102') AS date)`

` FROM sys.all_columns a, sys.all_columns b`

`)`

`INSERT dbo.calendar`

`SELECT DateTxt, DATEPART(YEAR,DateTxt), DATEPART(MONTH,DateTxt), DATEPART(DAY,DateTxt), DATEPART(WEEKDAY,DateTxt)`

`FROM dates;`

`GO`

`/****************************************************************************************`

`(2) Create Tally Table function (using Jeff Moden's "fnTally")`

`****************************************************************************************/`

`CREATE FUNCTION dbo.fnTally`

`/**********************************************************************************************************************`

` Purpose:`

` Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Trillion.`

` As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.`

` Usage:`

`--===== Syntax example (Returns BIGINT)`

` SELECT t.N`

` FROM dbo.fnTally(@ZeroOrOne,@MaxN) t`

`;`

` Notes:`

` 1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.`

` Refer to the following URLs for how it works and introduction for how it replaces certain loops.`

` http://www.sqlservercentral.com/articles/T-SQL/62867/`

` http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers`

` 2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type`

` will cause the sequence to start at 1.`

` 3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.`

` 5. If @MaxN is negative or NULL, a "TOP" error will be returned.`

` 6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger`

` number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with`

` that many values, you should consider using a different tool. Wink`

` 7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending`

` sort is required, use code similar to the following. Performance will decrease by about 27% but it's still`

` very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.`

` If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.`

` DECLARE @MaxN BIGINT;`

` SELECT @MaxN = 1000;`

` SELECT DescendingN = @MaxN-N+1`

` FROM dbo.fnTally(1,@MaxN);`

` 8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by`

` ROW_NUMBER() OVER (ORDER BY (SELECT NULL))`

` Revision History:`

` Rev 00 - Unknown - Jeff Moden`

` - Initial creation with error handling for @MaxN.`

` Rev 01 - 09 Feb 2013 - Jeff Moden`

` - Modified to start at 0 or 1.`

` Rev 02 - 16 May 2013 - Jeff Moden`

` - Removed error handling for @MaxN because of exceptional cases.`

` Rev 03 - 22 Apr 2015 - Jeff Moden`

` - Modify to handle 1 Trillion rows for experimental purposes.`

`**********************************************************************************************************************/`

` (@ZeroOrOne BIT, @MaxN BIGINT)`

`RETURNS TABLE WITH SCHEMABINDING AS`

` RETURN 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) --10E1 or 10 rows`

`, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10E4 or 10 Thousand rows`

`,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c) --10E12 or 1 Trillion rows`

` SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.`

` UNION ALL`

` SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN`

`;`

`GO`

`--SELECT * FROM dbo.calendar`

`/****************************************************************************************`

`(3) Perf test1, just generate a bunch of dates`

`****************************************************************************************/`

`SET NOCOUNT ON;`

`PRINT 'Using calendar table:';`

`GO`

` DECLARE @x date, @st datetime = getdate();`

` SELECT @x = DateTxt FROM dbo.calendar WHERE DateTxt BETWEEN '18000101' AND '22000101' ;`

` PRINT DATEDIFF(MS,@st,getdate());`

`GO 6`

`PRINT 'Using tally function:';`

`GO`

` DECLARE @x date, @st datetime = getdate();`

` SELECT @x = CAST(DATEADD(DAY,N,'18000101') AS date)`

` FROM dbo.fnTally(0,DATEDIFF(DAY,'18000101','22000101')-1)`

` PRINT DATEDIFF(MS,@st,getdate());`

`GO 6`

`/*`

`Using calendar table:`

`Beginning execution loop`

`60`

`36`

`33`

`36`

`33`

`33`

`Batch execution completed 6 times.`

`Using tally function:`

`Beginning execution loop`

`53`

`53`

`50`

`50`

`48`

`50`

`Batch execution completed 6 times.`

`*/`

`/****************************************************************************************`

`(4) Perf test2, last day and last thursday`

`****************************************************************************************/`

`SET STATISTICS IO ON;`

`GO`

`DECLARE @start_date date = '18000101', @end_date date = '22000101', @st datetime = getdate(), @x1 date, @x2 date;`

`SELECT @x1 = DATEADD(DAY,-1,DateTxt), @x2 = DATEADD(DAY, -DATEDIFF(DAY, 3, DATEADD(DAY,-1,DateTxt)) % 7,DATEADD(DAY,-1,DateTxt))`

`FROM dbo.calendar`

`WHERE DayOfMonthNbr = 1`

`AND DateTxt BETWEEN DATEADD(MONTH,1,@start_date) AND DATEADD(MONTH,1,@end_date);`

`PRINT DATEDIFF(MS,@st,getdate());`

`SET STATISTICS IO OFF;`

`GO 6`

`DECLARE @start_date date = '18000101', @end_date date = '22000101', @st datetime = getdate(), @x1 date, @x2 date;`

`SELECT @x1 = last_day_of_month, @x2 = DATEADD(DAY, -DATEDIFF(DAY, Thursday, last_day_of_month) % 7, last_day_of_month)`

`FROM fnTally(0,DATEDIFF(MONTH, @start_date, @end_date)) t`

`CROSS APPLY`

`(`

` SELECT DATEADD(DAY, -1, DATEADD(MONTH, t.N + 1, @start_date)) AS last_day_of_month,`

` 3 AS Thursday --0 for Mon, 1 for Tue, etc..`

`) AS assign_alias_names`

`WHERE t.N BETWEEN 0 AND DATEDIFF(MONTH, @start_date, @end_date)`

`PRINT DATEDIFF(MS,@st,getdate());`

`GO 6`

`/*`

`Beginning execution loop`

`Table 'calendar'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.`

`120`

`16`

`10`

`10`

`13`

`10`

`Batch execution completed 6 times.`

`Beginning execution loop`

`23`

`23`

`23`

`20`

`20`

`20`

`Batch execution completed 6 times.`

`*/`

Agian my experience has been that calendar table has outperformed a permanent or virtual tally table. This after lots of testing.

Recursion is known to be a poorly-performing method of generating numbers. Yeah, a crippled function probably will perform worse. Use an in-line cte and the performance is vastly better. Or even a properly clustered physical tally table.

ScottPletcher (4/1/2016)

Alan.B (3/31/2016)

1. Nothing I posted uses recursion. Look again.

2. People should put unique nonclustered indexes on their dim_date or calendar but dont. It's not like it gets modified all that often.

I thought the pattern of E12 usage indicated a recursive query, maybe not.

[Edit: I can't repost all the code because the filter at work "thinks" any code with "UNION ALL" in it is "sql injection".]

Heh... I know we all get in a hurry sometimes but look at who wrote that Tally Function. You and I both know how much that particular fellow is against using recursion to create sequences. In fact, he wrote an article on the subject to demonstrate just how bad such a thing is.

http://www.sqlservercentral.com/articles/T-SQL/74118/

With that in mind, I can personally 😀 vouch that he'd never take such an approach and guarantee that his Tally Function doesn't use any form of recursion even though it contains a UNION ALL in the function. The UNION ALL is there just to enable the ability of the Tally Function to produce a return that either starts at 0 or 1.

"Must look eye". 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Wow, the quoting is kind of getting crazy.

Wow, the quoting is kind of getting crazy.

Can I quote you on that? 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Seems like I've seen these before but thanks for the reminder.

• How would you use this approach to get the next Business Day (Excluding Holidays)? Example Next Business Day for Friday 9/2/2016. Assuming we have a Holiday Table with 9/5/2016 set for Labor Day.

• michael-l-johnson - Tuesday, August 2, 2016 7:28 AM

How would you use this approach to get the next Business Day (Excluding Holidays)? Example Next Business Day for Friday 9/2/2016. Assuming we have a Holiday Table with 9/5/2016 set for Labor Day.

Wow.  We sure missed the boat on that question.  Did you ever get an answer?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• michael-l-johnson - Tuesday, August 29, 2017 10:09 PM

Then I'd use ScottPletcher's technique to build a real Calendar table and incorporate the holiday table into it in the form of an "IsBusinessDay" column.  Then it just becomes a matter of finding the minimum date greater than "today" where IsBusinessDay = 1.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.