All Forums Database
gfuller40 24 posts Joined 05/10
04 Dec 2012
SQL to compare records (status change) in table

 
I have a table that contains history of a claim. Basically I'm looking at status changes and dates. Whenever someone updates a claim, the new row is loaded into the table I'm showing below. What I'm trying to obtain is all of the status changes for the column "c_sta_clm" I want to be able to capture the date "row_begin_dt" and both status change (PC to AC) & (AC to TE).
Any guidance on how to make this simple is hugely appreciated. I was thinking of making two volatile tables and joining on C_CLM, taking min status dates and comparing etc...
 
for this specific data sample I would like to return the status change from PC to AC and AC to TE and the date these changes occured.
 
 

row_begin_dt                user                    c_clm          c_sta_clm
2009-10-08  ?       C5S2M                         09050012            PC
2009-10-24  ?       C5S2M                         09050012            AC
2009-10-28  ?       C1CMH                         09050012            AC
2010-10-30  ?       C1CMH                         09050012            AC
2011-05-19  ?       A9709                         09050012            AC
2011-06-09  ?       C6JEC                         09050012            AC
2011-10-07  ?       DAJ07                         09050012            TE
2011-11-04  ?       DAJ07                         0905001             TE
Tags:
gfuller40 24 posts Joined 05/10
05 Dec 2012

I figured it out.
 

select
    row_begin_dt,
    c_ams_clm,
    c_clm,
    c_sta_clm,
    -- Find the c_sta_clm of the previous row
    max(c_sta_clm) over (partition by c_clm order by row_begin_dt rows between 1 preceding and 1 preceding) as prev_c_sta_clm
from pearl_p.TLTC900_CLM_PRSST
-- Include only records which have a c_sta_clm different to that of the previous row
qualify c_sta_clm <> prev_c_sta_clm
You must sign in to leave a comment.