SQLTeam.com | Weblogs | Forums

Splitting a single date column into start and end dates


#1

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)


#2

You should avoid using nolock as this allows for dirty reading.

Please provide:

  • table descriptions as create statements
  • sample data as insert statements
  • expected output from the sample data you provide