Hi,
I am attempting to calculate Volume of an Incident Queue at a Point in Time.
I am planning on using the sum function..eventually.
My current problem:
I have one column which captures bed transfer times (cvl.TransferRequestDtm).
Essentially a person is in the same bed for all the dates/time between one TransferRequestDtm until another TransferRequestDtm.
I want to add a new column (called 'EndLocation") which is equal to the very next instance of TransferRequestDtm for that particular visit. (cv.VisitIDCode).
I partitioned based on visit (column ' r ') with the result: 1 in the ' r ' column is the first transfer, '2' in the 'r' column is the second (end should be 'EndLocation' for the first instance of cvl.TransferRequestDtm), etc.
Any help is appreciated. My first post.
SELECT distinct c.IDCode AS CorpID
, cv.IDCode as MRN
, cv.VisitIDCode AS AccountNumber
, c.LastName
, c.FirstName
, str(C.BirthMonthNum,2) + '/' + str(C.BirthDayNum,2) + '/' + str(C.BirthYearNum,4) as DOB
, CONVERT(VARCHAR(10),CV.AdmitDtm,101) AS AdmitDate
, cvl.TransferRequestDtm
, row_number()over(Partition By c.IDCode, cv.VisitIDCode ORDER BY cvl.TransferRequestDtm asc) as r
, cv.CareLevelCode
, cv.DischargeDtm
, cvl.ClientVisitGUID
, l.Code
, cv.TypeCode
FROM cv3clientvisit CV (nolock)
INNER JOIN CV3Client C (nolock) ON C.GUID = CV.ClientGUID
INNER JOIN CV3ClientVisitLocation CVL (NOLOCK) ON CVL.ClientVisitGUID = CV.GUID
INNER JOIN CV3Location L (NOLOCK) ON L.GUID = CVL.LocationGUID
where cvl.TransferRequestDtm >= '06/01/2016'
and ((cv. TypeCode = 'inpatient')
or (cv. TypeCode = 'emergency'))
and l.code not like ('%temp%')
order by c.IDCode, cv.VisitIDCode, cvl.TransferRequestDtm]](http://help)