Updating a row to implement error trapping

Transact-SQL
1

Please any ideas on how I can achieve the result shown in the attached picture .For now I have all the rows of the error field initiated with a default value of '0' and update the field based on different conditions but for the result .Its a bit challenging since its based on comparing 2 rows. **The logic that allows episodes to be condensed into a single continuous care episode is a discharge code of 22 followed by an admission code of 4 on the same day. So if a discharged code of 22 is followed by a different code other than an admission code of 4 for the same day that is an invalid entry.

			   drop table #source
				 CREATE TABLE #source(patidid varchar(20),epn int,preadmitdate datetime,adminttime varchar(10),
			 admitcode varchar(10),admittype varchar(25),datedischarge datetime,disctime varchar(10),disccode varchar(10),dischargetype varchar(20),errorcode int)
			INSERT INTO #source VALUES
			 (1849,1,'4/23/2020','7:29',1,'First Admission','7/31/2020','9:03',22,'Campus Transfer',0)
			,(1849,2,'7/31/2020','11:00',4,'Campus Transfer','7/31/2020','12:09',22,'Campus Transfer',0)
			,(1849,3,'7/31/2020','13:10',2,'Re-Admission',null,null,null,null,0)
			select * from #source

error_trapping
error_trapping1221×194 7.67 KB

2

Try a CTE

;WITH endings AS
    (SELECT patidid, epn, Cast(preadmitdate + adminttime as smalldatetime) as AdmitDate, admitcode, disccode,
        ROW_NUMBER() OVER (PARTITION BY patidid ORDER BY epn) AS sort
      FROM #source ),
ranges AS
    (SELECT e1.sort, e1.patidid, e1.AdmitDate, e1.admitcode, e1.disccode, e2.disccode AS Priordisccode
      FROM endings e1
        LEFT OUTER JOIN endings e2 ON e1.patidid = e2.patidid AND e1.sort = e2.sort + 1)


Select  * from ranges
where Priordisccode = 22
and (AdmitCode is null or AdmitCode <> 4)
3

@mike01
It worked .Is there away to select the previous row with the Discharge Code=22? , because that might be the error record rather than the next record. Is it possible to catch both conditions at a go or it has to be separate conditions for both cases?. Taking into account that both records are correct if and only if Discharge Code=22 is followed by admit code=4 Thanks.