Adding weekdays to select statement

Question

I have a select statement that pulls and consolidates data from multiple tables. I need to add additional logic to calculate the number of weekdays between the createdDt and CallEndDate (but if the callEndDate is null use the current date).

We are using SQL Server 2016 and am at a loss on how to incorporate this additional calculated logic with my select statement below. Any suggestions?

       SELECT c.ID, c.CreatedDt, c.LastUpdatedDt, c.CALLSTARTDT, c.CALLENDDT, c.DUEDT,
c.TYPE, c.DESCRIPTION, c.COMMENT, c.CONTRACT, c.DISCLOSUREPTAN, c.DISCLOSURENPI, c.DISCLOSURETIN,
c.DISCLOSUREIVR, c.CALLERFIRSTNAME, c.CALLERLASTNAME, c.CALLERPHONE, c.CALLEREMAIL, c.STATUS,
c.STATUSQUALIFIER, c.RATING, u1.Name as LastUpdatedByName, u2.Name as CreatedByName,
u3.Name as OwnedByName, p.PTAN, p.NPI,p.NAME, con.LOB

FROM Case c

LEFT JOIN ossys_User u1 ON c.LastUpdatedBy = u1.Id
LEFT JOIN ossys_User u2 ON c.CreatedBy = u2.ID
LEFT JOIN ossys_User u3 ON c.OwnedBy = u3.Id
INNER JOIN PROVIDER p ON c.TargetId = p.Id
INNER JOIN CONTRACTS con ON p.Contract = con.Contract

WHERE
c.LastUpdatedDt BETWEEN  '2019-01-01' AND '2019-07-01'
Order By c.CreatedDt ASC

What do you mean by weekdays: working days / Mon - Fri / Mon - Sun ? — Razvan, Jul 31 '19 at 20:08
Have you googled it? https://stackoverflow.com/questions/252519/count-work-days-between-two-dates — openshac, Jul 31 '19 at 20:13
I have googled it but I don't understand how to add the declare and diffdays logic to this query. I'm pretty new to SQL so just getting the joins set up and working was a task but I can't find a good example of adding calculated columns to an existing query. — Josh, Jul 31 '19 at 20:15

MichaelD · Answer 1 · 2019-08-01T05:17:09.193

Calculations can be included in your select statement as such:

SELECT c.ID, c.CreatedDt, c.LastUpdatedDt, c.CALLSTARTDT, c.CALLENDDT, c.DUEDT,
c.TYPE, c.DESCRIPTION, c.COMMENT, c.CONTRACT, c.DISCLOSUREPTAN,
c.DISCLOSURENPI,c.DISCLOSURETIN,c.DISCLOSUREIVR, c.CALLERFIRSTNAME, c.CALLERLASTNAME,
c.CALLERPHONE, c.CALLEREMAIL, c.STATUS, c.STATUSQUALIFIER, c.RATING, 
u1.Name as LastUpdatedByName, u2.Name as CreatedByName,
u3.Name as OwnedByName, 
p.PTAN, p.NPI,p.NAME, 
con.LOB, 
((DATEDIFF(dd, c.CreateDt, ISNULL(c.CALLENDDT,@GETDATE)) + 1)
-(DATEDIFF(wk, c.CreateDt, ISNULL(c.CALLENDDT,@GETDATE)) * 2)
-(CASE WHEN DATENAME(dw, c.CreateDt) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, ISNULL(c.CALLENDDT, @GETDATE)) = 'Saturday' THEN 1 ELSE 0 END))
As diff

FROM Case c

LEFT JOIN ossys_User u1 ON c.LastUpdatedBy = u1.Id
LEFT JOIN ossys_User u2 ON c.CreatedBy = u2.ID
LEFT JOIN ossys_User u3 ON c.OwnedBy = u3.Id
INNER JOIN PROVIDER p ON c.TargetId = p.Id
INNER JOIN CONTRACTS con ON p.Contract = con.Contract

WHERE
c.LastUpdatedDt BETWEEN  '2019-01-01' AND '2019-07-01'
Order By c.CreatedDt ASC

Although I would more likely go with the solution defined by the link posted by @openshac and put that into a function before calling as such.

CREATE FUNCTION [dbo].fn_CountWeekDays
(
    @fromdate Datetime,
    @todate Datetime
)
RETURNS TABLE AS RETURN
(

SELECT
   (DATEDIFF(dd, @fromdate, @todate) + 1)
  -(DATEDIFF(wk, @fromdate, @todate) * 2)
  -(CASE WHEN DATENAME(dw, @fromdate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @todate) = 'Saturday' THEN 1 ELSE 0 END)
  As NoOfWeekDays

)

GO

SELECT c.ID, c.CreatedDt,      c.LastUpdatedDt, c.CALLSTARTDT, c.CALLENDDT, c.DUEDT,
c.TYPE, c.DESCRIPTION, c.COMMENT, c.CONTRACT, c.DISCLOSUREPTAN,
c.DISCLOSURENPI,c.DISCLOSURETIN,c.DISCLOSUREIVR, c.CALLERFIRSTNAME, c.CALLERLASTNAME,
c.CALLERPHONE, c.CALLEREMAIL, c.STATUS, c.STATUSQUALIFIER, c.RATING, 
u1.Name as LastUpdatedByName, u2.Name as CreatedByName,
u3.Name as OwnedByName, 
p.PTAN, p.NPI,p.NAME, 
con.LOB, 
CountWeekDays(c.CreateDt, ISNULL(c.CALLENDDT,@GETDATE))
As diff

FROM Case c

LEFT JOIN ossys_User u1 ON c.LastUpdatedBy = u1.Id
LEFT JOIN ossys_User u2 ON c.CreatedBy = u2.ID
LEFT JOIN ossys_User u3 ON c.OwnedBy = u3.Id
INNER JOIN PROVIDER p ON c.TargetId = p.Id
INNER JOIN CONTRACTS con ON p.Contract = con.Contract

WHERE
c.LastUpdatedDt BETWEEN  '2019-01-01' AND '2019-07-01'
Order By c.CreatedDt ASC

Thank you, this is great information! Exactly what I have been looking for. — Josh, Jul 31 '19 at 20:40
Well it turns out our null dates aren't stored as NULLs in the DB. They are stored as 1900-01-01 00:00:00.000 so the ISNULL function doesn't really work here. But for non 1900-01-01 it works great. — Josh, Jul 31 '19 at 23:07
Replace ISNULL() with another CASE WHEN construct, or probably a better solution - create a function to determine if the date is NULL(ish). — MichaelD, Jul 31 '19 at 23:17
That's my plan for tomorrow. I have never created a function so this will be a fun thing to learn. I tried copy/pasting the solution including the functions in SQL Server Management studio, but it popped an error indicating the function must be the only statement in the batch. So I need to do some reading to determine what that actually means. I appreciate the guidance though, you've pointed me in the right direction. — Josh, Aug 01 '19 at 01:52
@MichaelD Tip: `( @@DateFirst + DatePart( weekday, SampleDate ) - 1 ) % 7 + 1` will always return an integer from `1` to `7` with `1` corresponding to Sunday regardless of the setting of `DateFirst` or `Language`. — HABO, Aug 01 '19 at 02:56
@Josh if you are going to cut/paste just put a ‘go’ between the function declaration and the ‘select’. I’ve updated the answer above — MichaelD, Aug 01 '19 at 05:18

Adding weekdays to select statement

1 Answers1