Calendar Table Function

Question

Calendar Table Function

Viewing 10 posts - 31 through 40 (of 40 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

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

ScottPletcher SSC Guru Points: 101192 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 3

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.

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

akljfhnlaflkj

SSC Guru

Points: 76202

April 27, 2016 at 7:14 am

#1874924

Wow, the quoting is kind of getting crazy.

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 5

Iwas Bornready (4/27/2016)
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.

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

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

michael-l-johnson SSC Enthusiast Points: 126 More actions · Answer 7

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.

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 8

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.

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

michael-l-johnson SSC Enthusiast Points: 126 More actions · Answer 9

michael-l-johnson

SSC Enthusiast

Points: 126

August 29, 2017 at 10:09 pm

#1957350

I have not received a reply.

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 10

michael-l-johnson - Tuesday, August 29, 2017 10:09 PM
I have not received a reply.

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.

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

Calendar Table Function

Cookies on SQLServerCentral