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 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".

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

  • Wow, the quoting is kind of getting crazy.

  • 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)
    Intro to Tally Tables and Functions

  • 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".

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

  • I have not received a reply.

  • 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)
    Intro to Tally Tables and Functions

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

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