How to detect value changes according to other columns in Oracle
Below table contains non unique identifier, currency value and dates / times.
id_1 value_1 value_time id_version Version_time
138 250 09-SEP-14 595 02-SEP-14
140 250 15-SEP-14 695 01-AUG-14
140 300 30-DEC-14 720 05-NOV-14
233 250 01-JUN-15 800 16-MAY-15
As you can see id_1, the id_version and time columns can change in the table, but value_1 can remain the same.
I know that if id_1 is the same in rows, value_1 can only change according to id_version. But there are too many id_version in the table. And I know it changes according to id_version, but I don't know the exact time when it changed.
So firstly, I have to decide which id_version and id_version time causes the group to change the value by id_1.
But again id_1 is not uniqe, and id can change, but the value remains the same :)
editor: From OP comment - Get Started
Here is the desired result. I want the first and second lines to be not the third but the fourth.
| 140 | 250 | 15-SEP-14 | 695 | 01-AUG-14 |
| 140 | 300 | 31-DEC-14 | 725 | 07-NOV-14 |
| 140 | 300 | 05-JAN-14 | 740 | 30-NOV-14 |
| 140 | 300 | 30-DEC-14 | 720 | 05-NOV-14 |
editor: From OP comment - End
Thanks in advance for helping me with this situation.
source to share
Based on the input you entered so far (and processing only the image-bound data, not the data in the current sample data), the following should get you started:
SELECT
TMin.id_1
, TMin.value_1
, TO_CHAR(TAll.value_time, 'DD-MON-RR') value_time
, TMin.id_version
, TO_CHAR(TMin.version_time, 'DD-MON-RR') version_time
FROM
(SELECT
id_1
, value_1
, MIN(id_version) id_version
, MIN(version_time) version_time
FROM T
GROUP BY id_1, value_1
ORDER BY id_1, value_1
) TMin
JOIN T TAll
ON TMin.id_1 = TAll.id_1
AND TMin.value_1 = TAll.value_1
AND TMin.id_version = TAll.id_version
AND TMin.version_time = TAll.version_time
ORDER BY TMin.id_1, TMin.value_1
;
See it in action: SQL Fiddle .
Please comment if and how this requires adjustment / further detail.
source to share