Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Exclude weekends RE: Exclude weekends

  • August 19, 2017 at 10:29 pm

    #1955908

    PSB - Thursday, August 10, 2017 9:11 AM

    Are those 2 day or 3 day or 4 day weekends? 🙂 Are you a Moslem, so you only get Friday? 
    Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math.

    CREATE TABLE Calendar
    (cal_date DATE NOT NULL PRIMARY KEY,
    julian_business_day INTEGER NOT NULL,
    ...);

    The Julian business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.

    INSERT INTO Calendar
    VALUES ('2007-04-05', 42);
    ('2007-04-06', 43); -- good friday
    ('2007-04-07', 43);
    ('2007-04-08', 43);-- Easter sunday
    ('2007-04-09', 44);
    ('2007-04-10', 45); --Tuesday

    To compute the business days from Thursday of this week to next Tuesdays:

    SELECT (C2. julian_business_nbr - C1. julian_business_nbr)
    FROM Calendar AS C1, Calendar AS C2
    WHERE C1.cal_date = '2007-04-05',
    AND C2.cal_date = '2007-04-10';

    [/code]The Julian business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday. INSERT INTO Calendar VALUES ('2007-04-05', 42); ('2007-04-06', 43); -- good friday ('2007-04-07', 43); ('2007-04-08', 43);-- Easter sunday ('2007-04-09', 44); ('2007-04-10', 45); --Tuesday To compute the business days from Thursday of this week to next Tuesdays: SELECT (C2. julian_business_nbr - C1. julian_business_nbr) FROM Calendar AS C1, Calendar AS C2 WHERE C1.cal_date = '2007-04-05', AND C2.cal_date = '2007-04-10';

    Please post DDL and follow ANSI/ISO standards when asking for help.