Audit table field value change
From the below table example, I'm trying to identify only the records where VAL_IN changed. So I need to see only 4 records in the below example.
I've been reading about LEAD / LAG and think this is probably the function I need to use, but have not had luck with returning results. Can someone please help push me in the right direction.
ID,LAD_DT,VAL_IN
14097598330,01/01/2011 12:09:14 AM,0
14097598332,10/17/2014 9:02:06 PM,0
14097598332,01/01/2011 1:26:27 AM,0
14097598332,07/21/2011 9:00:46 PM,1
14097598332,08/30/2012 5:25:06 PM,0
14097598332,09/04/2012 9:17:48 PM,0
14097598332,03/18/2014 9:01:09 PM,0
14097598332,03/31/2014 7:35:40 PM,0
14097598332,04/01/2014 9:58:25 PM,1
14097598332,04/30/2014 5:04:20 PM,0
14097598332,05/05/2014 8:01:10 PM,0
14097598332,11/07/2014 11:56:06 AM,0
14097598332,05/01/2018 2:01:12 AM,0
14097598332,06/27/2019 10:37:15 PM,0
SELECT *
FROM (
SELECT ID, LAD_TS, VAL_IN,
LAG(VAL_IN) OVER (ORDER BY LAD_TS) AS VAL_1
FROM DVV.TDAD_AUDIT)
WHERE VAL_IN <> VAL_1
AND ID IN '14097598330'
- MIKEISMAD's blog
- Log in to post comments