CODEWITHSUNDEEP
sqloracle-database
checksql
checksql

Reputation: 1

Compare column value with previous record in Oracle

My oracle table data is as below.

Org_ID Product_ID Order_Month Amount
101 201 JAN-2021 2000
101 201 FEB-2021 2000
101 201 MAR-2021 2000
101 201 APR-2021 1500
101 201 MAY-2021 2000
101 202 JUN-2021 2000
101 202 JUL-2021 2000

We need to compare previous value for amount and find records with mis-matched amount and with respect to Product_ID.

My output should be like below. Tried using lag but couldn't find the solution. Can someone please provides inputs on how to approach for solving this.

Org_ID Product_ID Order_Month Amount
101 201 JAN-2021 to MAR-2021 2000
101 201 APR-2021 to APR-2021 1500
101 201 MAY-2021 to MAY-2021 1500
101 202 JUN-2021 to JUL-2021 1500

Upvotes: 0

Views: 358

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

This is a type of gaps-and-islands problem. In this case, the simplest solution is probably the difference of row numbers. The following assumes that order_month is actually a string (rather than a date):

select org_id, product_id, amount,
       min(order_month), max(order_month)
from (select t.*,
             row_number() over (partition by org_id, product_id order by to_date(order_month, 'MON-YYYY')) as seqnum,
             row_number() over (partition by org_id, product_id, amount order by to_date(order_month, 'MON-YYYY')) as seqnum_2
      from t
     ) t
group by org_id, product_id, amount, (seqnum - seqnm_2);

Why this works is a little tricky to explain. However, if you look at the results of the subquery, you will see how the difference between these two values is constant on adjacent months.

Upvotes: 0

ggordon
ggordon

Reputation: 10035

You may try the following

SELECT
    "Org_ID", 
    "Product_ID", 
    CONCAT(
         CONCAT(Order_Month_Group,' to '),
        TO_CHAR(MAX(actual_date),'MON-YYYY')
    ) as Order_Month, 
    "Amount"
FROM (
    SELECT
        t1.*,
        
        LAG(
             "Order_Month",
             CASE WHEN continued=0 THEN 0 ELSE seq_num-1 END
            ,"Order_Month") OVER (
                PARTITION BY "Org_ID","Product_ID","Amount"
                ORDER BY actual_date
        ) as Order_Month_Group
    FROM (
        SELECT 
            t.*,
            TO_DATE(t."Order_Month",'MON-YYYY') as actual_date,
            ROW_NUMBER() OVER (
                PARTITION BY t."Org_ID",t."Product_ID",t."Amount"
                ORDER BY TO_DATE("Order_Month",'MON-YYYY')
            ) as seq_num,
            CASE 
                WHEN t."Amount" = LAG(t."Amount",1,t."Amount") OVER (
                                      PARTITION BY t."Org_ID",t."Product_ID"
                                      ORDER BY TO_DATE("Order_Month",'MON-YYYY')
                                  ) THEN 1 
                ELSE 0 
            END as continued
        FROM 
            my_oracle_table t
    ) t1
) t2
GROUP BY "Org_ID", "Product_ID", Order_Month_Group, "Amount"
ORDER BY MIN(actual_date)

or

SELECT
    "Org_ID", 
    "Product_ID", 
    CONCAT(
         CONCAT(TO_CHAR(MIN(actual_date),'MON-YYYY'),' to '),
        TO_CHAR(MAX(actual_date),'MON-YYYY')
    ) as Order_Month, 
    "Amount"
FROM (
    SELECT
        t1.*,
        SUM(continued) OVER ( ORDER BY actual_date) as grp
    FROM (
        SELECT 
            t.*,
            TO_DATE("Order_Month",'MON-YYYY') as actual_date,
            CASE 
                WHEN t."Amount" = LAG(t."Amount",1,t."Amount") OVER (
                                      PARTITION BY t."Org_ID",t."Product_ID"
                                      ORDER BY TO_DATE("Order_Month",'MON-YYYY')
                                  ) THEN 0
                ELSE 1 
            END as continued
        FROM 
            my_oracle_table t
    ) t1
) t2
GROUP BY "Org_ID", "Product_ID", grp, "Amount"
ORDER BY MIN(actual_date)

View Demo on DB Fiddle

Let me know if this works for you.

Upvotes: 1

Related Questions