Showing the latest record with indicator 'C' and old with 'H' after the loading the table

Database
Enthusiast

Showing the latest record with indicator 'C' and old with 'H' after the loading the table

Hello to all,
I am new to this and need a little help in the below case as they need to show the proper indicators, when the records are loaded in the table as per the query some of the records are still showing the indicator 'C' insted of 'H' when they are updated, and we need all the records to show when user access it, but with the proper indicator:

Table ABC is populated wrong.

# SSN USERID SYSDATE TIME IND
- -------- ------ ------ ---- ---
. xxxxxxxxx . 04/05/03 1100 H
. xxxxxxxxx 07 06/01/05 1007 H
. xxxxxxxxx 01 06/01/05 1422 H
. xxxxxxxxx 07 06/16/05 1354 H
. xxxxxxxxx 07 06/16/05 1355 C
. xxxxxxxxx 07 08/16/05 1215 H
ABC xxxxxxxxx 07 10/19/05 0958 H
ABC xxxxxxxxx 07 10/19/05 0958 C
ABC xxxxxxxxx 07 12/12/05 1344 C
XYZ xxxxxxxxx 26 06/02/06 1757 H
XYZ xxxxxxxxx 01 11/02/06 1001 C
XYZ xxxxxxxxx 01 11/02/06 1002 C
ABC xxxxxxxxx 26 06/02/06 1757 H
XYZ xxxxxxxxx 01 10/02/06 1001 C
XYZ xxxxxxxxx 01 12/02/06 1003 C

In Last column Note that the last two Rows-- both have current history indicator of "C".
current indicator in table 'ABC' is not accurate.
We should only have one current indicator per craft(one for ABC and one for XYZ under each SS#)
not multiple current indicators.
Create a view which will show two current history indicator
for each ss# as 'C'for latest data (one for ABC and one for XYZ under each SS#) and all other as 'H' for old data .

I really appreciate your help and thanks in advance
Thanks & Regards
P
2 REPLIES
Enthusiast

Re: Showing the latest record with indicator 'C' and old with 'H' after the loading the table

CREATE VIEW ABC_VIEW AS
SELECT "#"
,SSN
,USERID
,SYSDATE
,"TIME"
,CASE WHEN SUM(1) OVER (PARTITION BY "#", SSN
ORDER BY SYSDATE DESC, "TIME" DESC
ROWS UNBOUNDED PRECEDING) = 1
THEN 'C'
ELSE 'H'
END AS IND
FROM ABC;

Note that when this view is used, the entire view will be materialized before any constraints will be applied, so hopefully, this is not a large table that will be accessed frequently through this view. If it is, it would be better to do this correction into a new table.
Enthusiast

Re: Showing the latest record with indicator 'C' and old with 'H' after the loading the table

Thank you Barry very much