Exclude weekends

• PSB

  SSChampion

  Points: 13195

  More actions

  August 10, 2017 at 9:11 am

  #327929

  Hi,

  I need to have a calculated field called EndDate and it should exclude weekends from the calucation .

  CREATE TABLE #Dates
  (
  StartDate DATE,
  Duration INT,
  EndDate DATE
  )

  INSERT INTO #Dates (StartDate,Duration)
  SELECT '2017-08-07',5 UNION
  SELECT '2017-08-10',3

  Desired Result

  SELECT '2017-08-07',5,'2017-08-11'

  SELECT '2017-08-10',3,'2017-08-14'

  Thanks,

• Thom A

  SSC Guru

  Points: 99143

  More actions

  August 10, 2017 at 9:14 am

  #1954831

  Have a look at: http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• drew.allen

  SSC Guru

  Points: 77014

  More actions

  August 10, 2017 at 9:44 am

  #1954834

  I wasn't sure if you needed to also account for holidays.  I came up with this approach that would account for holidays, but it does require a calendar table.  (You would need that anyhow to track holidays.)

  Data Setup
  CREATE TABLE #cal (
    dt    DATE NOT NULL PRIMARY KEY CLUSTERED,
    is_business_dt BIT NOT NULL
)

  INSERT #cal
VALUES
    ('20170804', 1),
    ('20170805', 0),
    ('20170806', 0),
    ('20170807', 1),
    ('20170808', 1),
    ('20170809', 1),
    ('20170810', 1),
    ('20170811', 1),
    ('20170812', 0),
    ('20170813', 0),
    ('20170814', 1),
    ('20170815', 1)

  SELECT *
FROM #cal

  CREATE TABLE #Dates
(
StartDate DATE,
Duration INT,
EndDate DATE
)

  INSERT INTO #Dates (StartDate,Duration)
SELECT '2017-08-07',5 UNION
SELECT '2017-08-10',3


  Solution:
  SELECT *
FROM #Dates
CROSS APPLY (
    SELECT dt AS EndDate
    FROM #cal
    WHERE dt >= StartDate
        AND is_business_dt = 1
    ORDER BY dt
    OFFSET Duration -1 ROWS
    FETCH NEXT 1 ROWS ONLY
) ed


  Cleanup:
  DROP TABLE #cal, #Dates

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• PSB

  SSChampion

  Points: 13195

  More actions

  August 10, 2017 at 10:19 am

  #1954839

  Perfect. Thanks 🙂

• jcelko212 32090

  SSCrazy Eights

  Points: 9304

  More actions

  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.