so I need to get the difference (in years) between two fields and I created the following variables:

SET YEAR_CNCL_EFCTV_DT = (SELECT YEAR(CNCL_EFCTV_DT) FROM CANCEL_DATES);
SET YEAR_CNCL_LST_MOD = (SELECT YEAR(CNCL_LST_MOD) FROM CANCEL_DATES);
SET YEAR = $YEAR_CNCL_EFCTV_DT - $YEAR_CNCL_LST_MOD;

Example:

CNCL_EFCTV_DT = 2023-03-02

CNCL_LST_MOD = 2022-02-24 00:56:21.754778

YEAR = 1

Problem 1: My variables only work when using single values, otherwise, I get 'Single-row subquery returns more than one row.' error.

Problem 2: How can I subtract the value of the 'YEAR' variable to a timestamp? I was trying this way but I don't know if it will work since my code stops running in the variable lines.

    DATEADD(YEAR, -$YEAR, CNCL_EFCTV_DT)

Thanks for you help!!

CodePudding user response：

The difference for multiple values can be done a couple of ways:

select 
    CNCL_EFCTV_DT
    ,CNCL_LST_MOD
    ,year(CNCL_EFCTV_DT) as YEAR_CNCL_EFCTV_DT 
    ,year(CNCL_LST_MOD) as YEAR_CNCL_LST_MOD 
    ,YEAR_CNCL_EFCTV_DT - YEAR_CNCL_LST_MOD as YEARS_A
    ,datediff('year', CNCL_LST_MOD, CNCL_EFCTV_DT) as YEARS_B
    ,year(CNCL_EFCTV_DT) - year(CNCL_LST_MOD) as years_c
from values
    ('2023-03-02'::date, '2022-02-24 00:56:21.754778'::timestamp),
    ('2022-01-02'::date, '2021-03-24 00:56:21.754778'::timestamp),
    ('2020-02-02'::date, '2019-01-24 00:56:21.754778'::timestamp)
    t(CNCL_EFCTV_DT, CNCL_LST_MOD);

gives:

and the subtraction in the form you have is correct, but the normal method is to test it by itself:

select 
    CNCL_EFCTV_DT
    ,CNCL_LST_MOD
    ,year(CNCL_EFCTV_DT) - year(CNCL_LST_MOD) as years
    ,dateadd('year', -years, '1999-01-30 01:23:45'::timestamp) as subtraction
from values
    ('2023-03-02'::date, '2022-02-24 00:56:21.754778'::timestamp),
    ('2022-01-02'::date, '2021-03-24 00:56:21.754778'::timestamp),
    ('2020-02-02'::date, '2019-01-24 00:56:21.754778'::timestamp)
    t(CNCL_EFCTV_DT, CNCL_LST_MOD);

CodePudding user response：

If you have multiple rows, you're either going to do row based processing (bad idea on Snowflake), or you can use set-based processing. Here are some examples, hoping one or more gives you what you need:

--
-- Create a test table and populate with multiple rows - example here shows an autocalculated value for date diff, and another column that's null to start
--

create
or replace table cancel_dates (CNCL_EFCTV_DT date, CNCL_LST_MOD timestamp, yr_diff integer default datediff(year,CNCL_EFCTV_DT,CNCL_LST_MOD::date), upd_yr_diff integer);

insert into
    cancel_dates (CNCL_EFCTV_DT, CNCL_LST_MOD)
values
    (
        to_date('2023-03-02', 'YYYY-MM-DD'),
        to_timestamp_ntz(
            '2022-02-24 00:56:21.754778',
            'YYYY-MM-DD HH24:MI:SS.FF'
        )
    ),
    (
        to_date('2022-01-02', 'YYYY-MM-DD'),
        to_timestamp_ntz(
            '2021-01-01 00:56:21.754778',
            'YYYY-MM-DD HH24:MI:SS.FF'
        )
    );

--
-- See the auto-calculated value for the year diff
--

select * from cancel_dates;

CNCL_EFCTV_DT   CNCL_LST_MOD                        YR_DIFF UPD_YR_DIFF
2023-03-02      2022-02-24T00:56:21.754778-05:00    -1  
2022-01-02      2021-01-01T00:56:21.754778-05:00    -1   


--
-- Do a set-based update for the null column
--

update cancel_dates 
set upd_yr_diff = datediff(year,CNCL_EFCTV_DT,CNCL_LST_MOD::date)
where upd_yr_diff is null;

select * from cancel_dates;
    
CNCL_EFCTV_DT   CNCL_LST_MOD                        YR_DIFF UPD_YR_DIFF
2023-03-02      2022-02-24T00:56:21.754778-05:00    -1              -1
2022-01-02      2021-01-01T00:56:21.754778-05:00    -1              -1

--
--  Option 2, create a view with the datediff calc across multiple rows
--
    
create
    or replace view date_logic as (
        select
            cncl_efctv_dt,
            YEAR(CNCL_EFCTV_DT) as CED_YR,
            cncl_lst_mod,
            YEAR(CNCL_LST_MOD) as CLD_YR,
            datediff(year,CNCL_EFCTV_DT,CNCL_LST_MOD::date) as yr_diff
        from
            cancel_dates
    );

select * from date_logic;

CNCL_EFCTV_DT   CED_YR  CNCL_LST_MOD                        CLD_YR  YR_DIFF
2023-03-02      2023    2022-02-24T00:56:21.754778-05:00    2022    -1
2022-01-02      2022    2021-01-01T00:56:21.754778-05:00    2021    -1