CODEWITHSUNDEEP
sql-servert-sql
Owain Esau
Owain Esau

Reputation: 1922

Getting days since start of week (excluding weekends)

This is part of a query that is calculating the total revenue for a contract based on the time-frame "This Week" along with the start and end dates of the contract (billed hourly). 

SELECT (ChargeRate - PayRate) * 8 * 
    CASE 
        WHEN ContractStartDate <= DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP) 
        AND ContractEndDate >= CURRENT_TIMESTAMP
            THEN DATEDIFF(DAY, DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
        WHEN ContractEndDate <= CURRENT_TIMESTAMP 
        AND ContractEndDate >= DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
        AND ContractStartDate <= CURRENT_TIMESTAMP
            THEN DATEDIFF(DAY, DATEADD(DAY, 1-DATEPART(WEEKDAY, ContractEndDate), ContractEndDate), ContractEndDate)
        WHEN ContractStartDate >= DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
        AND ContractStartDate <= CURRENT_TIMESTAMP
        AND ContractStartDate >= CURRENT_TIMESTAMP
            THEN DATEDIFF(DAY, ContractStartDate, CURRENT_TIMESTAMP)
        WHEN ContractEndDate <= CURRENT_TIMESTAMP 
        AND ContractEndDate >= DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
        AND ContractStartDate >= CURRENT_TIMESTAMP
            THEN  DATEDIFF(DAY, ContractStartDate, ContractEndDate)
        ELSE DATEDIFF(DAY, DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
    END

What i am struggling with is how i can exclude Saturday and Sunday so the count never goes past 5 and where Sunday is not the start of the week but instead Monday. So the number of days worked would end up being [ Monday = 1, Tuesday = 2 ... Friday = 5, Saturday = 5, Sunday = 5 ] based on what day the query is run.

What happens at the moment is that if these graphs are run on Saturday the calculation uses 6 days, if its run on Sunday the calculation uses 0 days. Every day during the week is correct, this can be seen with the following query:

DECLARE @StartDate DATETIME = '2019-12-1'
DECLARE @StartDate2 DATETIME = '2019-11-30'

SELECT DATEDIFF(DAY, DATEADD(DAY, 1-DATEPART(WEEKDAY, @StartDate), @StartDate), @StartDate)
SELECT DATEDIFF(DAY, DATEADD(DAY, 1-DATEPART(WEEKDAY, @StartDate2), @StartDate2), @StartDate2)

Results should be 0 and 6.

The only solution i can come up with is to nest the case statement and check if the value = 0 or 6 and change it to a 5, like so:

WHEN ContractStartDate <= DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP) 
AND ContractEndDate >= CURRENT_TIMESTAMP
    THEN 
        CASE 
            WHEN DATEDIFF(DAY, DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP), CURRENT_TIMESTAMP) IN (0,6)
                THEN 5
            ELSE DATEDIFF(DAY, DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
        END

which works but is a bit messy and i am interested to see if there is a better solution to this.

Upvotes: 1

Views: 87

Answers (2)

Vasily
Vasily

Reputation: 5782

additional solution to already posted by @tim-monfries:

DECLARE @StartDate DATETIME = '2019-11-01';
DECLARE @EndDate DATETIME = '2019-11-30';

WITH cte AS
    (
        SELECT @StartDate AS SomeDate
        UNION ALL
        SELECT SomeDate+1 FROM cte WHERE SomeDate < @EndDate
    )
SELECT COUNT(*)
FROM   cte
WHERE  DATENAME(dw, SomeDate) NOT IN ('Sunday', 'Saturday');

Upvotes: 1

tmonfries
tmonfries

Reputation: 71

Here's one way, borrowed from Jeff Moden via SQLServerCentral. (I'd comment this reply, but I don't have enough whacky points :( )

--count weekdays between two dates
DECLARE @StartDate DATETIME = '2019-11-01'
DECLARE @EndDate DATETIME = '2019-11-30'

SELECT (DATEDIFF(DD, @StartDate, @EndDate) + 1) --Total days in period, including weekends
  -(DATEDIFF(WK, @StartDate, @EndDate) * 2) --minus number of whole weekends in the period * 2 days
  -(CASE WHEN DATENAME(DW, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) --minus 1 if the period starts on a Sunday
  -(CASE WHEN DATENAME(DW, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) --minus 1 if the period ends on a Saturday

Of course, there are issues here, such as the use of English day names that won't travel well, but those can be worked around if necessary.

I have variation of this built into a "WeekdayCount" function that comes in very handy!

Way more detail here: https://www.sqlservercentral.com/articles/calculating-work-days

Upvotes: 3

Related Questions