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
SELECT DISTINCT
sessionid,
[TIME],
Pain_Scale,
Pain_Scale_Sign,
Pain_Scale_ET,
Offer_Pain_Medication,
Alert_Ob,
Pain_Relief_Adequate,
Alert_Anesthesiologist,
Instruct_Pt_Call_Nurse,
Refuse_Test,
Request_Pain_Medication
INTO #TMP_PDOC
FROM
dbo.IPR_Nursing_Pain_Management_Nurse_Pain_Documentation_Table
SELECT
BLSE.FacilityID,
BLSE.sessionID,
MODG.MRN,
(MODG.LastName +','+MODG.FirstName) AS PatientName,
PDOC_1.[TIME],
PDOC_1.Pain_Scale,
--PDOC_1.Pain_Scale_Sign,
--PDOC_1.Offer_Pain_Medication,
--PDOC_1.Alert_Ob,
--PDOC_1.Pain_Relief_Adequate,
--PDOC_1.Pain_Scale_ET,
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
FROM dbo.BLUser_names_Extended
WHERE (UserID = PDOC_1.Pain_Scale_Sign)) AS Nurs_Name_Pain_Scale
INTO #TMP_PNA
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]
INNER JOIN
dbo.MO_Demographics AS MODG ON PDOC_1.sessionid = MODG.SessionID
INNER JOIN
dbo.MO_Times AS MOT ON PDOC_1.sessionid = MOT.SessionID
INNER JOIN
dbo.MO_Dating AS MODAT ON MOT.SessionID = MODAT.SessionID
INNER JOIN
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
order by
MODG.MRN,
BLSE.sessionID,
PDOC_1.[TIME]
SELECT
FacilityID,
row_number() over (partition by sessionID order by sessionID desc) as ROWNUMBER,
sessionID,
MRN,
PatientName,
[TIME],
Pain_Scale,
Interventions,
PAIN_TYPE,
Nurs_Name_Pain_Scale
INTO #TMP_PNA2
FROM #TMP_PNA
ORDER BY
MRN,
ROWNUMBER,
[TIME];
WITH CTE as (
SELECT
RN = row_number() over (partition by sessionID order by sessionID desc),
*
FROM #TMP_PNA
)
SELECT DISTINCT
[Current Row].SESSIONID,
[Previous Row].[TIME] AS TIME1,
[Current Row].[TIME] AS TIME2,
[Next Row].[TIME] AS TIME3
INTO #CTE1
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
ORDER BY
SESSIONID,
[Previous Row].[TIME],
[Current Row].[TIME],
[Next Row].[TIME]
--SELECT * FROM #CTE1
SELECT
FacilityID,
ROWNUMBER,
P.sessionID,
MRN,
PatientName,
[TIME],
Pain_Scale,
Interventions,
PAIN_TYPE,
Nurs_Name_Pain_Scale,
C.TIME1,
C.TIME2,
C.TIME3
FROM #TMP_PNA2 P
INNER JOIN
#CTE1 C ON C.sessionID = P.SESSIONID
DROP TABLE #TMP_PDOC
DROP TABLE #TMP_PNA
DROP TABLE #TMP_PNA2
DROP TABLE #CTE1