Return flag 'y' if a column partition value for a date matches with its previous date usin-CodePudding

Use Case : Daily Flag - the date ‘2022-07-25’ will be compared with ‘2022-07-24’ if both the dates have same ‘number’ the ‘daily_number_flag’ will be set ‘y’ and if they have same ‘id’ then the ‘daily_id_flag’ will be ‘y’ for the date “2022-07-25”. Similarly, the date “2022-07-24” will be compared with the date “2022-07-23” based on the ‘number’ and ‘id’ the daily flags will be assigned respectively. The same is followed for all the other dates.

I will also have to assign monthly and yearly flags for both number and id.

Monthly flag - the date ‘2022-07-25’ will be compared with ‘2022-06-25’ if both the dates have same ‘number’ the ‘monthly_number_flag’ will be set ‘y’ and if they have same ‘id’ then the ‘monthly_id_flag’ will be ‘y’ for the date “2022-07-25”. Similarly, the date “2022-07-24” will be compared with the date “2022-06-24” based on the ‘number’ and ‘id’ the monthly flags will be assigned respectively. The same is followed for all the other dates.

Yearly flag - the date ‘2022-07-25’ will be compared with ‘2021-07-25’ if both the dates have same ‘number’ the ‘yearly_number_flag’ will be set ‘y’ and if they have same ‘id’ then the ‘yearly_id_flag’ will be ‘y’ for the date “2022-07-25”. Similarly, the date “2022-07-24” will be compared with the date “2021-07-24” based on the ‘number’ and ‘id’ the yearly flags will be assigned respectively. The same is followed for all the other dates.

Finally i will have 6 flags within the same table. Three flags for number(daily, monthly, yearly) and Three flags for id(daily, monthly, yearly).

I have done it for daily flag using lag function but this does not seems feasible with monthly and yearly.

WITH t1
     AS (SELECT customer_number,
                contract_id,
                time_key_source,
                Lag(time_key_source)
                  OVER(
                    partition BY customer_number
                    ORDER BY time_key_source) AS previous_customer_eff,
                    Lag(time_key_source)
                  OVER(
                    partition BY contract_id
                    ORDER BY time_key_source) AS previous_contract_eff
         FROM  contracts1)
SELECT customer_number,
       contract_id,
       time_key_source,
       CASE
         WHEN previous_customer_eff = Dateadd(d, -1, time_key_source) THEN 'y'
         ELSE 'n'
       END AS daily_customer_flag,
       CASE
         WHEN previous_contract_eff = Dateadd(d, -1, time_key_source) THEN 'y'
         ELSE 'n'
       END AS daily_contract_flag
FROM   t1
ORDER  BY time_key_source DESC;

Pre

CodePudding user response：

If you have SQL Server give something like this a shot:

select *
from t t1 cross apply (
    select distinct
        max(case when t2.time_key_source = dateadd(day,   -1, t1.time_key_source) then 'Y' else 'N' end) over () as one_day_ago,
        max(case when t2.time_key_source = dateadd(month, -1, t1.time_key_source) then 'Y' else 'N' end) over () as one_month_ago,
        max(case when t2.time_key_source = dateadd(year,  -1, t1.time_key_source) then 'Y' else 'N' end) over () as one_year_ago
    from t t2
    where t2.customer_number = t1.customer_number
      -- narrow the search range to the be as small as possible
      and t2.time_key_source >= dateadd(year, -1, t1.time_key_source)
      and t2.time_key_source  < t1.time_key_source
) v

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=69c75b18842c0577cccba4c8262464ae

This had to use distinct with the analytic max to get around a limitation of SQL Server so I do have some concerns that this might not be the best approach.