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

  • 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.
  • 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;
  • 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_yearint=2019,
    @input_monthint=9;
    declare
    @test_monthdate=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

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

  • 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 5 years, 1 month ago by  chenks.
    Attachments:
    You must be logged in to view attached files.
  • Steve Collins 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_yearint=2019,
    @input_monthint=9;
    declare
    @test_monthdate=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.

  • 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

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

  • 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_yearint=2019,
    @input_monthint=9;
    declare
    @test_monthdate=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

  • 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
  • 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 5 years, 1 month ago by  chenks.
  • 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;

     

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

    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)

  • 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;
  • Hey there... so further on down the road... I was wondering how the Calendar table was defined in this answer.   Any help would be much appreciated.  This thread explains the interest:

    https://www.sqlservercentral.com/forums/topic/transposing-the-data-and-then-converting-monthly-data-to-daily-data#post-3727375

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

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

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