CODEWITHSUNDEEP
sqlsql-servert-sql
Cascone
Cascone

Reputation: 25

TSQL - Window function / Ranking

I am trying to find all people that missed 3 or more consecutive appointments. I believe I can use a windowing function to accomplish this, but I am stuck and looking for some help.

Here is a sample of what I am looking for: For the following PATID = x001 meets the criteria for missing 1/12,1/14,1/15 And PATID = x002 Will not meet the criteria. 

PATID  DEPT   DATE       STATUS
x001   A002   1/1/2016   Missed
x001   A002   1/5/2016   Complete
x001   A002   1/8/2016   Missed
x001   A002   1/10/2016   Complete
x001   A002   1/12/2016   Missed
x001   A002   1/14/2016   Missed
x001   A002   1/15/2016   Missed
x001   A002   1/19/2016   Complete

x002   A003   1/1/2016   Missed
x002   A003   1/5/2016   Complete
x002   A003   1/8/2016   Missed
x002   A003   1/10/2016   Complete
x002   A003   1/12/2016   Missed
x002   A003   1/14/2016   Complete
x002   A003   1/15/2016   Missed
x002   A003   1/19/2016   Complete

This is what I have so far. 

SELECT 
PR.PATID
, PR.DEPT
, PR.DATE
, CASE WHEN PR.STATUS IN (3,4) THEN 'Cancel' WHEN PR.STATUS = 2 THEN 'COMPLETED' ELSE 'ERROR' END AS STATUS, ROW_NUMBER () OVER (PARTITION BY PR.PAT_ID,PR.DEPARTMENT_ID ORDER BY R.PAT_ID,PR.DEPARTMENT_ID,PR.CONTACT_DATE) AS RN  -- Just numbers the rows
, COUNT(*) OVER (PARTITION BY PR.PAT_ID,PR.DEPARTMENT_ID, CASE WHEN PR.APPT_STATUS_C IN (3,4) AND PR.CANCEL_REASON_C <> 4 THEN 'Cancel'                  WHEN PR.APPT_STATUS_C = 2 THEN 'COMPLETED' ELSE 'ERROR' END) AS RNC  -- Should have break at new statuses
FROM #PatsReturn AS PR

Once I figure out how to assign the correct breaks at the new status changes by dates, then I need to figure out a way to identify (possibly a new flag field??) the PATIDs that have missed 3+ consecutive....

Any help is greatly appreciated.

Thanks,

Upvotes: 0

Views: 65

Answers (2)

Alan Burstein
Alan Burstein

Reputation: 7918

If you go with Gordon's first solution you will want a POC index. Say this is your table:

CREATE TABLE #PatsReturn (PATID char(4), DEPT char(4), [date] date, [status] varchar(10));

You would want this index:

CREATE UNIQUE INDEX nc_PatsReturn  ON #PatsReturn(PATID, [date]) INCLUDE ([status]);

This will ensure a nice, linear sort-free query execution plan. The second solution would be a little trickier to index for.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269723

One method is just to use lag()/lead():

select distinct patid
from (select pr.*,
             lead(status) over (partition by patid order by date) as status_1,
             lead(status, 2) over (partition by patid order by date) as status_2
      from #PatsReturn pr
     ) pr
where status = 'Missed' and status_1 = 'Missed' and status_2 = 'Missed';

If you want information about each sequence, you can identify groups of similar appointments. One method is a difference of row numbers:

select patid, count(*) as numMissed, min(date), max(date)
from (select pr.*,
             row_number() over (partition by patid order by date) as seqnum_p,
             row_number() over (partition by patid, status order by date) as seqnum_ps
      from #PatsReturn pr
     ) pr
where status = 'Missed'
group by patid, (seqnum_p - seqnum_ps)
having count(*) >= 3;

To understand how this works, run the subquery and you can see the pattern where the difference in the two "seqnum" values is constant for sequences of the same status.

Upvotes: 2

Related Questions