How to retrieve the last n null values from sql table?

Question

I have table like this. From this i want the customer name who missed his last 3 appointments(Means his last 3 visit date is null).

+---------+------------------------+------------------------+
|Customer  |appointmentDate         |Vstdate                 |
+---------+------------------------+------------------------+
|a        |2017-01-01 00:00:00.000 |2017-01-01 10:42:33.793 |
|a        |2017-01-30 17:40:00.000 |NULL                    |
|a        |2017-01-30 17:40:00.000 |2017-01-30 19:52:39.000 |
|a        |2017-02-01 20:50:00.000 |2017-02-01 17:37:12.000 |
|a        |2013-02-14 12:20:59.407 |NULL                    |
|b        |2017-03-02 00:00:00.000 |2017-03-02 13:17:13.000 |
|b        |2017-03-25 12:30:00.000 |2017-03-25 14:30:00.000 |
|b        |2017-04-23 10:20:00.000 |NULL                    |
|b        |2017-05-27 12:30:00.000 |NULL                    |
|b        |2017-10-01 00:00:00.000 |NULL                    |
|c        |2017-03-02 00:00:00.000 |2017-03-02 13:17:13.000 |
|c        |2017-01-01 00:00:00.000 |2017-01-01 10:42:33.793 |
|c        |2017-01-30 17:40:00.000 |NULL                    |
|c        |2017-01-30 17:40:00.000 |2017-01-30 19:52:39.000 |
+---------+------------------------+------------------------+

For example from the above table only the customer b has missed his last 3 appointments(Means his last 3 visit date is null). I want only his name to be retried from the table.

Please Tag your database MSSQL or MySQL or PlSQL ?. Most Users tend to respond if you have made any attempt — Ven, Jul 19 '18 at 15:02
Possible duplicate of [Finding duplicate values in a SQL table](https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) — Tetsujin no Oni, Jul 19 '18 at 20:25

score 1 · Accepted Answer · answered Jul 19 '18 at 21:07

1

Use top 3:

select distinct customer from tmp c where (
  select max(vstdate) from (
    select top 3 vstdate from tmp e where e.customer = c.customer
    order by appointmentDate desc
  ) x
) is null;

Result:

customer  
----------
b

answered Jul 19 '18 at 21:07

The Impaler

45,731
9
39
76

score 0 · Answer 2 · answered Jul 19 '18 at 16:01

Use a window function:

declare @tmp table(Customer varchar(1), appointmentDate datetime2, Vstdate datetime2)
insert into @tmp values
 ('a', '2017-01-01 00:00:00.000', '2017-01-01 10:42:33.793')
,('a', '2017-01-30 17:40:00.000', NULL                     )
,('a', '2017-01-30 17:40:00.000', '2017-01-30 19:52:39.000')
,('a', '2017-02-01 20:50:00.000', '2017-02-01 17:37:12.000')
,('a', '2013-02-14 12:20:59.407', NULL                     )
,('b', '2017-03-02 00:00:00.000', '2017-03-02 13:17:13.000')
,('b', '2017-03-25 12:30:00.000', '2017-03-25 14:30:00.000')
,('b', '2017-04-23 10:20:00.000', NULL                     )
,('b', '2017-05-27 12:30:00.000', NULL                     )
,('b', '2017-10-01 00:00:00.000', NULL                     )
,('c', '2017-03-02 00:00:00.000', '2017-03-02 13:17:13.000')
,('c', '2017-01-01 00:00:00.000', '2017-01-01 10:42:33.793')
,('c', '2017-01-30 17:40:00.000', NULL                     )
,('c', '2017-01-30 17:40:00.000', '2017-01-30 19:52:39.000')

select t.Customer 
from (
    select *, row_number() over (partition by Customer order by appointmentDate desc) as ord
    from @tmp
) t
where t.ord <= 3 
group by t.Customer
having max(t.Vstdate) is null

Result:

How to retrieve the last n null values from sql table?

2 Answers2