CODEWITHSUNDEEP
sqlsql-servert-sql
Jane
Jane

Reputation: 11

T-SQL find the most recent customer record with no subsequent appts booked

I have Customer Appt table:

ID PK
CID FK
ApptDate
Status ( it could be; awaiting response, cancelled, did not attend, attended)

Customer Table:

CID PK
Name
SUrname
address etc.

every customer have multiple appointments booked and my task is finding the last appointment where status is not Attended And customer have not had any subsequent appts booked..

i've tried subquery but didn't work..also tried this:

select ID, CID, max(ApptDate)
from customer_appts
where status!= 'Attended'

this is not checking if there wasnt any subsequent appts booked.. I think i need to use if statement or nested.. if any one could direct me i'd be grateful

UPDATE: Example: appt records for customer ID: 20 -- it should return last record date 23/20/20-- No subsequent appts booked after that date and status is not attended

ID  CID Appt Date   Status
1   20  27/01/20    Not Attended
2   20  30/01/20    Attended
3   20  23/02/20    cancelled

MORE EXAMPLES

ID  CID Appt Date   Status
1   30  27/01/20    Not Attended
2   30  30/01/20    Cancelled
3   30  23/02/20    Attended

for Customer 30 it should NOT return any records as after the 'Cancelled' appts we booked them an appointment and status is 'Attended'

Upvotes: 0

Views: 473

Answers (2)

plalx
plalx

Reputation: 43718

I think you are trying to find out which client didn't have an appointment since the last time they missed one. Note that I've assumed id was an identity column and as good as the date for ordering.

Here's one way of doing it...

SELECT *
FROM customer_appts a
WHERE
  status <> 'Attended'
  AND NOT EXISTS (
    SELECT 1
    FROM customer_appts
    WHERE cid = a.cid AND id > a.id
  )

Here's another...

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY cid ORDER BY id DESC) AS ordering
  FROM customer_appts
) a
WHERE 
  a.ordering = 1
  AND status <> 'Attended'

SQL FIDDLE

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

my task is finding the last appointment where status is not Attended.

I think you want:

select a.*
from customer_appts a
where a.id = (select top (1) a2.id
              from customer_appts a2
              where a2.cid = a.cid and
                    a2.status <> 'Attended'
              order by a2.ApptDate desc
             );

Upvotes: 0

Related Questions