October 1, 2015 at 7:28 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 4, 2015 at 8:21 pm
Hi
One of the possible solutions could be
--create some test data
USE tempdb
GO
CREATE TABLE test1 (sessionId int, MRN int, ChartStartTime date ,DeliveryTime date)
GO
INSERT INTO dbo.test1(sessionId,MRN,ChartStartTime,DeliveryTime)
SELECT 36957,123456,'7/20/2015',NULL
UNION ALL
SELECT 37695,123456,'8/29/2015',NULL
UNION ALL
SELECT 37824,123456,'9/5/2015','9/5/2015'
UNION ALL
SELECT 37916,123456,'9/10/2015',NULL
UNION ALL
SELECT 37917,123455,'7/20/2015',NULL
UNION ALL
SELECT 37918,123455,'8/29/2015',NULL
UNION ALL
SELECT 37919,123455,'9/5/2015',NULL
UNION ALL
SELECT 37920,123455,'9/10/2015',NULL
--
;WITH GetDeliveryTime AS
(
SELECT MAX(t1.DeliveryTime) DelTime
,t1.MRN
FROM test1 t1
GROUP BY t1.MRN
)
SELECT t.sessionId
,t.MRN
,t.ChartStartTime
,t.DeliveryTime
,SUM(CASE
WHEN t.ChartStartTime >= dt.DelTime THEN 1
ELSE 0
END) OVER (PARTITION by t.MRN ORDER BY (SELECT NULL)) AS [No_of_visits]
--,COUNT(*) OVER (PARTITION BY t.MRN ORDER BY (SELECT NULL)) AS [Total_No_of_visits]
FROM dbo.test1 t
LEFT OUTER JOIN GetDeliveryTime dt
ON dt.MRN = t.MRN
D.Mincic
😀
MCTS Sql Server 2008, Database Development
October 5, 2015 at 3:09 am
Hi Sharon
To help us to help you, can you please set up a data script? You will want to include a minimum of two patients and a minimum of three visits each. Include the data you have already published.
Your script should consist of a CREATE TABLE statement and an INSERT to populate the table. Be sure to test it before posting.
I’m sure that what you are asking for is fairly straightforward, but without sample data we’d be pinning the tail on the donkey.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply