    
Onlyniru3
Mudiripoyina Bewarse
Username: Onlyniru3
Post Number: 17172
Registered: 08-2009
Posted From: 216.105.250.50
| | Posted on Wednesday, February 20, 2013 - 10:51 am: | 
|
SELECT a.rspns_id,
a.srvy_id,
a.chnl_id,
a.client_id,
a.srvy_dt_id,
sub3.applied_segment,
sub3.calculated_segment,
CASE WHEN (sub2.start_bal NOTNULL) THEN sub2.start_bal
WHEN (0 NOTNULL) THEN '0'
ELSE NULL
END AS start_bal,
CASE WHEN (sub1.ttm_nna NOTNULL) THEN sub1.ttm_nna WHEN (0 NOTNULL) THEN '0' ELSE NULL END AS ttm_nna,
CASE WHEN (sub1.ttm_rev NOTNULL) THEN sub1.ttm_rev WHEN (0 NOTNULL) THEN '0' ELSE NULL END AS ttm_rev,
CASE WHEN (sub1.ttm_trades NOTNULL) THEN sub1.ttm_trades WHEN (0 NOTNULL) THEN '0' ELSE NULL END AS ttm_trades,
CASE WHEN (sub1.ttm_option_trd NOTNULL) THEN sub1.ttm_option_trd WHEN (0 NOTNULL) THEN '0' ELSE NULL END AS ttm_option_trd,
CASE WHEN (sub1.ttm_mutual_fund_trd NOTNULL) THEN sub1.ttm_mutual_fund_trd WHEN (0 NOTNULL) THEN '0' ELSE NULL END AS ttm_mutual_fund_trd,
CASE WHEN (sub1.ttm_equity_trd NOTNULL) THEN sub1.ttm_equity_trd WHEN (0 NOTNULL) THEN '0' ELSE NULL END AS ttm_equity_trd,
CASE WHEN (sub1.ttm_fixed_income_trd NOTNULL) THEN sub1.ttm_fixed_income_trd WHEN (0 NOTNULL) THEN '0' ELSE NULL END AS ttm_fixed_income_trd,
CASE WHEN (sub1.ttm_other_security_trd NOTNULL) THEN sub1.ttm_other_security_trd WHEN (0 NOTNULL) THEN '0' ELSE NULL END AS ttm_other_security_trd,
CASE WHEN (sub2.web_login_days_prior_mth NOTNULL) THEN "numeric"(sub2.web_login_days_prior_mth) WHEN (0 NOTNULL) THEN '0' ELSE NULL END AS web_logins_prior_mth
FROM (
SELECT sub1a.rspns_id, sub1a.srvy_id, sub1a.chnl_id, sub1a.client_id,sub1a.srvy_dt_id, sub1b.cmfmid, sub1b.fmidl1, sub1b.fmidl12, sub1b.dt_nm
FROM edw_admin..client_srvy_rspns sub1a
JOIN (select a2.time_id ,a2.dt_nm ,a2.fscl_mth_id as cmfmid ,cast(to_char(add_months(a2.dt_nm,+2),'YYYYMM') as integer) as fmidl1 ,cast(to_char(add_months(a2.dt_nm,-9),'YYYYMM') as integer) as fmidl12 FROM edw_admin..vw_ref_tm_mst a2 where a2.dt_nm > current_date-770
and a2.dt_nm < current_date) sub1b ON (sub1a.srvy_dt_id = sub1b.time_id)) a
LEFT JOIN (
SELECT sub1a.rspns_id, SUM(e1.net_new_assets) AS ttm_nna, SUM(e1.revenue) AS ttm_rev, SUM(e1.trades) AS ttm_trades, SUM(e1.option_trd) AS ttm_option_trd, SUM(e1.mutual_fund_trd) AS ttm_mutual_fund_trd, SUM(e1.equity_trd) AS ttm_equity_trd, SUM(e1.fixed_income_trd) AS ttm_fixed_income_trd, SUM(e1.other_security_trd) AS ttm_other_security_trd
FROM (
SELECT a1.rspns_id, a1.client_id, b1.acct_id, a1.fmidl1, a1.fmidl12
FROM (
SELECT sub1a.rspns_id, sub1a.srvy_id, sub1a.chnl_id, sub1a.client_id, sub1a.srvy_dt_id, sub1b.cmfmid, sub1b.fmidl1, sub1b.fmidl12, sub1b.dt_nm
FROM edw_admin..client_srvy_rspns sub1a JOIN (select a2.time_id,a2.dt_nm ,a2.fscl_mth_id as cmfmid,cast(to_char(add_months(a2.dt_nm,+2),'YYYYMM') as integer) as fmidl1 ,cast(to_char(add_months(a2.dt_nm,-9),'YYYYMM') as integer) as fmidl12
FROM edw_admin..vw_ref_tm_mst a2 where a2.dt_nm > current_date-770 and a2.dt_nm < current_date
) sub1b ON (sub1a.srvy_dt_id = sub1b.time_id) ) a1 JOIN edw_admin..acct_client_hist b1 ON (a1.client_id = b1.client_id AND b1.curr_ind = 'Y') JOIN edw_admin..acct c1 ON (b1.acct_id = c1.acct_id) JOIN edw_admin..vw_ref_ofc_cd d1 ON (c1.ofc_cd = d1.ofc_cd AND d1.client_group_nm = 'Retail')
) sub1a
JOIN edw_admin..vw_crdm_fact_acct_kpis_month e1 ON (sub1a.acct_id = e1.account_id AND e1.fiscal_month_id >= sub1a.fmidl12 AND e1.fiscal_month_id <= sub1a.fmidl1)
GROUP BY sub1a.rspns_id
) sub1
ON (a.rspns_id = sub1.rspns_id)
LEFT JOIN (
SELECT sub2a.rspns_id, SUM(e2.end_liquidation_bal) AS start_bal, MAX(e2.acct_ttl_days_web_used) AS web_login_days_prior_mth
FROM (
SELECT a2.rspns_id, a2.client_id, b2.acct_id, a2.fmidl1, a2.fmidl12
FROM (
SELECT sub1a.rspns_id, sub1a.srvy_id, sub1a.chnl_id, sub1a.client_id, sub1a.srvy_dt_id, sub1b.cmfmid, sub1b.fmidl1, sub1b.fmidl12, sub1b.dt_nm
FROM edw_admin..client_srvy_rspns sub1a
JOIN (
select a2.time_id ,a2.dt_nm ,a2.fscl_mth_id as cmfmid ,cast(to_char(add_months(a2.dt_nm,+2),'YYYYMM') as integer) as fmidl1
,cast(to_char(add_months(a2.dt_nm,-9),'YYYYMM') as integer) as fmidl12 FROM edw_admin..vw_ref_tm_mst a2 where a2.dt_nm > current_date-770 and a2.dt_nm < current_date
) sub1b ON (sub1a.srvy_dt_id = sub1b.time_id)) a2
JOIN edw_admin..acct_client_hist b2 ON (a2.client_id = b2.client_id AND b2.curr_ind = 'Y')
JOIN edw_admin..acct c2 ON (b2.acct_id = c2.acct_id)
JOIN edw_admin..vw_ref_ofc_cd d2 ON (c2.ofc_cd = d2.ofc_cd AND d2.client_group_nm = 'Retail')) sub2a
JOIN edw_admin..vw_cidm_fact_acct_kpis_month e2 ON (sub2a.acct_id = e2.account_id AND sub2a.fmidl1 = e2.fiscal_month_id)
GROUP BY sub2a.rspns_id
) sub2 ON (a.rspns_id = sub2.rspns_id)
LEFT JOIN (
SELECT aa.client_id, aa.fiscal_month_id, aa.applied_segment, aa.calculated_segment, aa.servicing_segment
FROM edw_admin..vw_ids_client_retail_segmt_month aa
WHERE cast(aa.client_id as bigint) in
(SELECT DISTINCT(cast(bb.client_id as bigint)) FROM edw_admin..client_srvy_rspns bb)) sub3 ON (a.client_id = sub3.client_id AND a.fmidl1 = sub3.fiscal_month_id);
Puri - Nana gaaru kakunda meeku istam ayina actor evaru...Mahesh - Inka evaru pedda ayana NTR garu..
|
Photo Comments 