SQL to compare records (status change) in table

Database
Enthusiast

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 (2)
2 REPLIES
Enthusiast

Re: SQL to compare records (status change) in table

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

Enthusiast

Re: SQL to compare records (status change) in table

Thank you for answering your own question! It saved me having to look elsewhere for the answer or post a new Q on here!

 

The logic works perfectly:

 

max(c_sta_clm) over (partition by c_clm order by row_begin_dt rows between 1 preceding and 1 preceding)
-- Jonny