quote:

---

select isnull((datediff(d,db_stat_date,max(value_date))),0) vdpd
from dbo.SRC_HTD_TABLE a
,dbo.SRC_GCT gct
,dbo.SRC_ATD atd
,(select
b.tran_date
,b.tran_id
from dbo.SRC_HTD_TABLE b,dbo.SRC_GAM gam
where gam.acid=b.acid
and schm_code in ('OD218','OD220','OD221','OD222','OD223','OD224','OD225','OD226')
and b.part_tran_type= 'D'
and b.pstd_flg='Y' and b.del_flg='N') b
,(
select
tran_date
,tran_id
,part_tran_srl_num
,acid
from dbo.SRC_OCP ocp
where
clg_zone_code in ('IWRET1','IWRET2')
) ocp
,(
select
ocp.acid
,tran_date
,tran_id
from
dbo.SRC_ICI ici
,dbo.SRC_OCP ocp
where ici.acid=ocp.acid
and clg_zone_code=out_rej_zone_code
and clg_zone_date=out_rej_zone_date
and rtrim(ltrim(out_rej_set_num))=rtrim(ltrim(set_num))
and ocp.sol_id=out_rej_sol_id
and tran_amt=inst_amt
) ocpici
where a.acid=@vacid
and ((a.tran_date != atd.tran_date) and (a.tran_id != atd.tran_id) and (a.part_tran_srl_num != atd.part_tran_srl_num))
and ((a.tran_date != b.tran_date) and (a.tran_id != b.tran_id))
and ((ocp.acid=a.acid) and (ocp.tran_date!=a.tran_date) AND ( ocp.tran_id != a.tran_id ) AND (ocp.part_tran_srl_num != a.part_tran_srl_num))
and ((a.acid != ocpici.acid) and (a.tran_date != ocpici.tran_date) and (a.tran_id != ocpici.tran_id))
and tran_sub_type in ('CI','BS','BI','NR','O')
and (tran_particular not like 'FTR%' and tran_particular not like 'REV%' and tran_particular not like '%: Closure Proceeds' and tran_particular not like '%Disbursement Credit' and tran_particular not like 'REJECT:%')
and part_tran_type = 'C'
and a.pstd_flg='Y'
and a.del_flg='N'
group by gct.db_stat_date
OPTION (MAXDOP 20)

---

SELECT		ISNULL((DATEDIFF(DAY, {missing column alias here}.db_stat_date, max({missing column alias here}.value_date))), 0) AS vdpd 
FROM		dbo.SRC_HTD_TABLE AS a,
		dbo.SRC_GCT AS gct,
		dbo.SRC_ATD AS atd,
		(
			SELECT		b.tran_date,
					b.tran_id 
			FROM		dbo.SRC_HTD_TABLE AS b
			INNER JOIN	dbo.SRC_GAM AS gam ON gam.acid = b.acid
			WHERE		{missing column alias here}.schm_code in ('OD218', 'OD220', 'OD221', 'OD222', 'OD223', 'OD224', 'OD225', 'OD226')
					and b.part_tran_type = 'D'
					and b.pstd_flg = 'Y'
					and b.del_flg = 'N'
		) AS b,
		(
			SELECT	tran_date,
				tran_id,
				part_tran_srl_num,
				acid
			FROM	dbo.SRC_OCP
			WHERE	clg_zone_code IN ('IWRET1', 'IWRET2')
		) AS ocp,
		(
			SELECT		ocp.acid,
					{missing column alias here}.tran_date,
					{missing column alias here}.tran_id 
			FROM		dbo.SRC_ICI AS ici
			INNER JOIN	dbo.SRC_OCP AS ocp ON ocp.acid = ici.acid
			WHERE		{missing column alias here}.clg_zone_code = {missing column alias here}.out_rej_zone_code
					AND {missing column alias here}.clg_zone_date = {missing column alias here}.out_rej_zone_date
					AND RTRIM(LTRIM({missing column alias here}.out_rej_set_num)) = RTRIM(LTRIM({missing column alias here}.set_num))
					AND ocp.sol_id = {missing column alias here}.out_rej_sol_id
					AND {missing column alias here}.tran_amt = {missing column alias here}.inst_amt
		) AS ocpici
