New Member - First Post

Database

New Member - First Post

Greetings

Ive been reading this board for over a year but this is my first post.

I find this forum very useful but I was wondering about additional resources you use for this type of stuff.

Please list other relevant sites/blogs/forums that you follow

I'll go post an intro now!
3 REPLIES
Supporter

Re: New Member - First Post

http://www.teradataforum.com/

independent and has a big archive

Teradata Employee

Re: New Member - First Post

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.

Re: New Member - First Post

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_SNAPSHOT_ID =

B.CUST_DETAIL_MTHLY_SNAPSHOT_ID;