SQL查询以查找日期更改的第一次出现。

Question

我改变了这个问题，使它更容易理解。

我创建了一个查询，以获得以下日期输出：

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

我将参数传递为01-11-2021和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

我如何调整上面的查询，以便如果在值列(如person_number 12 )中发生了更改，那么这两行应该出现在输出中；如果在同一雇员的两行(如person_number 11 )中没有任何更改，则应该只选择最新的行。

预期产出为-

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

案例

1. 如果person_number 12的值从5.5更改为0，那么这两行都应该出现，而最新的行base_element_name应该添加"~“。
2. 如果“健康”只标记给员工一次，并且是“结束日期”，则只应显示该行。
3. 如果日期范围内的值没有变化，那么应该出现最新的行，例如-11。
4. 对于person#14和15，第一个有效开始日期应该是第一个生效日期，第一个生效日期是“最新”更改或最后一个更改值的生效日期。

Answer 1

只需对查询进行排序，以显示2个最新日期，然后将~添加到第二个最新日期。希望这能帮上忙。

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)