November 21, 2016 at 6:18 am
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
November 21, 2016 at 6:22 am
You've been here long enough to know what is required: sample DDL, INSERT statements containing sample data and desired results based on your sample data, please.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 21, 2016 at 6:33 am
I provided sample data located under
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 am sorry if you are unable to provide a reply based upon what I sent you.
I hope other users are able to assist based upon what I provided.
November 21, 2016 at 6:56 am
Here is the forum etiquette link: http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
Notice how striking a resemblance your test data bears to that shown in the 'The Wrong Way to Post Data' section.
If you want a coded solution, please have the courtesy to provide coded sample data, rather than expecting others to do it for you.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 21, 2016 at 7:40 am
Did you try LEAD()?
November 21, 2016 at 8:54 am
There are three issues that I see immediately.
1) In your CTE, you do not provide an alias for the ROW_NUMBER() expression.
2) You should be using LEAD/LAG instead of ROW_NUMBER() with a self-join.
3) You're OVER clause is non-deterministic. Your PARTITION is exactly the same as your ORDER BY clause. By definition, all records within a partition have the same value, ordering by that value causes them all to TIE, and you have no tie-breaker.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 21, 2016 at 11:10 am
THIS WORKED LIKE A CHARM !!
CREATE TABLE #1(
ID1int identity(1,1),
sessionidvarchar(10),
[time]datetime)
INSERT INTO #1(sessionid, [time])
SELECT DISTINCT
sessionid, [time]
FROM #tmp_pna
ORDER BY sessionid, [time]
--select * from #1
-----------------------------------------------------------
--------------- GEN NEXT/PREV -----------------------------
SELECT DISTINCT
T.sessionid,
T.[time],
(SELECT MIN([time]) FROM #1 S WHERE S.sessionid = T.sessionid AND S.ID1 > T.ID1 ) NextTime
INTO #2
FROM #1 T
ORDER BY T.sessionid, T.[time]
--select * from #2
SELECT Distinct
T.sessionid,
--S.[time] PreviousTime,
T.[time] CurrentTime,
t.NextTime,
DATEDIFF(n,T.[time],t.NextTime) timedif
INTO #TMP_TIME
FROM #2 T LEFT JOIN #2 S ON T.sessionid = S.sessionid AND T.[time] = S.[time]
order by
T.sessionid,
T.[time]
I then joined it to the main query.
November 21, 2016 at 12:31 pm
sharonmtowler (11/21/2016)
THIS WORKED LIKE A CHARM !!
It may have "worked" but it is likely to perform horribly. You should compare it to LEAD/LAG before settling on that as the solution.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 21, 2016 at 12:46 pm
drew.allen (11/21/2016)
sharonmtowler (11/21/2016)
THIS WORKED LIKE A CHARM !!It may have "worked" but it is likely to perform horribly. You should compare it to LEAD/LAG before settling on that as the solution.
Drew
And here's the reason behind the bad performance:
November 21, 2016 at 1:18 pm
sharonmtowler (11/21/2016)
I provided sample data located underthe 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 am sorry if you are unable to provide a reply based upon what I sent you.
I hope other users are able to assist based upon what I provided.
ok...so based on this data...what are your expected results please?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 21, 2016 at 6:02 pm
Self-joins with aggregates. YUCK.
How about something really simple... maybe like this?
SELECT PatientID
, SessionID
, SessionTime
, LAG([SessionTime]) OVER (PARTITION BY PatientID ORDER BY SessionTime) AS PrevSession
FROM
(SELECT 1 AS PatientID, 1 AS SessionID, '11/16/16 5:01 AM' AS SessionTime
UNION ALL SELECT 2, 1, '11/16/16 5:19 AM'
UNION ALL SELECT 3, 1, '11/16/16 12:10 PM'
UNION ALL SELECT 1, 2, '11/17/16 1:04 PM'
UNION ALL SELECT 2, 2, '11/17/16 8:20 PM'
UNION ALL SELECT 1, 3, '11/19/16 4:08 PM'
UNION ALL SELECT 2, 3, '11/19/16 4:20 PM'
UNION ALL SELECT 3, 3, '11/19/16 4:30 PM'
UNION ALL SELECT 4, 3, '11/19/16 11:00 PM') x
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply