How to update multiple columns in one table based on if else condition

Database

How to update multiple columns in one table based on if else condition

Hi all,

pls let me Know i f any body having any idea on below Issue.

Issue:

The connect date in the NDW_BASE_TABLES for a subscriber is truncated to the 1st of the month. So if a sub  connected on the 8th of this month (07/08/2012) then his connect date will show as 07/01/2012. Due to this, the subscriber overall connects are fine however the separation in New connect and Reconnect subs does not work correctly.

We need to do the following:

- Monthly Historically Oct 2011 and going forward  (3 scripts -  1 for historical (new), one for etl load (change to existing) and one for catch-up(change to existing). Start with historical)

Update the detail table and bring it back to original form. So basically set all the reconnect_cust_cnt  =  new_cust_cnt and reconnect_cust_rev = new_cust_rev. Next set reconnect cust cnt and reconnect_cust_rev as zero.

Then update the detail table again where the logic is if connect date is less than beginning of month date (1st of the month)  then Reconnect (Cnt and rev) else New connect (Cnt and rev)

Example:

For June 2012 month (May 22nd to June 21st)

Any subscriber having connect date less than June 1st is a Reconnect else they are new connect.

For July 2012 month (June 22nd to July 21st)

Any subscriber having connect date less than July 1st is a Reconnect else they are new connect.

That logic will replace the bold highlighted below

-- SEPERATE NEWCONNECTS AND RECONNECTS

UPDATE NDW_ANALYTIC_TABLES.CUST_DETAIL_MTHLY_SNAPSHOT_A

FROM (

SELECT Customer_Account_id, CUST_DETAIL_MTHLY_SNAPSHOT_ID FROM

NDW_ANALYTIC_TABLES.CUST_DETAIL_MTHLY_SNAPSHOT_A x

WHERE x.FSCL_MONTH_ID = (

SELECT y.PERIOD_ID FROM VT_COENDMONTH_DT Y)

and x.NEW_CUST_CNT = 1

AND x.Customer_Account_id  IN

(SELECT DISTINCT (y.Customer_Account_id) FROM &TTDDBASEVW.CUSTOMER_ACCOUNT y

CROSS JOIN VT_COENDMONTH_DT

WHERE Customer_Account_Connect_Date < Fiscal_Month_Start_Date)

)  B

SET NEW_CUST_CNT = 0,

NEW_CUST_REV = 0.00

WHERE CUST_DETAIL_MTHLY_SNAPSHOT_A.Customer_Account_id = B.Customer_Account_id

and CUST_DETAIL_MTHLY_SNAPSHOT_A.CUST_DETAIL_MTHLY_SNAPSHO T_ID =

B.CUST_DETAIL_MTHLY_SNAPSHOT_ID;

Tags (1)
3 REPLIES
N/A

Re: How to update multiple columns in one table based on if else condition

Sorry, i don't understand what you actually want.

Might be replacing the WHERE condition with a CASE in the SET, like:

set col = case when Customer_Account_Connect_Date < Fiscal_Month_Start_Date then xxx else yyy end

Dieter

Re: How to update multiple columns in one table based on if else condition

use case statement

Regards,

Re: How to update multiple columns in one table based on if else condition

Hi Dieter,

I want to modify the below update statement(bold highlighted below) .

Issue is  if connect date is less than beginning of month date (1st of the month)  then Reconnect (Cnt and rev) else New connect (Cnt and rev)

Example:

For June 2012 month (May 22nd to June 21st)

Any subscriber having connect date less than June 1st is a Reconnect else they are new connect.

Statement:

UPDATE NDW_ANALYTIC_TABLES.CUST_DETAIL_MTHLY_SNAPSHOT_A

FROM (

SELECT Customer_Account_id, CUST_DETAIL_MTHLY_SNAPSHOT_ID FROM

NDW_ANALYTIC_TABLES.CUST_DETAIL_MTHLY_SNAPSHOT_A x

WHERE x.FSCL_MONTH_ID = (

SELECT y.PERIOD_ID FROM VT_COENDMONTH_DT Y)

and x.NEW_CUST_CNT = 1

AND x.Customer_Account_id  IN

(SELECT DISTINCT (y.Customer_Account_id) FROM &TTDDBASEVW.CUSTOMER_ACCOUNT y

CROSS JOIN VT_COENDMONTH_DT

WHERE Customer_Account_Connect_Date < Fiscal_Month_Start_Date)

)  B

SET NEW_CUST_CNT = 0,

NEW_CUST_REV = 0.00

WHERE CUST_DETAIL_MTHLY_SNAPSHOT_A.Customer_Account_id = B.Customer_Account_id

and CUST_DETAIL_MTHLY_SNAPSHOT_A.CUST_DETAIL_MTHLY_SNAPSHO T_ID =

B.CUST_DETAIL_MTHLY_SNAPSHOT_ID;