'SQL / stored procedure - detect when changes happen within a list of specified values
I need to create a report that returns customer accounts whose account type have changed to or from this list of account_types:
('CAM', 'CAMADP', 'CAMHD', 'CAMHDADP', 'DIST', 'DISTADP', 'GAM', 'GAMADP', 'LAM', 'LAMADP')
or accounts that have been deactivated.
Let's say I have this table today:
| client | account_type | customer_org_ID | insert_date | update_date | active_Y_N |
|---|---|---|---|---|---|
| GAM | C000001 | 2006-05-04 09:15:34 | 2020-05-04 06:01:16 | Y | |
| Netflix | LAM | C000002 | 2006-05-04 09:15:34 | 2021-09-04 08:35:34 | Y |
| Nordstrom | DIST | C000003 | 2006-05-04 09:15:34 | 2021-05-04 02:15:34 | Y |
| Costco | CAMHDADP | C000004 | 2006-05-04 09:15:34 | 2021-05-04 09:36:34 | Y |
| Boeing | CAMHD | C000005 | 2006-05-04 09:15:34 | 2021-05-04 12:15:45 | Y |
| Samsung | DISTADP | C000006 | 2006-05-04 09:15:34 | 2020-06-15 01:10:16 | Y |
Let's say I come into work tomorrow and there have been changes to rows 1, 2, 3, 5, and 6 (seen in bold).
| client | account_type | customer_org_ID | insert_date | update_date | active_Y_N |
|---|---|---|---|---|---|
| CAM | C000001 | 2006-05-04 09:15:34 | 2021-11-03 12:40:41 | Y | |
| Netflix | DISTADP | C000002 | 2006-05-04 09:15:34 | 2021-11-03 12:40:41 | Y |
| Nordstrom | GAMADP | C000003 | 2006-05-04 09:15:34 | 2021-11-03 12:40:41 | Y |
| Costco | CAMHDADP | C000004 | 2006-05-04 09:15:34 | 2021-05-04 09:36:34 | Y |
| Boeing | UNKWN | C000005 | 2006-05-04 09:15:34 | 2021-11-03 12:40:41 | Y |
| Samsung | DISTADP | C000006 | 2006-05-04 09:15:34 | 2021-11-03 12:40:41 | N |
I need a SQL query or a stored procedure that will return these four rows of data since there were updates made to their account_type within the list of account_types that I mentioned in the beginning. Or the account has been deactivated.
| client | account_type | customer_org_ID | insert_date | update_date | active_Y_N |
|---|---|---|---|---|---|
| CAM | C000001 | 2006-05-04 09:15:34 | 2021-11-03 12:40:41 | Y | |
| Netflix | DISTADP | C000002 | 2006-05-04 09:15:34 | 2021-11-03 12:40:41 | Y |
| Nordstrom | GAMADP | C000003 | 2006-05-04 09:15:34 | 2021-11-03 12:40:41 | Y |
| Samsung | DISTADP | C000006 | 2006-05-04 09:15:34 | 2021-11-03 12:40:41 | N |
I am working in DB2 LUW database. Unfortunately I cannot add any new columns to the database I'm querying the data from.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|