WHERE		a.acid = @vacid 
		AND a.tran_date != atd.tran_date
		AND a.tran_id != atd.tran_id
		AND a.part_tran_srl_num != atd.part_tran_srl_num
		AND a.tran_date != b.tran_date
		AND a.tran_id != b.tran_id
		AND ocp.acid = a.acid
		AND ocp.tran_date != a.tran_date
		AND ocp.tran_id != a.tran_id
		AND ocp.part_tran_srl_num != a.part_tran_srl_num
		AND a.acid != ocpici.acid
		AND a.tran_date != ocpici.tran_date
		AND a.tran_id != ocpici.tran_id
		AND {missing column alias here}.tran_sub_type in ('CI', 'BS', 'BI', 'NR', 'O')
		AND {missing column alias here}.tran_particular not like 'FTR%'
		AND {missing column alias here}.tran_particular not like 'REV%'
		AND {missing column alias here}.tran_particular not like '%: Closure Proceeds'
		AND {missing column alias here}.tran_particular not like '%Disbursement Credit'
		AND {missing column alias here}.tran_particular not like 'REJECT:%'
		AND {missing column alias here}.part_tran_type = 'C'
		AND a.pstd_flg = 'Y'
		AND a.del_flg = 'N'
GROUP BY	gct.db_stat_date
OPTION		(MAXDOP 20) 

quote:

---

SELECT [acid]
,[foracid]
,[acct_name]
,[schm_code]
,[clr_bal_amt]
,[cust_id]
,[sanct_lim]
,[acct_crncy_code]
,[sol_id]
,[free_code_3]
,[DATE_SANCT_LIM_MAINTANED]
FROM [dbo].[tmp_list]

---

quote:

---

SELECT [acid]
,[foracid]
,[acct_name]
,[schm_code]
,[clr_bal_amt]
,[cust_id]
,[sanct_lim]
,[acct_crncy_code]
,[sol_id]
,[free_code_3]
,[DATE_SANCT_LIM_MAINTANED]
,dwf_fn(acid) amounts_c
FROM [dbo].[tmp_list]

---

SELECT		ISNULL((DATEDIFF(DAY, gct.db_stat_date, MAX(a.value_date))), 0) AS vdpd 
FROM		dbo.SRC_HTD_TABLE AS a
CROSS JOIN	dbo.SRC_GCT AS gct
INNER JOIN	dbo.SRC_ATD AS atd ON atd.tran_date <> a.tran_date
			AND atd.tran_id <> a.tran_id
			AND atd.part_tran_srl_num <> a.part_tran_srl_num
INNER JOIN	dbo.SRC_HTD_TABLE AS b ON b.tran_date <> a.tran_date
			AND b.tran_id <> a.tran_id
			AND b.part_tran_type = 'D'
			AND b.pstd_flg = 'Y'
			AND b.del_flg = 'N'
INNER JOIN	dbo.SRC_GAM AS gam ON gam.acid = b.acid
			AND gam.schm_code IN ('OD218', 'OD220', 'OD221', 'OD222', 'OD223', 'OD224', 'OD225', 'OD226')
INNER JOIN	dbo.SRC_OCP AS ocp ON ON ocp.acid = a.acid
			AND ocp.tran_date <> a.tran_date
			AND ocp.tran_id <> a.tran_id
			AND ocp.part_tran_srl_num <> a.part_tran_srl_num
			AND ocp.clg_zone_code IN ('IWRET1', 'IWRET2')
			AND ocp.acid <> a.acid
			AND ocp.tran_date <> a.tran_date
			AND ocp.tran_id <> a.tran_id
INNER JOIN	dbo.SRC_ICI AS ici ON ici.acid = ocp.acid
			AND ici.out_rej_zone_code = ocp.clg_zone_code
			AND ici.out_rej_zone_date = ocp.clg_zone_date
			AND LTRIM(ici.set_num) = LTRIM(ocp.out_rej_set_num)
			AND ici.out_rej_sol_id = ocp.sol_id
			AND ici.inst_amt = ocp.tran_amt
WHERE		a.acid = @vACID
		AND a.tran_sub_type IN ('CI', 'BS', 'BI', 'NR', 'O')
		AND a.tran_particular NOT LIKE 'FTR%'
		AND a.tran_particular NOT LIKE 'REV%'
		AND a.tran_particular NOT LIKE '%: Closure Proceeds'
		AND a.tran_particular NOT LIKE '%Disbursement Credit'
		AND a.tran_particular NOT LIKE 'REJECT:%'
		AND a.part_tran_type = 'C'
		AND a.pstd_flg = 'Y'
		AND a.del_flg = 'N'
GROUP BY	gct.db_stat_date
OPTION		(MAXDOP 20)