I have a sp that I generates records per patient. The patient could have multiple records or just one. The data below shows 3 patients, the sessionid is the unique chart id per patient.
What I am trying to achieve is to create a field called next time. I would like to calculate the difference between the first time, which is row number 1 to the next time which can be row number 2 and so on, depending on the number of rows the patient has.
the data looks like this
row number----- session----- time
1 ----- 1----- 11/16/16 5:01 AM
2 ----- 1----- 11/16/16 5:19 AM
3 ----- 1----- 11/16/16 12:10 PM
1 ----- 2----- 11/17/16 1:04 PM
2 ----- 2----- 11/17/16 8:20 PM
1 ----- 3----- 11/19/16 4:08 PM
2 ----- 3 -----11/19/16 4:20 PM
3 ----- 3----- 11/19/16 4:30 PM
4 ----- 3----- 11/19/16 11:00 PM
I have added CTE to my sp, but I don't believe I am using it correctly. I was also hoping there was an easier way to do this.
stored proc below
(MODG.LastName +','+MODG.FirstName) AS PatientName,
CASE WHEN PDOC_1.Request_Pain_Medication = 'TRUE' THEN 'Patient Requests Pain Medication'
WHEN PDOC_1.Offer_Pain_Medication = 'TRUE' THEN 'Pain Medication Offered'
WHEN PDOC_1.Refuse_Test = 'TRUE' THEN 'Patient Refused Pain Medication at this Time'
WHEN PDOC_1.Instruct_Pt_Call_Nurse = 'TRUE' THEN 'Instructed to Inform RN if Pain Persists'
WHEN PDOC_1.Pain_Relief_Adequate = 'TRUE' THEN 'Pain Relief Adequate'
WHEN PDOC_1.Alert_Anesthesiologist = 'TRUE' THEN 'Anesthesiologist Notified by Provider'
WHEN PDOC_1.Alert_Ob = 'TRUE' THEN 'Provider Notified'
WHEN PMGT.Pt_Request_PO_Medication_Meds_Given_1_0 = 'TRUE' THEN 'PO Medication'
WHEN PMGT.Injection_IM_SC_Meds_Given_1_1 = 'TRUE' THEN 'SubQ Injection'
WHEN PMGT.Pt_Request_IM_Medication_Meds_Given_1_2 = 'TRUE' THEN 'IM Medication'
WHEN PMGT.Pt_Request_IV_Medication = 'TRUE' THEN 'IV Medication'
WHEN PMGT.Pt_Request_Epidural = 'TRUE' THEN 'Epidural'
WHEN PMGT.Pt_Request_No_Anesthesia = 'TRUE' THEN 'No Pharmacological Intervention'
WHEN PMGT.Pt_Request_Undecided = 'TRUE' THEN 'Undecided'
WHEN PMGT.Pt_Request_Other = 'TRUE' THEN 'Other'
END AS Interventions,
CASE WHEN PDOC_1.[TIME] <= MOT.DeliveryTime THEN 'A' WHEN PDOC_1.[TIME] > MOT.DeliveryTime THEN 'P' END AS PAIN_TYPE,
(SELECT TOP (1) Surname + ', ' + FirstName AS Expr1
WHERE (UserID = PDOC_1.Pain_Scale_Sign)) AS Nurs_Name_Pain_Scale
FROM #TMP_PDOC AS PDOC_1
LEFT OUTER JOIN
dbo.IPR_Pain_Management_Plan_Timed_Nurse_Pain_Documentation_Table AS PMGT
ON PDOC_1.sessionid = PMGT.sessionid AND PDOC_1.[TIME] = PMGT.[TIME]
dbo.MO_Demographics AS MODG ON PDOC_1.sessionid = MODG.SessionID
dbo.MO_Times AS MOT ON PDOC_1.sessionid = MOT.SessionID
dbo.MO_Dating AS MODAT ON MOT.SessionID = MODAT.SessionID
dbo.BLSession_Extended AS BLSE ON MODG.SessionID = BLSE.sessionID
where GAWeeksAtAdmission between 26 and 42
AND MOT.RegistrationAdmissionTime >= GetDate() - 8 AND CAST(MOT.RegistrationAdmissionTime AS DATE) <= GetDate()-1
and BLSE.FacilityID = 0 and PDOC_1.Pain_Scale>=5
and PDOC_1.[TIME] <= MOT.DeliveryTime
row_number() over (partition by sessionID order by sessionID desc) as ROWNUMBER,
WITH CTE as (
RN = row_number() over (partition by sessionID order by sessionID desc),
[Previous Row].[TIME] AS TIME1,
[Current Row].[TIME] AS TIME2,
[Next Row].[TIME] AS TIME3
FROM CTE [Current Row]
LEFT JOIN CTE [Previous Row] ON
[Previous Row].RN = [Current Row].RN - 1
LEFT JOIN CTE [Next Row] ON
[Next Row].RN = [Current Row].RN + 1
--SELECT * FROM #CTE1
FROM #TMP_PNA2 P
#CTE1 C ON C.sessionID = P.SESSIONID
DROP TABLE #TMP_PDOC
DROP TABLE #TMP_PNA
DROP TABLE #TMP_PNA2
DROP TABLE #CTE1