count number of days between two dates excluding weekends for a particular month

  • chenks

    SSC Enthusiast

    Points: 132

    Hi guys, got a conundrum here that i'm pretty sure is possilbe but can't get my head around.

    I have a table that contains 5 fields - engineername, startdate, starttime, enddate, endtime (varchar, datetime, datetime, datetime, datetime) - see "capture.jpg" attachment.

    i want to be able to produce a list of results that shows the total number of days for each person in a particular month, and for it not to include weekend days. the startdate and enddate may be in different months, so it has to take that into account and not include those days - see "capture2.jpg" attachment.

    is this possible? basically i'll be passing 2 variables in to dictate which month is to checked @month and @year - example being month = 9 and year = 2019.

    Attachments:
    You must be logged in to view attached files.
  • pietlinden

    SSC Guru

    Points: 62451

    Something like this? (Use a Calendar table).

    use tempdb;
    GO
    -- count the dates between two days excluding weekends
    CREATE TABLE #Calendar (TheDate DATE PRIMARY KEY CLUSTERED);
    GO
    DECLARE @TheDate  DATE = '01-Jan-2019'
    WHILE @TheDate < '01-Feb-2019'
    BEGIN
     INSERT INTO #Calendar(TheDate) VALUES (@TheDate);
     SET @TheDate = DATEADD(day,1,@TheDate);
    END
    CREATE TABLE #Events (EventID INT IDENTITY
          , PersonID INT NOT NULL
          , StartDate DATE NOT NULL
          , EndDate DATE NOT NULL);
    GO
    INSERT INTO #Events (PersonID, StartDate, EndDate) VALUES (100,'01-01-2019','01-09-2019'),(100,'01-12-2019','01-15-2019');
    SELECT PersonID
     , StartDate
     , EndDate
     , COUNT(c.TheDate) AS DayCount
    FROM #Events e INNER JOIN #Calendar c ON (c.TheDate>=e.StartDate AND c.TheDate<=e.EndDate)
    WHERE DATEPART(WEEKDAY, c.TheDate) BETWEEN 2 AND 6
    GROUP BY PersonID, StartDate, EndDate;
  • scdecade

    Old Hand

    Points: 394

    This is an application for Jonathan's daterange function.  Here is his article:

    https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

    Something like this:

    declare
    @input_year int=2019,
    @input_month int=9;
    declare
    @test_month date=datefromparts(@input_year,@input_month,1);

    select
    count(*) days_in_month_excluding_wkends
    from
    dbo.daterange(@test_month,eomonth(@test_month),'dd',1)
    where
    datepart(dw, [value]) not in(1,7);

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • sterling3721

    SSC Veteran

    Points: 277

    this question was asked several times, if you have other holidays besides weekends(new year's day, christmas, labor day, etc), a calendar table is probably the best approach. You can create such a table for 100 years very easily. I keep such a table in utility database from 2000 - 2050, enough for another 30 years.

  • chenks

    SSC Enthusiast

    Points: 132

    the only days i would be exluding are weekend days (so no other holidays to be excluded).

    so would a calendar still be the most appropriate way to do this?

    I currently don't have calendar table at all, but i guess creating one would be quick and handy to have anyway?

    I've created one anyway now (100 years from the start of this year).

    • This reply was modified 1 week ago by  chenks.
    Attachments:
    You must be logged in to view attached files.
  • chenks

    SSC Enthusiast

    Points: 132

    scdecade wrote:

    This is an application for Jonathan's daterange function.  Here is his article:

    https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

    Something like this:

    declare
    @input_year int=2019,
    @input_month int=9;
    declare
    @test_month date=datefromparts(@input_year,@input_month,1);

    select
    count(*) days_in_month_excluding_wkends
    from
    dbo.daterange(@test_month,eomonth(@test_month),'dd',1)
    where
    datepart(dw, [value]) not in(1,7);

    not SQL 2000 compatible unfortunately.

  • scdecade

    Old Hand

    Points: 394

    Yea yea, I know or I noticed after I posted that.  Instead of eomonth() you could use:

    dateadd(day,-1,dateadd(month,1,@test_month))

    Then you could use a tally table and dateadd function.  Or just create a calendar table like the others have posted.

    Seriously tho, Sql 2000 is obsolete.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • chenks

    SSC Enthusiast

    Points: 132

    yeah i know it's obsolete, but it's what i have to work with.

    a legacy application uses it and there's no requirement to upgrade. the work involved would greatly outweigh the benefits.

    but yeah i've created a calendar table (as noted above).

    however, not sure how to use that in conjuction with what i want to achieve.

  • scdecade

    Old Hand

    Points: 394

    It would be similar to what was posted except using your calendar table instead of the daterange function.  Eomonth can't be used either because it's Sql 2016+.

    Something like this:

    declare
    @input_year int=2019,
    @input_month int=9;
    declare
    @test_month date=datefromparts(@input_year,@input_month,1);

    select
    count(*) days_in_month_excluding_wkends
    from
    YourCalendarTable
    where
    TheDate between @test_month and dateadd(day,-1,dateadd(month,1,@test_month))
    and datepart(dw, TheDate) not in(1,7);

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • DesNorton

    SSC-Insane

    Points: 22692

    chenks wrote:

    the only days i would be exluding are weekend days (so no other holidays to be excluded).

    so would a calendar still be the most appropriate way to do this?

    I currently don't have calendar table at all, but i guess creating one would be quick and handy to have anyway?

    I've created one anyway now (100 years from the start of this year).

     

    Considering your use case, I would add a calculated field to indicate whether the date is a weekend.  Then use that in your WHERE clause, or else use a SUM instead of a COUNT

    ALTER TABLE YOUR_TABLE_NAME
    ADD isWeekend AS CAST(CASE WHEN DATEDIFF(dd, 0, CalendarDate) %7 >= 5 THEN 1 ELSE 0 END AS int) PERSISTED
  • chenks

    SSC Enthusiast

    Points: 132

    guessing that uses another term that SQL2000 doesn't like, as it doesn't seem to like the word "PERSISTED"

    but i'll just drop the table and re-create it with the extra column

    • This reply was modified 6 days, 2 hours ago by  chenks.
  • DesNorton

    SSC-Insane

    Points: 22692

    A calendar table is definitely the better option.

    But, just for fun, I have come up with 2 options that *SHOULD* work with SQL2000.  Unfortunately I do not have SQL2000 to test on.

     

    Sample Data

    IF OBJECT_ID(N'tempdb..#diary', N'U') IS NOT NULL
    BEGIN
    DROP TABLE #diary
    END

    CREATE TABLE #diary (
    diary_id int NOT NULL
    , diary_engineer_id char(7) NOT NULL
    , diary_start_date datetime NOT NULL
    , diary_end_date datetime NOT NULL
    )

    INSERT INTO #diary ( diary_id, diary_engineer_id, diary_start_date, diary_end_date )
    VALUES ( 37, 'PERSON1', '2019-08-25', '2019-09-02' )
    , ( 32, 'PERSON1', '2019-09-04', '2019-09-05' )
    , ( 34, 'PERSON1', '2019-09-10', '2019-09-10' )
    , ( 35, 'PERSON1', '2019-09-16', '2019-09-20' )
    , ( 36, 'PERSON1', '2019-09-27', '2019-09-30' )
    , ( 39, 'PERSON2', '2019-09-11', '2019-09-17' )
    , ( 38, 'PERSON3', '2019-09-25', '2019-10-03' )
    , ( 40, 'PERSON3', '2019-09-05', '2019-09-12' )
    , ( 41, 'PERSON4', '2019-08-30', '2019-09-02' )
    , ( 42, 'PERSON4', '2019-09-14', '2019-09-14' )

    Option 1 (COUNT)

    DECLARE @year  int     = 2019;
    DECLARE @month tinyint = 09;

    SELECT d.diary_engineer_id, WorkingDays = COUNT(*)
    FROM #diary AS d
    INNER JOIN (
    SELECT CalDates.CalendarDate, isWeekday = CAST(CASE WHEN DATEDIFF(dd, 0, CalDates.CalendarDate) %7 < 5 THEN 1 ELSE 0 END AS int)
    FROM (
    SELECT CalendarDate = DATEADD(dd, CalDays.MonthDay -1, DATEADD(mm, @month -1, DATEADD(yy, @year-1900, 0)))
    FROM (
    SELECT 1 AS MonthDay UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
    SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
    SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
    SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL
    SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL
    SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31
    ) AS CalDays
    ) AS CalDates
    WHERE CalDates.CalendarDate < DATEADD(mm, @month, DATEADD(yy, @year-1900, 0))
    ) AS c
    ON c.CalendarDate >= d.diary_start_date
    AND c.CalendarDate <= d.diary_end_date
    WHERE c.isWeekday = 1
    GROUP BY d.diary_engineer_id
    ORDER BY d.diary_engineer_id;

    Option 2 (SUM)

    DECLARE @year  int     = 2019;
    DECLARE @month tinyint = 09;

    SELECT d.diary_engineer_id, WorkingDays = SUM(c.isWeekday)
    FROM #diary AS d
    INNER JOIN (
    SELECT CalDates.CalendarDate, isWeekday = CAST(CASE WHEN DATEDIFF(dd, 0, CalDates.CalendarDate) %7 < 5 THEN 1 ELSE 0 END AS int)
    FROM (
    SELECT CalendarDate = DATEADD(dd, CalDays.MonthDay -1, DATEADD(mm, @month -1, DATEADD(yy, @year-1900, 0)))
    FROM (
    SELECT 1 AS MonthDay UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
    SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
    SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
    SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL
    SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL
    SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31
    ) AS CalDays
    ) AS CalDates
    WHERE CalDates.CalendarDate < DATEADD(mm, @month, DATEADD(yy, @year-1900, 0))
    ) AS c
    ON c.CalendarDate >= d.diary_start_date
    AND c.CalendarDate <= d.diary_end_date
    GROUP BY d.diary_engineer_id
    ORDER BY d.diary_engineer_id;

     

  • Jeff Moden

    SSC Guru

    Points: 994647

    Had to take my non-calendar table solution down (previously posted in this spot).  It had an error when more than 1 month was spanned.

     

     

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • chenks

    SSC Enthusiast

    Points: 132

    a working solution has been found thanks to @aaron-n-cutshall

    has been tested and works.

    declare @month int;
    declare @year int;

    select @month = 9, @year = 2019;

    select d.Diary_Engineer_ID, sum(1-c.isWeekend) as TotalWeekDays

    from Diary d

    inner join Calendar c

    on c.CalendarDate between convert(datetime, cast(@year as varchar) + '-' + cast(@month as varchar) + '-01', 101)

    and convert(datetime, cast(case when @month = 12 then @year+1 else @year end as varchar) + '-' + cast(case when @month = 12 then 1 else @month+1 end as varchar) + '-01', 101) - 1

    and c.CalendarDate between d.Diary_Start_Date and d.Diary_End_Date

    group by d.Diary_Engineer_ID;

Viewing 14 posts - 1 through 14 (of 14 total)

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