Welcome to the official Teradata forum....glad you made it here! :-) The Developers’ forums are growing really quickly and gaining momentum. However, sometimes I have to just "google" the topic I want to research to get results from technical / programming forums or Teradata documentation.
pls let me Know i f any body having any idea on below 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)
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
SELECT Customer_Account_id, CUST_DETAIL_MTHLY_SNAPSHOT_ID FROM
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)
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_SNAPSHOT_ID =