Date logic (looking for first matching row in the past 90 days)

Question

I have a table of "patient visits" where each row records a single patient-visit event. In simplified form, the table is like this:

CREATE TABLE #MyVisits (
    PatientID char(10) NOT NULL, --Alphanumeric Patient ID
    VisitID char(10) NOT NULL, --Alphanumeric Visit ID
    Clinic varchar(10) NOT NULL, --Name of clinic visited
    DateVisit date NOT NULL --Date of visit
    EarliestVisitComment varchar(100) NULL -- Free text field
)

Patients - who always have the same Alphanumeric ID - may have only ever visited once, or very infrequently, or regularly.

What I need to do is, for each row, see if the given patient (using the PatientID field) had any prior visit within the previous 90 days (i.e. DateVisit minus 90 days), and record the Clinic + ' ' + VisitID for that visit (as a single string) in the EarliestVisitComment field.

If the patient has had multiple visits in the 90-day look-back window, I only want to identify the first / earliest visit the patient had in that window.

(If there is only one visit, then EarliestVisitComment can be null or blank.)

I have got as far as using ROW_NUMBER to partition and rank the data, but it feels like I am a long way off correctly grabbing the value I need, espcially as I have no interest in sequential visits more than 90 days apart. I have tried working with LAG() and NTILE() but feel stuck. I am sure I can figure our a "row-by-agonising-row" approach but hopefully there is a better way. Is there a kind SQL guru out there who can help?

Isn't it better to have a view that calculates this column. With a table, you will have to recalculate this every time something changes — siggemannen, Aug 28 '23 at 15:39

Charlieface · Accepted Answer · 2023-08-29T09:25:54.520

1

Could just use a subquery.

UPDATE v
SET EarliestVisitComment = earliest.EarliestVisitComment
FROM #MyVisits v
OUTER APPLY (
    SELECT TOP (1) EarliestVisitComment = earliest.Clinic + ' ' + earliest.VisitID
    FROM #MyVisits earliest
    WHERE earliest.PatientID = v.PatientID
      AND earliest.DateVisit >= DATEADD(day, -90, v.DateVisit)
      AND earliest.VisitID <> v.VisitID -- do you need this? what happens if there is only visit?
    ORDER BY
      v.DateVisit
) earliest;

But if you want to include cases where the visits are more than 90 days apart, but there are intervening visits less than 90 days apart then it's more complicated.

edited Aug 29 '23 at 09:25

answered Aug 28 '23 at 11:40

Charlieface

52,284
6
19
43

thanks so much. It seems obvious in retrospect, I evidently had a big blind spot on how to handle this. I did make a couple of changes - added TOP 1 to the subquery so it only returns a single answer per row and tweaked the sort order. Thanks so much for helping me out – Telescope2334 Aug 29 '23 at 04:30

Date logic (looking for first matching row in the past 90 days)

1 Answers1