I have a table TLA_1 that during the FIRST round, it looks like this:
During the second round there were some changes, and for '222' there is another added status for RETURNED. And '111' has removed the RETURNED status. Like this:
The order always goes as follow: BOUGHT then CLAIMED then RETURNED. You will always have a bought, and then it can be followed by a CLAIMED or a CLAIMD AND RETURNED. So possible orders for each transaction are:
- BOUGHT
- BOUGHT & CLAIMED
- BOUGHT & CLAIMED & RETURNED
- BOUGHT & RETURNED
I do not want to update the table if the LATEST status record in the FIRST round is GREATER than the change coming from the second round.
Thus, update only happens when:
IF table only contains BOUGHT, and later only CLAIMED or (CLAIMED AND RETURNED)are added, UPDATE table accordingly.
IF table contains BOUGHT and CLAIMED, and RETURNED is added, UPDATE table accordingly.
IF table contains all 3 status' and later RETURNED is removed or (returned and claimed), do not update table.
IF table contains BOUGHT AND CLAIMED (no returned), and later only claimed is removed do not update the table.
This is what I have so far:
TBL_1:
DROP TABLE TBL_1;
CREATE TABLE TBL_1 (
PERSON_IDENFITICATION VARCHAR(100) NOT NULL,
TRANSACTION_IDENTIFICATION VARCHAR(100) NOT NULL,
STATUS VARCHAR(100) NOT NULL,
STATUS_DATETIME datetime NOT NULL,
EXPIRATION_DATETIME datetime NOT NULL
);
TBL_EXPANDED_COLUMNS:
DROP TABLE TBL_EXPANDED_COLUMNS;
CREATE TABLE TBL_EXPANDED_COLUMNS;(
PERSON_IDENFITICATION VARCHAR(100) NOT NULL,
TRANSACTION_IDENTIFICATION VARCHAR(100) NOT NULL,
LAST_STATUS VARCHAR(100) NOT NULL,
BOUGHT_DATETIME DATETIME NOT NULL,
CLAIMED_DATETIME DATETIME NOT NULL,
RETURNED_DATETIME DATETIME NOT NULL,
EXPIRATION_DATETIME DATETIME NOT NULL
);
MERGE INTO TBL_EXPANDED_COLUMNS B
USING (
SELECT
PERSON_IDENFITICATION,
TRANSACTION_IDENTIFICATION
,(array_agg(STATUS) within group(order by STATUS_DATETIME desc)[0])::varchar as LAST_STATUS
,coalesce(max(case when STATUS = 'BOUGHT' THEN STATUS_DATETIME END), max(case when STATUS = 'CLAIMED' THEN STATUS_DATETIME END), max(case when STATUS = 'RETURNED' THEN STATUS_DATETIME END), '1900-01-01'::datetime) as BOUGHT_DATETIME
,coalesce(max(case when STATUS = 'CLAIMED' THEN STATUS_DATETIME END),'1900-01-01'::datetime) as CLAIMED_DATETIME
,coalesce(max(case when STATUS = 'RETURNED' THEN STATUS_DATETIME END),'1900-01-01'::datetime) as RETURNED_DATETIME
,EXPIRATION_DATETIME
FROM TBL_1
GROUP BY 1,2,7
) A ON (A.PERSON_IDENTIFICATION = B.PERSON_IDENTIFICATION
and A.TRANSACTION_IDENTIFICATION = B.TRANSACTION_IDENTIFICATION)
WHEN MATCHED THEN
Is someone able to help me rewrite the query above to only update the table when the certain conditions are met?

