October 1, 2015 at 7:26 am
I need to create a query where patients have delivered and may have returned within a certain number of days for another admittance.
each visit a new chart(sessionid) will be created.
I thought I could create a visit id number which would count the number of visits per patient.
The first visit has to be the day of delivery where delivery date is not null.
I could create a
CASE WHEN MT.DeliveryTime IS NOT NULL THEN 1
WHEN MT.ChartStartTime > MT.DeliveryTime THEN 2 END AS VISITID, but that did not work they way i envisioned.
how would i get the next visit date as visit 2????
**FacilityIDsessionIdMRN PATIENT ChartStartTimeDeliveryTimeVISIT_ID**
0 36957123456BETTY SUE7/20/2015 NULL 1
0 37695123456BETTY SUE8/29/2015 NULL 1
0 37824123456BETTY SUE9/5/2015 9/5/2015 1
0 37916123456BETTY SUE9/10/2015 NULL 1
I used the following but it is not working.
ROW_NUMBER() OVER (PARTITION BY S.SESSIONID ORDER BY S.MRN) AS VISIT_ID
I put the following query together but im having a tough time with this.
SELECT
S.FacilityID,
S.sessionId,
S.MRN,
S.LastName + ', ' + S.firstname AS PATIENT,
DATEDIFF(D,MT.DeliveryTime,MT.DISCHARGETIME)AS LENGTH_OF_STAY,
isnull(dbo.EFgetFindingValue(30741,default,S.Sessionid),999) AS GESTATIONAL_AGE,
CASE WHEN DT.VaginalDelivery = 'YES' THEN 'VAGINAL'
WHEN DT.CesareanDelivery = 'YES' THEN 'CESAREAN' END AS DELIVERY_TYPE,
MT.ChartStartTime,
MT.DeliveryTime,
ROW_NUMBER() OVER (PARTITION BY S.SESSIONID ORDER BY S.MRN) AS VISIT_ID
FROM
dbo.BLSession_Extended AS S
LEFT OUTER JOIN
dbo.MO_DeliveryTypePatient_table AS DT ON S.sessionID = DT.SessionID
INNER JOIN
dbo.MO_Times MT ON S.sessionId = MT.SessionID
WHERE --(F.ObjectName = 'Time_Of_Delivery!Delivery_Report_Neonate[#]') AND (F.ValueStr = 'true')
MT.ChartStartTime between '07/01/2015' and '09/30/2015' and S.FacilityID=0
--S.MRN IN (')
--AND s.MRN in
-- (SELECT s_PrevSession.MRN
-- FROM
-- BLSession_Extended s_PrevSession
-- INNER JOIN MO_Times t_PrevSession ON s_PrevSession.sessionID = t_PrevSession.SessionID
-- WHERE
-- s_PrevSession.SessionID <> s.SessionID
-- AND t_PrevSession.DeliveryTime IS NOT NULL
-- AND s.Open_Time between t_PrevSession.DischargeTime and DateAdd(day,100,t_PrevSession.DischargeTime))
ORDER BY S.MRN,MT.CHARTSTARTTIME
October 2, 2015 at 3:52 am
Hi Sharon,
unless you post the CREATE TABLE statements + INSERT INTO with sample data, it is just groping in the dark....
However, your PARTITION seems to be incorrect if I understand the example correctly.
IMHO it should be something like :
ROW_NUMBER() OVER (PARTITION BY S.MRN ORDER BY S.SESSIONID) AS VISIT_ID
You want to split your result into parts where each part contains all visits of a certain patient... so PARTITION BY should contain identification of the patient... and then you want to number each patient's visits from 1, so you need to order by either sessionID or ChartStartTime (maybe.. not sure how the data look in reality).
Did it help a bit? If not, please, take the time to post table structure and sample data - then someone will be able to help you.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply