All Records BETWEEN Dates including NULLS 1

[ousoonerjoe]

Trying to locate all Job Numbers that have been invoiced within the last 90 days as well as non-invoiced items. Nulls will also need to be included in the result set. I don't know if I've been hit with the stupid stick this morning, but I'm getting all records or no records. There are some mistyped dates from 2019 that are actually helping realize something is not working right here. It is also returning values from 1994. Obviously a bit out the expected range. Any assistance/guidance is appreciated.

SELECT 
	[Date90] = DATEADD(DAY, -90, wpmas_dt_billed),
	[InvoiceDate] = wpmas_dt_billed,
	[JobNum] = wpmas_job_no,
	[Stat] = wpmas_job_status 
FROM tmpwpmas 
WHERE wpmas_div = '3'
	AND wpmas_dt_billed BETWEEN DATEADD(DAY, -90, wpmas_dt_billed) AND ISNULL(wpmas_dt_billed, GETDATE())
ORDER BY wpmas_dt_billed DESC;

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[RiverGuy]

AND (wpmas_dt_billed BETWEEN DATEADD(DAY, -90, GETDATE()) AND GETDATE() OR (wpmas_dt_billed IS NULL))

 

[markros]

Try instead

SELECT 
    [Date90] = DATEADD(DAY, -90, wpmas_dt_billed),
    [InvoiceDate] = wpmas_dt_billed,
    [JobNum] = wpmas_job_no,
    [Stat] = wpmas_job_status 
FROM tmpwpmas 
WHERE wpmas_div = '3'
    AND wpmas_dt_billed BETWEEN DATEADD(DAY, -90+datediff(day,0,GETDATE()),0) AND GETDATE()
UNION ALL
SELECT 
    [Date90] = DATEADD(DAY, -90, wpmas_dt_billed),
    [InvoiceDate] = wpmas_dt_billed,
    [JobNum] = wpmas_job_no,
    [Stat] = wpmas_job_status 
FROM tmpwpmas 
WHERE wpmas_div = '3'
    AND wpmas_dt_billed IS NULL

ORDER BY wpmas_dt_billed DESC;

PluralSight Learning Library

 

[ousoonerjoe]

Thank you, RiverGuy. I see what I did wrong...

BETWEEN DATEADD(DAY, -90, wpmas_dt_billed)

...was keeping all items within 90 days. wpmas_dt_billed should have been GETDATE(). What I'm not completely understanding is why

AND ISNULL(wpmas_dt_billed, GETDATE())

does not act the same as

OR (wpmas_dt_billed IS NULL))

I would have thought that the ISNULL() would have caused the NULL dates to evaluate out and be included without the OR statement?


--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------