CTE Issue - Duration Times Changing

Due to the 50000 character limitation; I'll post the remaining part of this issue in a reply to this original message.

With the help of forum members, I was able to put together the code below to display the duration times each patient spend in each care unit. If I un-remark the where clause for a single account number, then the result set (see result set #1) is correct. However, when I run it remarked out to get all patients, then the duration times change (see result set #2). The duration times for patient Jane Doe changes; can anyone see why this might be occurring?




Code Block
;WITH CTE AS (
SELECT
CONVERT(NVARCHAR,DATE_IN, 112) as DATE_IN,
ACCOUNT_NUMBER,
PATIENT_NAME,
CAREUNIT_KEY,
CARE_UNIT,
CARE_UNIT_DESC,
DATE_TIME_IN,
EPISODE_KEY,
TIME_IN,
ADMIT_DATE,
DISCH_DATE,
MEDREC_NO,
SERVICECODE,
PROVIDER_CODE,
PROVIDER_ROLE,
PHYSNAME
ROW_NUMBER() OVER (ORDER BY DATE_TIME_IN) AS seq
FROM reports.CAREUNITS_INFO
--where account_number IN ('777777')
)
SELECT
a.DATE_IN,
a.ACCOUNT_NUMBER,
a.PATIENT_NAME,
a.CAREUNIT_KEY,
a.CARE_UNIT,
a.CARE_UNIT_DESC,
a.DATE_TIME_IN,
a.TIME_IN,
DATEDIFF(second, a.DATE_TIME_IN, b.DATE_TIME_IN) / 3600 as Duration_Hours,
(DATEDIFF(second, a.DATE_TIME_IN, b.DATE_TIME_IN) % 3600 / 60) AS Duration_Minutes,
a.ADMIT_DATE,
a.DISCH_DATE,
a.MEDREC_NO,
a.SERVICECODE,
a.PROVIDER_CODE,
a.PROVIDER_ROLE,
a.PHYSNAME
FROM cte a
INNER JOIN cte b ON a.Seq + 1 = b.Seq
order by a.DATE_IN, a.ACCOUNT_NUMBER




Please see my reply for the remainder of this message...