To provide an answer to this problem, I did the following (all of the code below is on the fiddle here):
CREATE TABLE test
(
row_number INTEGER NOT NULL,
code TEXT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NULL
);
Populated with:
INSERT INTO test VALUES
(1, 'A', '2021-09-07', NULL),
(2, 'A', '2021-04-01', '2021-09-06'),
(3, 'B', '2021-03-13', '2021-03-31'),
(4, 'A', '2021-01-13', '2021-03-12'),
(5, 'A', '2021-01-01', '2021-01-12');
Note the use of out of sequence row_number
s - this can very easily happen in a frequently updated database - the resolution should only depend on those parts of the data that are independent of implementation details - i.e. the from_date
in this case.
Step 1:
Mark when a code change takes place - in order of from_date DESC
.
SELECT
ROW_NUMBER() OVER (ORDER BY from_date DESC) AS rn,
code, from_date, to_date,
CASE
WHEN LAG(code) OVER (ORDER BY from_date DESC) = code THEN 0
ELSE 1
END AS code_change
FROM test;
Result:
rn code from_date to_date code_change
1 A 2021-09-07 NULL 1
2 A 2021-04-01 2021-09-06 0
3 B 2021-03-13 2021-03-31 1
4 A 2021-01-13 2021-03-12 1
5 A 2021-01-01 2021-01-12 0
Second step:
Perform a cumulative sum over the code_change
generated variable
SELECT
*, SUM(code_change) OVER (ORDER BY from_date DESC)
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY from_date DESC) AS rn,
code, from_date, to_date,
CASE
WHEN LAG(code) OVER (ORDER BY from_date DESC) = code THEN 0
ELSE 1
END AS code_change
FROM test
) AS tab_01
ORDER BY from_date DESC;
Result:
rn code from_date to_date code_change sum
1 A 2021-09-07 NULL 1 1
2 A 2021-04-01 2021-09-06 0 1
3 B 2021-03-13 2021-03-31 1 2
4 A 2021-01-13 2021-03-12 1 3
5 A 2021-01-01 2021-01-12 0 3
So, we now have a means of grouping the codes by sequence of the same code!
Step 3:
We now obtain the MIN(sum_cc)
to obtain the group with code 'A'
. This gives us the group with the most recent date(s) - because of the ORDER BY from_date DESC
!
SELECT
code, MIN(sum_cc)
FROM
(
SELECT
*, SUM(code_change) OVER (ORDER BY from_date DESC) AS sum_cc
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY from_date DESC) AS rn,
code, from_date, to_date,
...
... SQL snipped for brevity
...
) AS tab_02
WHERE code = 'A'
GROUP BY code;
Result:
code min
A 1
So, we no know that 'A'
with grouping = 1
that is the group of interest.
VERY IMPORTANT - this will work even if the row_number
varialbe is NOT in sync with the date values - this can easily happen in a multi-user system - see this fiddle here.
Step 4:
We now JOIN
the result of step 4 back to the result from step 3 and obtain our final desired result as follows:
SELECT tab_03.*, tab_02.*
FROM
(
SELECT
*, SUM(tab_01.code_change) OVER (ORDER BY tab_01.from_date DESC) AS xx
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY from_date DESC) AS rn,
code, from_date, to_date,
CASE
WHEN LAG(code) OVER (ORDER BY from_date DESC) = code THEN 0
ELSE 1
END AS code_change
FROM test
) AS tab_01
) AS tab_03
JOIN
(
SELECT
code, MIN(sum_cc) AS yy
FROM
(
SELECT
*, SUM(code_change) OVER (ORDER BY from_date DESC) AS sum_cc
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY from_date DESC) AS rn,
code, from_date, to_date,
CASE
WHEN LAG(code) OVER (ORDER BY from_date DESC) = code THEN 0
ELSE 1
END AS code_change
FROM test
) AS tab_01
ORDER BY from_date DESC
) AS tab_02
WHERE code = 'A'
GROUP BY code
) AS tab_02
ON tab_03.xx = tab_02.yy;
Result:
row_number code from_date to_date
1 A 2021-09-07 NULL
2 A 2021-04-01 2021-09-06
which is correct for the data given above. This solution also works in situations where the row_number
is not in sync with the dates (see here)!