SQL to update a column using another column

UDA
Enthusiast

SQL to update a column using another column

I need the logic/ansi sql to update a column, LP_Qty in my table using another col in the same table this is RF_Qty. RF_Qty gets updated everyday as the table refreshes everyday. But LP_Qty should be equal to RF_Qty only when it is FisPeriodEnd_Dt, else it should retain it's previous value till it is FisPeriodEnd_Dt.

My target table has the following Cols

Item_Id
FiscalPeriod_Id (MonthLevel)
RF_Qty,
LP_Qty

there is another table called Fiscal_Period that has
FiscalPeriod_Id (MonthLevel)
FiscalPeriodStart_Dt
FiscalPeriodEnd_Dt

We also have current_date to use as this is not for history

how do I populate the target table?

thanks in advance
5 REPLIES
Enthusiast

Re: SQL to update a column using another column

I am not entirely sure this will work since I don't fully understand your ETL process or the data involved, but could you add an update to LP_Qty as part of your daily refresh process? Only when it is FisPeriodEnd_Dt though would the value actually change. Something like:

Update Target_Table
From Fiscal_Period FP
Set LP_Qty = Case When FP.FiscalPeriodEnd_Dt = Current_Date Then RF_Qty Else LP_Qty End
Where Target_Table.FiscalPeriod_Id = FP.FiscalPeriod_Id;

Good luck,
Jason
Enthusiast

Re: SQL to update a column using another column

Thanks Jason for your reply. My ETL process deletes all data from the target table everyday and reloads it with fresh data. It's a DSS.

I need to do the update with previous day's value if the condition for end of fiscalperiod is not met (FiscalPeriodEnd_Dt = current_date). I need to get a snapshot of the previous day's LastProjected_Qty value and use this for update else use current value of ResultantForecast_Qty.

What modifications to your code would achieve that??

Thanks again

Enthusiast

Re: SQL to update a column using another column

Seems like you should try to add the logic to your insert statement so that you do not need to do a sperate update. Could you post your current insert statement for review?
Enthusiast

Re: SQL to update a column using another column

this is my original insert statement it updates LP_Qty with the RF_Qty everyday now.

DELETE FROM RTL_DEV_DATA.RPLNSHMNT_FORECAST ALL;

INSERT INTO RTL_DEV_DATA.RPLNSHMNT_FORECAST

SEL it.Item_ID,
fp.Fiscalperiod_ID,
rc.Facility_ID,
sum(rc.Dtl_Qty),
sum(dm.ResultantForecast_Qty),
sum(dm.ResultantForecast_Qty),
sum(dm.ForcedSysForecast_Qty)

FROM RTL_DEV_DATA.FISCAL_PERIOD fp ,RTL_DEV_DATA.ITEM it, RTL_DEV_WORK.TSTAGING_RECEIPTS rc,RTL_DEV_WORK.TSTAGING_DMND dm

WHERE it.Class_id=rc.Class_ID
AND it.VENDORORG_ID=rc.VENDORORG_ID
AND it.STYLE_ID=rc.STYLE_ID
AND it.COLOR_ID=rc.COLOR_ID
AND it.SIZE_ID=rc.SIZE_ID
AND it.CHAIN_ID=rc.CHAIN_ID
AND it.Class_id=dm.Class_ID
AND it.VENDORORG_ID=dm.VENDORORG_ID
AND it.STYLE_ID=dm.STYLE_ID
AND it.COLOR_ID=dm.COLOR_ID
AND it.SIZE_ID=dm.SIZE_ID
AND it.CHAIN_ID=dm.CHAIN_ID
AND rc.Class_id=dm.Class_ID
AND rc.VENDORORG_ID=dm.VENDORORG_ID
AND rc.STYLE_ID=dm.STYLE_ID
AND rc.COLOR_ID=dm.COLOR_ID
AND rc.SIZE_ID=dm.SIZE_ID
AND rc.CHAIN_ID=dm.CHAIN_ID
AND rc.Facility_ID=dm.Facility_ID
AND dm.Fiscalperiod_ID=fp.Fiscalperiod_ID
AND fp.FiscalPeriodEnd_Dt = rc.Dtl_Date
AND rc.REQMT_CODE='S'

group by 1,2,3;
Enthusiast

Re: SQL to update a column using another column

You said initially that LP_Qty "should retain it's previous value till it is FisPeriodEnd_Dt". I don't know your data well enough to see how to calculate this on a daily basis.

Can you provide a SQL statement that would calculate LP_Qty for a NON-FisPeriodEnd_Dt day?

Perhaps you will need to change your insert to join 2 derived tables to work. Such as:

INSERT INTO RTL_DEV_DATA.RPLNSHMNT_FORECAST
SEL A.Item_ID,
A.Fiscalperiod_ID,
A.Facility_ID,
A.Dtl_Qty,
Case When A.FiscalPeriodEnd_Dt = Current_Date Then A.RF_qty else B.LP_qty end,
A.RF_Qty,
A.ForcedSysForecast_Qty
FROM

(SEL it.Item_ID,
fp.Fiscalperiod_ID,
rc.Facility_ID,
sum(rc.Dtl_Qty),
sum(dm.ResultantForecast_Qty),
sum(dm.ResultantForecast_Qty),
sum(dm.ForcedSysForecast_Qty)

FROM RTL_DEV_DATA.FISCAL_PERIOD fp ,RTL_DEV_DATA.ITEM it, RTL_DEV_WORK.TSTAGING_RECEIPTS rc,RTL_DEV_WORK.TSTAGING_DMND dm

WHERE it.Class_id=rc.Class_ID
AND it.VENDORORG_ID=rc.VENDORORG_ID
AND it.STYLE_ID=rc.STYLE_ID
AND it.COLOR_ID=rc.COLOR_ID
AND it.SIZE_ID=rc.SIZE_ID
AND it.CHAIN_ID=rc.CHAIN_ID
AND it.Class_id=dm.Class_ID
AND it.VENDORORG_ID=dm.VENDORORG_ID
AND it.STYLE_ID=dm.STYLE_ID
AND it.COLOR_ID=dm.COLOR_ID
AND it.SIZE_ID=dm.SIZE_ID
AND it.CHAIN_ID=dm.CHAIN_ID
AND rc.Class_id=dm.Class_ID
AND rc.VENDORORG_ID=dm.VENDORORG_ID
AND rc.STYLE_ID=dm.STYLE_ID
AND rc.COLOR_ID=dm.COLOR_ID
AND rc.SIZE_ID=dm.SIZE_ID
AND rc.CHAIN_ID=dm.CHAIN_ID
AND rc.Facility_ID=dm.Facility_ID
AND dm.Fiscalperiod_ID=fp.Fiscalperiod_ID
AND fp.FiscalPeriodEnd_Dt = rc.Dtl_Date
AND rc.REQMT_CODE='S'

group by 1,2,3) as A,

(SEL Item_ID,
Sum(Some_Column) as LP_Qty
From Some_Table
) as B

WHERE A.Item_ID = B.ITEM_ID;

I am probably missing a whole lot that will prevent this from working logically. I probably can not give you the exact answer to your question without fully understanding all the tables and relationships involved. So my advice is to play around with this example. Assuming that the data is available (that LP_Qty can be calculated somehow on a daily basis), then the use of a CASE statement and maybe derived tables should work. I would try to achieve it in one statement verses an Insert + Update if the performance demands can be met.

Good luck
Jason