I have changed the question to make it more understandable
I have created a query to get the following output with date:
PERSON_nUMBER base_element_name effective_start_date effective_end_date Value
11 Health 2021-11-21 4712-12-31 5.5
11 Health 2021-10-18 2021-11-20 5.5
12 Health 2021-11-11 4712-12-31 0
12 Health 2021-11-01 2021-11-10 5.5
13 Health 2021-11-01 2021-11-09 6
14 Health 2021-11-10 2021-11-19 6
14 Health 2021-11-20 2021-11-30 6
14 Health 2021-12-01 2021-12-05 5
14 Health 2021-12-06 4712-12-31 5
15 Health 2021-11-10 2021-11-19 6
15 Health 2021-11-20 2021-11-30 6
15 Health 2021-12-01 2021-12-05 6
15 Health 2021-12-06 2021-12-15 5
15 Health 2021-12-16 4712-12-31 5
I pass the parameter as 01-11-2021 and 30-11-2021
select
petf.person_number
petf.base_element_name,
to_char(peef.effective_start_date,'YYYY-MM-DD') effective_start_date ,
to_char(peef.effective_end_date,'YYYY-MM-DD') effective_end_date,
pivf.value
from pay_element_types_f petf ,
pay_input_values_f pivf,
per_all_elementries_f peef
where pivf.element_type_id = petf.element_type_id
AND petf.base_element_name in ('Health')
and peef.element_entry_id= pivf.element_entry_id
and peef.effective_start_date between :from_date and :to_date
How can I tweak the above query such that if there is a change in value column like person_number 12 then the two rows should come in output; and if there is no change within the two rows of the same employee like person_number 11 then only the latest row should be picked.
The expected output is -
PERSON_nUMBER base_element_name effective_start_date Value
11 Health 2021-11-21 5.5
12 ~Health 2021-11-11 0
12 Health 2021-11-01 5.5
13 Health 2021-11-01 6
14 Health 2021-11-10 6
14 ~Health 2021-12-01 5
15 Health 2021-11-10 6
15 ~Health 2021-12-06 5
Case
- if there is a change in value from 5.5 to 0 for person_number 12 then both the rows should come and latest one base_element_name should have "~" added.
- If there is Health tagged to an employee only once and is end date then only that row should be displayed.
- If there is no change in the value within the date range then the latest row should come for example-11
- For person#14 and 15 , the first effective start date should come and the first occurence effective start date of the "latest" change or the last changed value
CodePudding user response：
Just rank your query to show the 2 latest dates then add ~ to the 2nd latest date. Hope this could help.
WITH CTE AS(
select
petf.person_number
petf.base_element_name,
to_char(peef.effective_start_date,'YYYY-MM-DD') effective_start_date ,
to_char(peef.effective_end_date,'YYYY-MM-DD') effective_end_date,
pivf.value
,RANK() OVER(PARTITION BY petf.person_number ORDER BY peef.effective_start_date desc) as my_rank --Add Rank to Get the 2 latest date.
from pay_element_types_f petf ,
pay_input_values_f pivf,
per_all_elementries_f peef
where pivf.element_type_id = petf.element_type_id
AND petf.base_element_name in ('Health')
and peef.element_entry_id= pivf.element_entry_id
and peef.effective_start_date between :from_date and :to_date
)
SELECT
person_number
,CASE WHEN my_rank = 2 THEN CONCAT('~',base_element_name) ELSE base_element_name END as base_element_name
,effective_start_date
,Value
FROM CTE
WHERE my_rank in (1,2)
CodePudding user response：
From Oracle 12, you can use MATCH_RECOGNIZE:
SELECT person_number,
CASE cls WHEN 'CHANGED' THEN '~' END || base_element_name
AS base_element_name,
effective_start_date,
effective_end_date,
value
FROM (
SELECT *
FROM your_query
-- Without date filter
)
MATCH_RECOGNIZE (
PARTITION BY person_number
ORDER BY effective_start_date
MEASURES
CLASSIFIER() AS cls
ALL ROWS PER MATCH
PATTERN (first_row (changed | {- outside_range -} | $))
DEFINE
first_row AS effective_start_date < :to_date INTERVAL '1' DAY
AND effective_end_date >= :from_date,
changed AS value != first_row.value,
outside_range AS effective_start_date >= :to_date INTERVAL '1' DAY
);
Which, for the sample data:
CREATE TABLE your_query (
PERSON_NUMBER,
base_element_name,
effective_start_date,
effective_end_date,
Value
) AS
SELECT 11, 'Health', DATE '2021-11-21', DATE '4712-12-31', 5.5 FROM DUAL UNION ALL
SELECT 11, 'Health', DATE '2021-10-18', DATE '2021-11-20', 5.5 FROM DUAL UNION ALL
SELECT 12, 'Health', DATE '2021-11-11', DATE '4712-12-31', 0.0 FROM DUAL UNION ALL
SELECT 12, 'Health', DATE '2021-11-01', DATE '2021-11-10', 5.5 FROM DUAL UNION ALL
SELECT 13, 'Health', DATE '2021-11-01', DATE '2021-11-09', 6.0 FROM DUAL UNION ALL
SELECT 14, 'Health', DATE '2021-11-10', DATE '2021-11-19', 6.0 FROM DUAL UNION ALL
SELECT 14, 'Health', DATE '2021-11-20', DATE '2021-11-30', 6.0 FROM DUAL UNION ALL
SELECT 14, 'Health', DATE '2021-12-01', DATE '2021-12-05', 5.0 FROM DUAL UNION ALL
SELECT 14, 'Health', DATE '2021-12-06', DATE '4712-12-31', 5.0 FROM DUAL UNION ALL
SELECT 15, 'Health', DATE '2021-11-10', DATE '2021-11-19', 6.0 FROM DUAL UNION ALL
SELECT 15, 'Health', DATE '2021-11-20', DATE '2021-11-30', 6.0 FROM DUAL UNION ALL
SELECT 15, 'Health', DATE '2021-12-01', DATE '2021-12-05', 6.0 FROM DUAL UNION ALL
SELECT 15, 'Health', DATE '2021-12-06', DATE '2021-12-15', 5.0 FROM DUAL UNION ALL
SELECT 15, 'Health', DATE '2021-12-16', DATE '4712-12-31', 5.0 FROM DUAL UNION ALL
SELECT 16, 'Health', DATE '2021-10-16', DATE '2021-11-15', 4.0 FROM DUAL UNION ALL
SELECT 16, 'Health', DATE '2021-11-16', DATE '4712-12-31', 5.0 FROM DUAL;
(Which includes person #16 where the effective_start_date of the first row is before the start of the range but the change occurs within the range.)
Outputs:
PERSON_NUMBER BASE_ELEMENT_NAME EFFECTIVE_START_DATE EFFECTIVE_END_DATE VALUE 11 Health 2021-11-21 00:00:00 4712-12-31 00:00:00 5.5 12 Health 2021-11-01 00:00:00 2021-11-10 00:00:00 5.5 12 ~Health 2021-11-11 00:00:00 4712-12-31 00:00:00 0 13 Health 2021-11-01 00:00:00 2021-11-09 00:00:00 6 14 Health 2021-11-20 00:00:00 2021-11-30 00:00:00 6 14 ~Health 2021-12-01 00:00:00 2021-12-05 00:00:00 5 15 Health 2021-11-20 00:00:00 2021-11-30 00:00:00 6 16 Health 2021-10-16 00:00:00 2021-11-15 00:00:00 4 16 ~Health 2021-11-16 00:00:00 4712-12-31 00:00:00 5
db<>fiddle here