I'm trying to achieve results of a max date or a null date for a specific time period from a joined table. Is the issue with the join or my having clause? I'm at a loss right now. 

I've checked numerous questions on StackOverflow, and I haven't been able to achieve the desired outcome reporting the null results as well as the max results. 

```
SELECT 
i.item
, i.description
--, i.last_inv
--, i.change_date
, CASE WHEN MAX(case when m.trans_date is null THEN 1 ELSE 0 END) = 0 THEN MAX(m.trans_date) END AS 'last_used'
FROM item_mst i
LEFT JOIN matltran_mst m on m.item = i.item
WHERE i.item like '2%' and m.trans_type <> 'M'   
GROUP BY i.item, i.description
HAVING (MAX(m.trans_date) NOT BETWEEN '10/1/2017' and '5/8/2019' or MAX(m.trans_date) IS NULL) 
ORDER BY 'last_used'

```

I'm expecting about 8 results with a last\_used date and over 1,000 (actual number TBD), but instead I get the 8 results with the last\_used date, without any of the NULL dates.

Here's a sample of the expected results:

item last\_used
---------------

21050000202-0 2017-08-31 00:00:00.000
2200130425 2017-08-09 00:00:00.000
2200130494 2017-09-15 00:00:00.000
220030010 2017-09-25 00:00:00.000
2201050937-2 2017-09-01 00:00:00.000
22081001202 2017-08-16 00:00:00.000
2209070909 2017-08-15 11:31:45.230
221500 2017-08-21 00:00:00.000
21010000215 NULL
21050000215 NULL
21050000215-0 NULL
210800 NULL
21330000202 NULL
213600 NULL
22000000210 NULL