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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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://qa.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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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(
ID1 int identity(1,1),
sessionid varchar(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