Compensating for NULL values in SQL Queries

Question

Let's say I have two tables of data that looks like this:

DvTestResults

DVTR_DeviceNo	DVTR_TestedOnAt	DVTR_TesterNo
DV00001	2022-08-11 14:15:16.000	0001
DV00001	2022-08-19 21:08:16.000	NULL
DV00001	2022-09-22 08:14:32.000	NULL
DV00002	2023-06-03 18:18:03.000	NULL
DV00002	2023-08-15 19:01:36.000	0007
DV00003	2022-12-23 08:04:47.000	0014
DV00003	2023-01-03 10:09:51.000	0014
DV00003	2023-01-09 08:01:33.000	0014
DV00004	2023-03-14 11:49:02.000	0298
DV00004	2023-03-15 09:08:13.000	0298
DV00005	2022-04-28 16:23:14.000	NULL
DV00005	2022-08-14 08:20:56.000	NULL

Tester

T_TesterNo	T_TesterName
0001	John
0007	Stacy
0014	James
0298	Carlos

I want to find the last time each device was tested and who tested it, ordered by device number without any device numbers repeating.

I have the following code:

SELECT DvTestResults.DVTR_DeviceNo, max.LastTimeTested, Tester.T_TesterName 
FROM DvTestResults
INNER JOIN
(
    SELECT DVTR_DeviceNo, MAX(DVTR_TestedOnAt) as LastTimeTested 
    FROM DvTestResults
    GROUP BY DVTR_DeviceNo
) as max
    on max.DVTR_DeviceNo = DvTestResults.DVTR_DeviceNo and max.LastTimeTested = DvTestResults.DVTR_TestedOnAt
INNER JOIN Tester ON Tester.TesterNo = DvTestResults.DVTR_TesterNo
ORDER BY DvTestResults.DVTR_DeviceNo

Unfortunately, while the code works fine for units where the last test's DVTR_TesterNo is not NULL, it does not give values for when it is. What would be a good solution if I wanted to have a list of the last times a device number was tested and who tested even if it came up NULL. Preferably, like with DV00001 in this example, the tester is typically the same name of the last person who tested it (in this case, John) — even though they didn't log in to do the last test that DV00001 did. So, for this example, I'd like an output for DV00001 of:

DVTR_DeviceNo	LastTimeTested	T_TesterName
DV00001	2022-09-22 08:14:32.000	John

For a device like DV00005, which has only ever been tested anonymously, I'd like an output of:

DVTR_DeviceNo	LastTimeTested	T_TesterName
DV00005	2022-08-14 08:20:56.000	Anonymous

Can anyone help?

Change your inner join on Tester to a left join. Then in your name column use `coalesce(Test.T_TesterName, 'Anonymous')` — Sean Lange, Mar 23 '23 at 20:31
@JoelCoehoorn yeah I didn't test anything but just glanced at it. I knew that funky subquery was suspect to some other issues. — Sean Lange, Mar 23 '23 at 20:33

GMB · Accepted Answer · 2023-03-24T21:56:21.520

Getting the latest test per device is a typical top-1-per-group problem, which we can approach with row_number() and filtering:

select *
from (
    select r.*,
        row_number() over(partition by DVTR_DeviceNo order by DVTR_TestedOnAt desc) rn
    from DvTestResults r
) r
where rn = 1

We would then left join on the testers table to try and bring the tester’s name.

Preferably, like with DV00001 in this example, the tester is typically the same name of the last person who tested it (in this case, John)

Retrieving the latest tester (hence ignoring null values) is a more complex task in SQL Server. We can either use apply, or more window functions. The latter would be:

select r.DVTR_DeviceNo, r.DVTR_TestedOnAt, coalesce(t.T_TesterName, 'Anonymous') T_TesterName
from (
    select r.*,
        max(DVTR_TesterNo) over(partition by DVTR_DeviceNo, grp) LastDVTR_TesterNo
    from (
        select r.*,
            row_number()         over(partition by DVTR_DeviceNo order by DVTR_TestedOnAt desc) rn,
            count(DVTR_TesterNo) over(partition by DVTR_DeviceNo order by DVTR_TestedOnAt) grp
        from DvTestResults r
    ) r
) r
left join Tester t on t.T_TesterNo = r.LastDVTR_TesterNo
where r.rn = 1

Related: How to make LAG() ignore NULLs in SQL Server

Demo, based on the test data created by @marcothesane:

DVTR_DeviceNo	DVTR_TestedOnAt	T_TesterName
DV00001	2022-09-22 08:14:32.000	John
DV00002	2023-08-15 19:01:36.000	Stacy
DV00003	2023-01-09 08:01:33.000	James
DV00004	2023-03-15 09:08:13.000	Carlos
DV00005	2022-08-14 08:20:56.000	Anonymous

This is better. I (also) misread the question at first, seeing the 'Anonymous' listing as meaning we were allowed to use a simple LEFT JOIN. Reading it again, I see that is only valid for the case where a known person has _never_ tested the device. — Joel Coehoorn, Mar 23 '23 at 21:27
This is good. The only issue is that it doesn't bring up the very last time that the device was tested. — a3dur4n, Mar 24 '23 at 21:35
@a3dur4n: I reviewed the query and fixed a few glitches - and added a fiddle to the answer. This seems to work as expected now. — GMB, Mar 24 '23 at 21:57

Joel Coehoorn · Answer 2 · 2023-03-23T21:36:34.023

Based on my new understanding of the problem, this should work:

SELECT DVTR_DeviceNo, MAX(DVTR_TestedOnAt) As DVTR_TestedOnAt
    , coalesce(
        (
         SELECT T_TestName
         FROM (
            SELECT DVTR_TesterNo, DVTR_DeviceNo 
               , row_number() over 
                   (PARTITION BY DVTR_DeviceNo
                    ORDER BY case when DVTR_TesterNo IS NULL THEN 1 ELSE 0 END
                               ,DVTR_TestedOnAt DESC) rn     
            FROM DvTestResults
         ) dtr0
         LEFT JOIN Tester t ON t.T_TesterNo = dtr0.DVTR_TesterNo
         WHERE dtr0.rn = 1 AND dtr0.DVTR_DeviceNo = dtr.DVTR_DeviceNo
        ) 
      , 'Anonymous') T_TestName
FROM DvTestRestuls dtr
GROUP BY DVTR_DeviceNo

marcothesane · Answer 3 · 2023-03-24T00:35:48.757

I would work with MAX(dvtr_testerno) OVER(PARTITION BY dvtr_deviceno), in SQL Server, to compensate for the missing LAST_VALUE(a IGNORE NULLS) to backfill the missing foreign keys where I can, in a subquery on DVTestResults (I call that subquery w_testerno), and then left join that subquery with the testers; the resulting NULLs for t_testername become 'Anonymous' thanks to an IFNULL() :

WITH
-- your input
dvtestresults(dvtr_deviceno,dvtr_testedonat,dvtr_testerno) AS (
          SELECT 'DV00001','2022-08-11 14:15:16.000',0001
UNION ALL SELECT 'DV00001','2022-08-19 21:08:16.000',NULL
UNION ALL SELECT 'DV00001','2022-09-22 08:14:32.000',NULL
UNION ALL SELECT 'DV00002','2023-06-03 18:18:03.000',NULL
UNION ALL SELECT 'DV00002','2023-08-15 19:01:36.000',0007
UNION ALL SELECT 'DV00003','2022-12-23 08:04:47.000',0014
UNION ALL SELECT 'DV00003','2023-01-03 10:09:51.000',0014
UNION ALL SELECT 'DV00003','2023-01-09 08:01:33.000',0014
UNION ALL SELECT 'DV00004','2023-03-14 11:49:02.000',0298
UNION ALL SELECT 'DV00004','2023-03-15 09:08:13.000',0298
UNION ALL SELECT 'DV00005','2022-04-28 16:23:14.000',NULL
UNION ALL SELECT 'DV00005','2022-08-14 08:20:56.000',NULL
)
,
tester(t_testerno,t_testername) aS (
          SELECT 0001,'John'
UNION ALL SELECT 0007,'Stacy'
UNION ALL SELECT 0014,'James'
UNION ALL SELECT 0298,'Carlos'
)
-- end of your input. Query starts here, replace following comma with "WITH"
,
w_testerno AS (
  SELECT
    dvtr_deviceno
  , dvtr_testedonat
  , MAX(dvtr_testerno) OVER(
      PARTITION BY dvtr_deviceno 
    ) AS dvtr_testerno
  FROM dvtestresults
)
SELECT 
  dvtr_deviceno
, dvtr_testedonat
, isnull(t_testername,'Anonymous') AS t_testermane
FROM      w_testerno
LEFT JOIN tester ON t_testerno = dvtr_testerno
ORDER BY 1,2;

dvtr_deviceno	dvtr_testedonat	t_testermane
DV00001	2022-08-11 14:15:16	John
DV00001	2022-08-19 21:08:16	John
DV00001	2022-09-22 08:14:32	John
DV00002	2023-06-03 18:18:03	Anonymous
DV00002	2023-08-15 19:01:36	Stacy
DV00003	2022-12-23 08:04:47	James
DV00003	2023-01-03 10:09:51	James
DV00003	2023-01-09 08:01:33	James
DV00004	2023-03-14 11:49:02	Carlos
DV00004	2023-03-15 09:08:13	Carlos
DV00005	2022-04-28 16:23:14	Anonymous
DV00005	2022-08-14 08:20:56	Anonymous

Compensating for NULL values in SQL Queries

3 Answers3