Post reply

Help on counting working days between two variable dates

  • April 28, 2010 at 10:06 am

    #220938

    I am a novice at T-SQL, which I use on our manufacturing system. THe problem here is working out how to calculate the number of working days between two variable dates.

    I have a calendar table (TM_calendar) that shows all dates from 1st January 2000 and specifies whether they are a working day or not (flagging weekends and bank holidays as non-working - the flag is 'daytype' and the values are 'W' for working and 'N' for non-working).

    I have created a set of queries that calculate the performance of our suppliers. The end result is to show if a delivery is late. In its simplest form, the 'daterecd' is is checked against the 'duedate'. This is easy using 'datediff' but the problem comes when working out if any of the days between 'duedate' and 'daterecd' are non-working days - I just need a count of working days that an order is overdue.

    Any help would be appreciated.

    Thanks

    Tom

  • April 28, 2010 at 12:51 pm

    #1159226

    Something like this?

    SELECT SUM(CASE WHEN daytype ='W' THEN 1 ELSE 0 END) as total_days

    FROM TM_calendar

    WHERE yourColumn>= @startDate

    AND yourColumn<@endDate


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • April 29, 2010 at 1:41 am

    #1159469

    Thanks for this. It works! Thanks very much.

    Tom

  • April 29, 2010 at 9:19 am

    #1159794

    tommill (4/29/2010)

    Thanks for this. It works! Thanks very much.

    Tom

    Glad I could help! 😀

    But do you know HOW it works, not only THAT it works?

    It's important to understand the code, not simply copy and paste... 😉


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • April 29, 2010 at 9:29 am

    #1159811

    Hi

    Yes, I think I do understand it, as I have had to tweak it to meet a number of varying conditions. One of the c6 variants is shown below. You will see it differs somewhat from your original code.

    Thanks again.

    Tom

    CASE

    WHEN ord_status = 'O1' THEN

    (SELECT SUM(CASE WHEN Despatch_Day = 'Y' THEN 1 ELSE 0 END) AS total_days

    FROM dbo.TM_calendar

    WHERE (FullDate > duedate) AND (FullDate < getdate()))

    END AS odue_days

Viewing 5 posts - 1 through 4 (of 4 total)

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