Help getting a visit number

Transact-SQL
1

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????
FacilityID sessionId MRN PATIENT ChartStartTime DeliveryTime VISIT_ID
0 36957 123456 BETTY SUE 7/20/2015 NULL 1
0 37695 123456 BETTY SUE 8/29/2015 NULL 1
0 37824 123456 BETTY SUE 9/5/2015 9/5/2015 1
0 37916 123456 BETTY SUE 9/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 ('300002660890','100004902885','300002758666','100004489667','100004939095','100004215165')
--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

2

So you're looking at patient readmissions? I have no idea if this holds up to scrutiny, but seems to work with my quick testing (Using 30 day readmission)

   DECLARE @Patient TABLE
(
	FacilityID int,
	sessionId int,
	MRN int,
	PATIENT VARCHAR(50),
	ChartStartTime DATETIME,
	DeliveryTime DATETIME,
	VISIT_ID INT
);

INSERT INTO @Patient
VALUES	(0,36957,123456,'BETTY SUE','20150720',NULL,1),
		(0,37695,123456,'BETTY SUE','20150829',NULL,1),
		(0,37824,123456,'BETTY SUE','20150905','20150905',1),
		(0,37916,123456,'BETTY SUE','20150910',NULL,1),
		(0,37918,123456,'BETTY SUE','20150915',NULL,1),
		(0,37919,123456,'BETTY SUE','20150916','20150916',1), --Should event happen twice (in this case i doubt it!)
		(0,37919,123456,'BETTY SUE','20150917',NULL,1),
		(0,37919,123456,'BETTY SUE','20151115',NULL,1);

SELECT	P.*,
		VisitID = ROW_NUMBER() OVER (PARTITION BY P.MRN,COALESCE(A.DeliveryTime,P.DeliveryTime,P.ChartStartTime) ORDER BY P.ChartStartTime)
FROM	@Patient AS P
OUTER
APPLY	(
			SELECT	TOP 1 DeliveryTime
			FROM	@Patient AS B
			WHERE	P.MRN = B.MRN
					AND	P.ChartStartTime <= DATEADD(DD,30,B.DeliveryTime) --30 Days readmission
					AND B.DeliveryTime < P.ChartStartTime
					AND P.DeliveryTime IS NULL
			ORDER	BY B.DeliveryTime DESC
		) AS A;
3

thank you so much, but that didnt really work.

DECLARE @Patient TABLE
(
FacilityID int,
sessionId int,
MRN VARCHAR(255),
PATIENT VARCHAR(255),
ChartStartTime DATETIME,
DeliveryTime DATETIME,
VISIT_ID INT
);

INSERT INTO @Patient
SELECT
S.FacilityID,
S.sessionId,
S.MRN,
S.LastName + ', ' + S.firstname AS PATIENT,
MT.ChartStartTime,
MT.DeliveryTime,
ROW_NUMBER() OVER (PARTITION BY S.mrn ORDER BY S.sessionid) 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 MT.ChartStartTime between '01/01/2015' and '10/01/2015'
and S.FacilityID=0
ORDER BY S.MRN,S.SESSIONID
SELECT P.*,
VisitID = ROW_NUMBER() OVER (PARTITION BY P.MRN,COALESCE(A.DeliveryTime,P.DeliveryTime,P.ChartStartTime) ORDER BY P.ChartStartTime)
FROM @Patient AS P
OUTER
APPLY (
SELECT TOP 1 DeliveryTime
FROM @Patient AS B
WHERE P.MRN = B.MRN
AND P.ChartStartTime >= DATEADD(D,45,B.DeliveryTime) --30 Days readmission
AND B.DeliveryTime < P.ChartStartTime
AND P.DeliveryTime IS NULL
ORDER BY B.DeliveryTime DESC
) AS A;

the number of days remission is not working correctly. i am picking up patients that delivered 2 years prior where i should only be picking up patients that delivered 45 days prior or whatever number is entered.