CODEWITHSUNDEEP
sql
Galmighty
Galmighty

Reputation: 15

SQL - if value is >1 look back row when value was = 0 for value

I have the following table output:

PLANT_SYS PEGGING_LEVEL_NO DEMAND_ORDER_NUMBER SUPPLY_ORDER_NUM ASSEMBLY_WO
3 0 H806145376 107973812 107973812
3 1 107973812 107975466 107973812
3 2 107975466 107981206 107975466
3 3 107981206 87688107 107981206
3 3 107981206 88597377 107981206
3 2 107975466 H024435900RT 107975466
3 0 H790880014GT3 108063690 108063690
3 1 108063690 88596228 108063690
3 1 108063690 108064890 108063690
3 2 108064890 88168775 108064890
3 2 108064890 88168776 108064890
3 2 108064890 88597385 108064890

What I am trying to achieve is when the value in the column PEGGING_LEVEL_NO is >1 look back and return the last value when PEGGING_LEVEL_NO was =0 from SUPPLY_ORDER_NUM. so the table would look like this with the column Required Value added:

PLANT_SYS PEGGING_LEVEL_NO DEMAND_ORDER_NUMBER SUPPLY_ORDER_NUM ASSEMBLY_WO Required_Value
3 0 H806145376 107973812 107973812 107973812
3 1 107973812 107975466 107973812 107973812
3 2 107975466 107981206 107975466 107973812
3 3 107981206 87688107 107981206 107973812
3 3 107981206 88597377 107981206 107973812
3 2 107975466 H024435900RT 107975466 107973812
3 0 H790880014GT3 108063690 108063690 108063690
3 1 108063690 88596228 108063690 108063690
3 1 108063690 108064890 108063690 108063690
3 2 108064890 88168775 108064890 108063690
3 2 108064890 88168776 108064890 108063690
3 2 108064890 88597385 108064890 108063690

at the moment I have the following query:

   SELECT
   plant_sys,
   pegging_level_no,
   demand_order_number,
   supply_order_num,
   CASE WHEN pegging_level_no = 0 THEN (supply_order_num) ELSE demand_order_number end AS Assembly_WO
FROM
   og_surf_snop_dm.sop_plan_calc_cp_t 
   WHERE  PLANT_SYS = '0003'

ORDER BY
   pegging_seq_2 ASC,
   pegging_level_no ASC;

I am hoping the above is possible but if not I have also been trying to do is use the example from this post: SQL - check previous row until the requested value is found But I can not get it to work with the code I have.

Any and all help is greatly appreciated!

UPDATE* Thanks you avb for looking at this, I have tried to add an ordering column, but I don't really know what I am doing here to make this work tbh!! I have tried the following :

SELECT plant_sys,
   pegging_level_no,
   demand_order_number,
   supply_order_num,
       max(nullif(PEGGING_LEVEL_NO, 2)) over (partition by plant_sys) as imputed_status
from og_surf_snop_dm.sop_plan_calc_cp_t

output being:

PLANT_SYS PEGGING_LEVEL_NO DEMAND_ORDER_NUMBER SUPPLY_ORDER_NUM ASSEMBLY_WO IMPUTED_STATUS
3 0 10200466 10200466 10200466 4
3 2 107992155 H035731700 107992155 4
3 2 107992155 H035731800 107992155 4

and also

SELECT
   plant_sys,
   pegging_level_no,
   demand_order_number,
   supply_order_num,
   CASE WHEN pegging_level_no = 0 THEN (supply_order_num) ELSE demand_order_number end AS Assembly_WO,
   ROW_NUMBER() OVER (ORDER BY plant_sys) AS Position
FROM
   og_surf_snop_dm.sop_plan_calc_cp_t

output being:

PLANT_SYS PEGGING_LEVEL_NO DEMAND_ORDER_NUMBER SUPPLY_ORDER_NUM ASSEMBLY_WO Position
3 0 10200466 10200466 10200466 2126
3 2 107992155 H035731700 107992155 37582
3 2 107992155 H035731800 107992155 2127

UPDATE 05/05*** I have gotten closer with the below code, but the required_Value is still showing 'NULL' for the items where the PEGGING_LEVEL_NO >0

SELECT 
    t.*,
    ROW_NUMBER() OVER (ORDER BY plant_sys) AS Position,
    CASE WHEN pegging_level_no = 0 THEN (supply_order_num) ELSE demand_order_number end AS Assembly_WO, -- ignore
    max(Required_Value) over (partition by PLANT_SYS, grp) as Required_Value
from (select t.*,
             sum(case when Required_Value is null then 0 else 1 end) over (partition by PLANT_SYS order by 27 Desc) as grp
      from 
        (select 
            t.*
            ,Case When PEGGING_LEVEL_NO = 0 Then SUPPLY_ORDER_NUM END as Required_Value 
        FROM og_surf_snop_dm.sop_plan_calc_cp_t t
         WHERE  PLANT_SYS = '0003')  t
     ) t;

Upvotes: 0

Views: 758

Answers (1)

avb0101
avb0101

Reputation: 84

You need to have an ordering column (identity seed, row_number etc.) of some sort added to the table to maintain the row order but the code referenced in that link will work.

Once you have an ordering column established you can use the below code which is borrowed from the other post you referenced.

In my example I created a column called "RowNumber" which is used to create the "grp" column which in turn is used to partition the Required_Value column.

select 
    t.PLANT_SYS
    ,t.PEGGING_LEVEL_NO
    ,t.DEMAND_ORDER_NUMBER
    ,t.SUPPLY_ORDER_NUM
    ,t.ASSEMBLY_WO
    ,max(Required_Value)  over (partition by PLANT_SYS, grp) as Required_Value
from (select t.*
             ,sum(case when Required_Value is null then 0 else 1 end) over (partition by PLANT_SYS order by RowNumber) as grp
      from 
        (select 
            *
            ,Case When PEGGING_LEVEL_NO = 0 Then SUPPLY_ORDER_NUM END as Required_Value 
        FROM og_surf_snop_dm.sop_plan_calc_cp_t
         WHERE  PLANT_SYS = '0003')  t
     ) t;

Upvotes: 1

Related Questions