sql2015 sql2015 - 19 days ago 7
SQL Question

Querying result from select part of statement

I have a stored procedure to work out how many working days between two dates

select
casekey, LoginName, casestartdatedate,
dbo.CalcWorkDaysBetween(casestartdatedate, GETDATE()) AS 'WD'
from
Car_case with (nolock)
where
dbo.CalcWorkDaysBetween(casestartdatedate, GETDATE()) <= DATEADD(dd,DATEDIFF(dd, 0, GETDATE()), -60)
and CaseClosedDateDate is null
order by
CaseStartDateDate asc


In my select part of statement I want to show the number of working days between the case start date and today's date. This part is fine. But I only want to return cases where the 'working days' is 60 days or greater - I'm having trouble with this part of query. See my code above. not too sure why its not working. It's returning results less than and greater than 60 days making me realize I've gone wrong somewhere.

Any help would be appreciated!

Answer

If I understand correctly, you just need to fix the where condition:

select casekey, LoginName, casestartdatedate,
       dbo.CalcWorkDaysBetween(casestartdatedate, GETDATE()) AS WD
from Car_case cc with (nolock)
where dbo.CalcWorkDaysBetween(casestartdatedate, GETDATE()) >= 60 and
      CaseClosedDateDate is null
order by CaseStartDateDate asc;

Note: In your version you are comparing the result of the function (which is presumably an integer) to a date.