I am trying to calculate a KPI for each patient, the KPI is called "Initial prescription start date(IPST)".

The definition of IPST is if the patient has a negative history of using that particular medication for 60 days before a start date that start date is a IPST.

For example- See screen shot below, for patient with ID=101, I will start with IPST as 4/15/2019 , the difference in days between 4/15/2019 and 4/1/2019 is 14 <60 thus I will change my IPST to 4/1/2019.

Continuing with this iteration IPST for 101 is 3/17/2019 and 102 is 3/18/2018 as shown on the right hand side table.

Check example calculation here

I tried to build a UDF as below, where I am passing id of a patient and UDF is returning the IPST.

CREATE FUNCTION [Initial_Prescription_Date] ( @id Uniqueidentifier

) RETURNS date AS BEGIN { I am failing to implement this code here
}


I can get a list of Start_dates for a patient from a medication table like this

Select id, start_date from patient_medication

I will have to iterate through this list to get to the IPST for a patient.

1 Answers

0
Jim Jimson On

I'll answer in order to start a dialog that we can work on.

The issue that I have is the the difference in days for ID = 102 between the last record and the one you've picked as the IPST is 29 days, but the IPST you've picked for 102 is 393 days, is that correct?

You don't need to loop to solve this problem. If you're comparing all of your dates only to your most recent, you can simply use MAX:

DECLARE @PatientRecords TABLE
(
ID INTEGER,
StartDate DATE,
Medicine VARCHAR(100)
)

INSERT INTO @PatientRecords VALUES
(101,'20181201','XYZ'),
(101,'20190115','XYZ'),
(101,'20190317','XYZ'),
(101,'20190401','XYZ'),
(101,'20190415','XYZ'),
(102,'20190401','XYZ'),
(102,'20190415','XYZ'),
(102,'20190315','XYZ'),
(102,'20180318','XYZ');

With maxCTE AS
(
SELECT *, DATEDIFF(DAY, StartDate, MAX(StartDate) OVER (PARTITION BY ID, MEDICINE ORDER BY StartDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) [IPSTDateDifference]
FROM @PatientRecords
)

SELECT m.ID, m.Medicine, MIN(m.StartDate) [IPST]
FROM maxCTE m
WHERE [IPSTDateDifference] < 60
GROUP BY m.ID, m.Medicine
ORDER BY 1,3;