Use an if or statement

Transact-SQL
1

I need a third column checking to see if [Days Late] is greater than 5 or less than -5. If True, "Out of Limits", if false, "Within Limits"

SELECT rtrim(pl.PARTNUMBER) as [Part Number]
, cast(rh.RECV_DATE-pl.DUE_DATE as integer) as [Days Late]

Thanks for your help.

2

case when cast(rh.RECV_DATE-pl.DUE_DATE as integer) > 5 or cast(rh.RECV_DATE-pl.DUE_DATE as integer) < -5 then 'Out of Limits'
else 'Within Limits'
end

Are those columns datetime data type? If so, you should be using DATEADD function on them. Which version of SQL Server are you using?

3

Yes, they are datetime data. I'm using SQL Server 2008 R2

4

You can use outer apply to tidy up a bit and you can further reference in the select statement if needed.

SELECT rtrim(PARTNUMBER) as [Part Number]
, a.DaysLate as [Days Late]
, case when a.DaysLate > 5 or a.DaysLate < -5 then 'Out of Limits' else 'Within Limits' end as [Check]
from YourTable
outer apply
(
select cast(RECV_DATE-DUE_DATE as integer)
) a(DaysLate);

5

I believe that code will break in 2012+ which is why I asked about the version. You have to use date functions going forward.

6

I thank you both for your suggestions. I have it working now.

7

I believe it only breaks if you change the data type from datetime to datetime2 - but it would be much better to use date functions anyways to prevent code breaking in a future version when the data type is changed.

8

yes, it errors if it's datetime2