help required for a update scenario

Database

help required for a update scenario

Hi,

We have a customer group with some customer under it with different product. Need to update a value for below scenario.

C1,C2 and C3 are from same customer group. Always qty<>cal_qty for C2 and C3 in daily data. If in daily data we find C1 then the "cal_qty" should be equal to "qty" for C2 and C3. If C1 is not there in the daily data then no need to do any update anything

Before Update if C1 is there:                                                                         After Update if C1 is there:  

Customer id    Product  date                  qty        cal_qty             Customer id    Product     date                  qty        cal_qty

C1                      P1      2013-11-01        10          10                      C1                      P1      2013-11-01        10          10

C2                      P2      2013-11-01        20            ?                      C2                      P2      2013-11-01        20           20

C3                      P1      2013-11-01        30            ?                      C3                      P1      2013-11-01        30            30

C2                      P1      2013-11-02        40            ?                      C2                      P1      2013-11-02        40            40

C3                      P1      2013-11-03        50            ?                      C3                      P1      2013-11-03        50            50

Before Update if C1 is not there:                                                                         After Update if C1 is not there:  no change in daily data 

Customer id    Product  date                  qty        cal_qty             Customer id    Product     date                  qty        cal_qty

C2                      P2      2013-11-01        20            ?                      C2                      P2      2013-11-01        20           ?

C3                      P1      2013-11-01        30            ?                      C3                      P1      2013-11-01        30            ?

C2                      P1      2013-11-02        40            ?                      C2                      P1      2013-11-02        40            ?

C3                      P1      2013-11-03        50            ?                      C3                      P1      2013-11-03        50            ?

Tags (1)
9 REPLIES

Re: help required for a update scenario

Did you try writing a SQL?

If you are not getting the results then someone can lead you where you want to be after looking into the SQL...

Re: help required for a update scenario

I created a temp table to maintain the customer group information with a flag:

temp_table 

cust_id flag

C1        Y

C2        N

C3        N

The below update query will work if customer C1 is present in daily data, but need to modify the query in such a way that it should not do any update if C1 is not present in daily data. Here C1 is considered as primary customer , if its not there data should flow as such.

update tgt

from table_A tgt,

(sel 

cust_id,

flag

from 

temp_table

) temp

set cal_qty=

case (when Customer_id=temp.cust_id and temp.flag='N')

then qty end 

where Customer_id=temp.cust_id

Re: help required for a update scenario

Can anyone please help on above query ?

Re: help required for a update scenario

Replace CUSTOMER_TEST with your table name and I hope the below query will work for you

UPDATE TBLA
FROM CUSTOMER_TEST TBLA, CUSTOMER_TEST TBLB, (SELECT CUSTOMER_ID FROM CUSTOMER_TEST WHERE CUSTOMER_ID = 'C1' QUALIFY RANK() OVER(ORDER BY DATE_)=1) TBLC
SET CAL_QTY = CASE WHEN TBLC.CUSTOMER_ID IS NOT NULL THEN TBLB.QTY ELSE TBLB.CAL_QTY END
WHERE TBLA.CUSTOMER_ID = TBLB.CUSTOMER_ID
AND TBLA.PRODUCT = TBLB.PRODUCT
AND TBLA.DATE_ = TBLB.DATE_

Re: help required for a update scenario

Thanks Kiani,

But here I have multile customer groups to be checked before updating the daily data. Like in above example we have one customer group with C1,C2 and C3 in which C1 is the customer which is like primary customer. Now if we have another customer group as C4 and C5 which will also flow through the daily data. In the second customer group if C4 is the primary , then we have to check for C4 in daily data do the same logic for C5 customer as well.

So same update query should work for multiple customer groups. We can even have lookup table which holds the customer groups , but difficult part to maintain the same update query.

Can you please chek if its possible.

Re: help required for a update scenario

So basically what I understand is that you need to update certain customer groups data only if the primary customers data within that customer group exists... Is that correct?

Re: help required for a update scenario

Yes Kiani

Re: help required for a update scenario

To more clarify, consider if we have two customer groups , one set with C1,C2 & C3 (C1 as primary) and second set with C4 & C5 (C4 as primary). The update to the daily data should work separately for the two groups checking for its respective primary customer from the groups.

Re: help required for a update scenario

Considering you have a table that maintains the group and primary customer relationship within that group, you can execute the below mentioned query to get the desired results.

CUSTMOER_GROUP should look something like this;

CUSTOMER_ID, CUSTOMER_ID_P

C1, C1

C2, C1

C3, C1

(The first column contains the list of customer ids, and the second column mentions the primary customer id for those customers thus making the groups automatically)

UPDATE TBLA
FROM CUSTOMER_TEST TBLA, CUSTOMER_TEST TBLB, (SELECT * FROM CUSTOMER_GROUP WHERE CUSTOMER_ID_P IN (SELECT DISTINCT CUSTOMER_ID_P FROM CUSTOMER_GROUP where customer_id = customer_id_p)) TBLC
SET CAL_QTY = CASE WHEN TBLC.CUSTOMER_ID IS NOT NULL THEN TBLB.QTY ELSE TBLB.CAL_QTY END
WHERE TBLA.CUSTOMER_ID = TBLB.CUSTOMER_ID
AND TBLA.CUSTOMER_ID = TBLC.CUSTOMER_ID
AND TBLA.PRODUCT = TBLB.PRODUCT
AND TBLA.DATE_ = TBLB.DATE_;

Hope it helps...