CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Nabaa
Nabaa

Reputation: 53

Check values precedence based on creation date order in Snowflake

I have a small case that I want to highlight using SQL/Snowflake.

For example (table 1 below), if the creation date of the value NO PO in CF_PO_Number is less than the creation date of the value PO-0257880 then PO_Issued = True

Job_ID CF_PO_Number Created_At
153428 PO-025780 2021-10-20 10:22:43.000000000
153428 2021-10-20 09:03:14.000000000
153428 NO PO 2021-10-05 13:16:04.000000000

The desired outcome for table 1 is as follows,

Job_ID PO_Issued
153428 True

Another example (table 2 below), if the creation date of the value PO-0257880 in CF_PO_Number is older than the creation date of the value NO PO then PO_Issued = False

Job_ID CF_PO_Number Created_At
153428 NO PO 2021-10-20 10:22:43.000000000
153428 2021-10-20 09:03:14.000000000
153428 PO-025780 2021-10-05 13:16:04.000000000

The desired outcome for table 2 is as follows,

Job_ID PO_Issued
153428 False

Moreover, CF_PO_Number can have only one value either NO PO and it means PO is required and not issued then PO_Issued = False or PO-025780 which means PO is required and issued then PO_Issued = True, the example cases are rarely happening.

I hope the case is now clear.

Upvotes: 1

Views: 115

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

So if I understand there can only be three values of CF_PO_Number ['NO_PO', 'PO-025780', null] and we are finding the related dates for no_po and po.

Then this SQL I think covers your cases: just noticed the 431 case

SELECT
    job_id,
    max(iff(CF_PO_Number='NO PO', Created_At, null)) as no_po_date,
    max(iff(CF_PO_Number='PO-025780', Created_At, null)) as po_date,
    CASE
        WHEN no_po_date is null AND po_date is not null then true
        WHEN no_po_date is not null AND po_date is null then false
        WHEN no_po_date is null AND po_date is null then false -- CAN THIS HAPPEN? 
        ELSE no_po_date < po_date
    END as PO_Issued      
FROM VALUES
    (153428, 'PO-025780', '2021-10-20 10:22:43'),
    (153428, null, '2021-10-20 09:03:14'),
    (153428, 'NO PO', '2021-10-05 13:16:04'),
    (153429, 'NO PO', '2021-10-20 10:22:43'),
    (153429, null, '2021-10-20 09:03:14'),
    (153429, 'PO-025780', '2021-10-05 13:16:04'),
    (153430, 'NO PO', '2021-10-05 13:16:04'),
    (153431, 'PO-025780', '2021-10-05 13:16:04')
    t(Job_ID, CF_PO_Number, Created_At)
GROUP BY 1
ORDER BY 1;

which gives:

JOB_ID NO_PO_DATE PO_DATE PO_ISSUED
153,428 2021-10-05 13:16:04 2021-10-20 10:22:43 TRUE
153,429 2021-10-20 10:22:43 2021-10-05 13:16:04 FALSE
153,430 2021-10-05 13:16:04 FALSE
153,431 2021-10-05 13:16:04 TRUE

Upvotes: 1

Related Questions