David David - 4 months ago 8
SQL Question

Oracle SQL- Flag records based on record's date vs history

This is my 1st post on the forum. Usually I was able to find what I needed - but to tell the truth - I am not really sure how to ask a correct question to the issue. Therefore, please accept my apologies if there already is an answer on the forum and I missed it.

I am running the following code in an Oracle database via Benthic Software:

SELECT
T1."REGION"
, T1."COUNTRY"
, T1."IDNum"
, T1."CUSTOMER"
, T1."BUSSINESS"
, T3."FISCALYEARMONTH"
, T3."FISCALYEAR"
, SUM(T4."VALUE")
,"HISTORICAL_PURCHASE_FLAG"

FROM
"DATABASE"."SALES" T4
, "DATABASE"."CUSTOMER" T1
, "DATABASE"."PRODUCT" T2
, "DATABASE"."TIME" T3

WHERE
T4."CUSTOMERID" = T1."CUSTOMERID"
AND T4."PRODUCTID" = T2."PRODUCTID"
AND T4."DATEID" = T3."DATEID"
AND T3."FISCALYEAR" IN ('2016')
AND T1."COUNTRY" IN ('ENGLAND', 'France')


GROUP BY
T1."REGION"
, T1."COUNTRY"
, T1."IDNum"
, T1."CUSTOMER"
, T1."BUSSINESS"
, T3."FISCALYEARMONTH"
, T3."FISCALYEAR"
;


This query provides me with information on transactions. As you can see above, I would like to add a column named "HISTORICAL_PURCHASE_FLAG".

I would like the query to take CUSTOMER and FISCALYEARMONTH. Then, I would like to check if there are any transactions registered for the CUSTOMER, up to 2 years in the past.

So lets say I get the following result:

LineNum REGION COUNTRY IDNum CUSTOMER BUSSINESS FISCALYEARMONTH FISCALYEAR VALUE HISTORICAL_PURCHASE_FLAG
1 Europe ENGLAND 255 Abraxo Cleaner Co. Chemicals 201605 2016 34,567.00
2 Europe FRANCE 123 Metal Trade Heavy 201602 2016 12,500.00
3 Europe ENGLAND 255 Abraxo Cleaner Co. Chemicals 201601 2016 8,400.00


LineNum 1 shows transaction for Abraxo Cleaner Co. registered on 201605. And LineNum 3 is also for Abraxo Cleaner Co. but registered on 201601. What I would need the query to do, is to flag LineNum 1 as 'Existing'. Because there was a previous transaction registered.

On the other hand, LineNum 3 was the first time transactions was registered for Abraxo Cleaner Co. so the line would be flagged as 'New'.

To sum up, I would like for each row of data to be treated individually. And to check if there are any previous records of data for CUSTOMER & FISCALYEARMONTH - 24 months.

Thank you in advance for the help.

Answer

You can use LAG function:

SELECT
"REGION"
, "COUNTRY"
, "IDNum"
, "CUSTOMER"
, "BUSSINESS"
, "FISCALYEARMONTH"
, "FISCALYEAR"
, SUM("VALUE") 
, MAX(CASE WHEN to_date(prev_fym,'YYYYMM')  >= ADD_MONTHS (to_date("FISCALYEARMONTH",'YYYYMM'), -24) THEN 'Existing' 
           ELSE NULL END) "HISTORICAL_PURCHASE_FLAG"
FROM
(
    SELECT
    T1."REGION"
    , T1."COUNTRY"
    , T1."IDNum"
    , T1."CUSTOMER"
    , T1."BUSSINESS"
    , T3."FISCALYEARMONTH"
    , T3."FISCALYEAR"
    , T4."VALUE"
    , LAG ("FISCALYEARMONTH", 1) OVER (PARTITION BY T1."IDNum" ORDER BY T3."FISCALYEARMONTH" DESC) prev_fym 
    FROM
    "DATABASE"."SALES" T4
    , "DATABASE"."CUSTOMER" T1
    , "DATABASE"."PRODUCT" T2
    , "DATABASE"."TIME" T3
    WHERE
    T4."CUSTOMERID" = T1."CUSTOMERID"
    AND T4."PRODUCTID" = T2."PRODUCTID"
    AND T4."DATEID" = T3."DATEID"
    AND T1."COUNTRY" IN ('ENGLAND', 'France')
    AND T3."FISCALYEAR" IN ('2014','2015','2016')
)
WHERE "FISCALYEAR" IN ('2016')
GROUP BY
"REGION"
, "COUNTRY"
, "IDNum"
, "CUSTOMER"
, "BUSSINESS"
, "FISCALYEARMONTH"
, "FISCALYEAR"
;