quote:

---

Originally posted by zooby

I need your help to add this column "FC_DATE_ACTUAL" which comes from this table "Foreclosure_Property" .. into this query below.

WITH vxAST_MVT(
ASSET_ID,
EVENT_TYPE,
EVENT_DATE,
EVENT_UPB,
EVENT_DESC)
AS(

SELECT DISTINCT
a.ASSET_ID,
mvt.EVENT_TYPE,
mvt.EVENT_DATE,
mvt.EVENT_UPB,
mvt.EVENT_DESC
FROM V_US_ASSET a

INNER JOIN( SELECT ASSET_ID,1 EVENT_TYPE,asset_acquisition_date EVENT_DATE,UPB_ACQ EVENT_UPB,TRANSFER_REASON EVENT_DESC
FROM V_US_ASSET WHERE asset_acquisition_date IS NOT NULL AND TRANSFER_REASON IS NOT NULL

UNION

SELECT ASSET_ID,1,ASSET_REACTIVATION_DATE,UPB_AT_REACTIVATION,REACTIVATION_TRANSFER_REASON
FROM V_US_ASSET WHERE ASSET_REACTIVATION_DATE IS NOT NULL AND REACTIVATION_TRANSFER_REASON IS NOT NULL

UNION

SELECT ASSET_ID,1,REACTIVATION_SS_TRANSFER_DATE_HISTORICAL,REACTIVATION_UPB_SS_TRANSFER_AMT_HISTORICAL,REACTIVATION_TRANSFER_REASON_HISTORICAL
FROM V_US_ASSET WHERE REACTIVATION_SS_TRANSFER_DATE_HISTORICAL IS NOT NULL AND REACTIVATION_SS_TRANSFER_DATE_HISTORICAL IS NOT NULL

UNION
SELECT ASSET_ID,-1,ACT_DISPO_DATE, UPB_DISPO, DISPO_FULL_TYPE
FROM V_US_DISPOSITIONS
UNION
SELECT ASSET_ID,-1 AS TYPES, act_tfr_master_serv_date,MOD_UPB,'RTM'
FROM V_US_RTM
UNION
SELECT vprop.ASSET_ID,0,vreo.FC_DATE_ACTUAL,vreo.REO_CONVERSION_AMT,'REO CONV'
FROM V_US_PROPERTY vprop INNER JOIN FORECLOSURE_PROPERTY vreo ON vreo.PROPERTY_SYS_ID = vprop.PROPERTY_SYS_ID)
mvt

ON a.ASSET_ID = mvt.ASSET_ID),

---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------

vxMATURITY(
ASSET_ID,
MAT_DATE,
MOD_DATE) AS
(
SELECT DISTINCT ast.ASSET_ID, amod.MOD_MATURITY_DATE, amod.ACT_MOD_DATE
FROM ASSET_MODIFICATION amod
INNER JOIN ASSET ast ON ast.ASSET_SYS_ID = amod.ASSET_SYS_ID

WHERE amod.MOD_MATURITY_DATE IS NOT NULL
AND amod.ACT_MOD_DATE IS NOT NULL
AND amod.MOD_STATUS>0

UNION

SELECT DISTINCT ast.ASSET_ID, nt.ORIG_MATURITY_DATE, -1
FROM NOTE nt
INNER JOIN ASSET ast ON ast.ASSET_SYS_ID = nt.ASSET_SYS_ID)

---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------

SELECT DISTINCT
CASE
WHEN vast.PORTFOLIO_NAME = 'CDO Capmark VII' THEN 'CDO'
ELSE vast.PORTFOLIO_TYPE
END PORT_TYPE,
REPLACE(vast.PORTFOLIO_NAME,',',';') 'Portfolio Name',
CASE
WHEN (SELECT MIN(x.EVENT_DATE) FROM vxAST_MVT x
WHERE x.ASSET_ID = vxast.ASSET_ID AND
x.EVENT_TYPE = 0 AND
x.EVENT_DATE <= vxast.EVENT_DATE) IS NOT NULL THEN 'REO'
ELSE 'LOAN'
END 'LOAN_TYPE',
vxast.ASSET_ID,
vxast.EVENT_TYPE,
vxast.EVENT_DATE,
vxast.EVENT_UPB,


CASE WHEN vxast.EVENT_TYPE = 1 THEN
CASE WHEN vxast.EVENT_DESC = 'Delinquent Monthly or Balloon Loan Payment' THEN
CASE
WHEN (SELECT MAX(MAT_DATE)
FROM vxMATURITY t2 WHERE t2.ASSET_id=vxast.ASSET_ID
AND t2.MOD_DATE<vxast.EVENT_DATE) < vxast.EVENT_DATE THEN 'Maturity Default'

WHEN (SELECT MAX(MAT_DATE)
FROM vxMATURITY t2 WHERE t2.ASSET_id=vxast.ASSET_ID
AND t2.MOD_DATE<vxast.EVENT_DATE) > vxast.EVENT_DATE

AND ((SELECT MAX(MAT_DATE)
FROM vxMATURITY t2 WHERE t2.ASSET_id=vxast.ASSET_ID
AND t2.MOD_DATE<vxast.EVENT_DATE) - 100) < vxast.EVENT_DATE THEN 'Imminent Maturity Default'

ELSE 'Delinquent Monthly Payment'
END
WHEN vxast.EVENT_DESC = 'Involuntary Bankruptcy or Appointment of Receiver' THEN 'Bankruptcy'
WHEN vxast.EVENT_DESC = 'Commencement of Foreclosure' THEN 'Imminent Default'
WHEN vxast.EVENT_DESC = 'Admission from Borrower Inability to Pay Loan' THEN 'Imminent Default'
WHEN vxast.EVENT_DESC = 'Non-Monetary Default Materially Affects Interests of Certificate holders' THEN 'Non Monetary Default'
WHEN vxast.EVENT_DESC = 'Non-Monetary Default Materially Impairs Value of Property' THEN 'Non Monetary Default'
WHEN vxast.EVENT_DESC = 'Servicing Transfer' THEN 'Servicing Transfer'
WHEN vxast.EVENT_DESC = 'Monetary Default Likely to Occur' THEN
CASE
WHEN (SELECT MAX(MAT_DATE)
FROM vxMATURITY t2 WHERE t2.ASSET_id=vxast.ASSET_ID
AND t2.MOD_DATE<vxast.EVENT_DATE) > vxast.EVENT_DATE

AND ((SELECT MAX(MAT_DATE)
FROM vxMATURITY t2 WHERE t2.ASSET_id=vxast.ASSET_ID
AND t2.MOD_DATE<vxast.EVENT_DATE) - 100) < vxast.EVENT_DATE THEN 'Imminent Maturity Default'
ELSE 'Imminent Default'
END
ELSE vxast.EVENT_DESC
END
ELSE vxast.EVENT_DESC
END 'EVENT_DESC',

(SELECT MAX(MAT_DATE)
FROM vxMATURITY t2
WHERE t2.ASSET_id=vxast.ASSET_ID
AND t2.MOD_DATE<vxast.EVENT_DATE) 'MATURITY',
p.CURR_APP_VALUE,
p.CURR_APP_DATE


FROM vxAST_MVT vxast

LEFT OUTER JOIN ASSET ast
ON vxast.ASSET_ID = vxast.ASSET_ID

LEFT OUTER JOIN V_US_ASSET vast
ON vxast.ASSET_ID = vast.ASSET_ID

LEFT OUTER JOIN NOTE nt
ON ast.ASSET_SYS_ID = nt.ASSET_SYS_ID

left outer join V_US_PROPERTY p
on p.ASSET_ID = vxast.ASSET_ID

WHERE
vxast.EVENT_DATE>= '12/31/2010' AND
vxast.EVENT_DATE<='9/30/2011'and
vxast.event_type = -1 and
vxast.event_desc = 'REO Sale'

ORDER BY vxast.EVENT_DATE

---

WITH vxAST_MVT(
ASSET_ID,
EVENT_TYPE,
EVENT_DATE,
EVENT_UPB,
EVENT_DESC,
FC_ACTUAL_DATE) 
AS(

SELECT DISTINCT
a.ASSET_ID,
mvt.EVENT_TYPE,
mvt.EVENT_DATE,
mvt.EVENT_UPB,
mvt.EVENT_DESC,
mvt.FC_ACTUAL_DATE

FROM V_US_ASSET a

INNER JOIN(	SELECT ASSET_ID,1 EVENT_TYPE,asset_acquisition_date EVENT_DATE,CAST(NULL AS datetime) AS FC_ACTUAL_DATE,UPB_ACQ EVENT_UPB,TRANSFER_REASON EVENT_DESC
FROM V_US_ASSET WHERE asset_acquisition_date IS NOT NULL AND TRANSFER_REASON IS NOT NULL

UNION

SELECT ASSET_ID,1,ASSET_REACTIVATION_DATE,NULL,UPB_AT_REACTIVATION,REACTIVATION_TRANSFER_REASON
FROM V_US_ASSET WHERE ASSET_REACTIVATION_DATE IS NOT NULL AND REACTIVATION_TRANSFER_REASON IS NOT NULL

UNION

SELECT ASSET_ID,1,REACTIVATION_SS_TRANSFER_DATE_HISTORICAL,NULL,REACTIVATION_UPB_SS_TRANSFER_AMT_HISTORICAL,REACTIVATION_TRANSFER_REASON_HISTORICAL
FROM V_US_ASSET WHERE REACTIVATION_SS_TRANSFER_DATE_HISTORICAL IS NOT NULL AND REACTIVATION_SS_TRANSFER_DATE_HISTORICAL IS NOT NULL

UNION
SELECT ASSET_ID,-1,ACT_DISPO_DATE,NULL, UPB_DISPO, DISPO_FULL_TYPE
FROM V_US_DISPOSITIONS 
UNION
SELECT ASSET_ID,-1 AS TYPES, act_tfr_master_serv_date,NULL,MOD_UPB,'RTM'	
FROM V_US_RTM
UNION 
SELECT vprop.ASSET_ID,0,NULL,vreo.FC_DATE_ACTUAL,vreo.REO_CONVERSION_AMT,'REO CONV'
FROM V_US_PROPERTY vprop INNER JOIN FORECLOSURE_PROPERTY vreo ON vreo.PROPERTY_SYS_ID = vprop.PROPERTY_SYS_ID)
mvt

ON a.ASSET_ID = mvt.ASSET_ID),

---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------

vxMATURITY(
ASSET_ID,
MAT_DATE,
MOD_DATE) AS
(
SELECT DISTINCT	ast.ASSET_ID, amod.MOD_MATURITY_DATE, amod.ACT_MOD_DATE
FROM ASSET_MODIFICATION amod 
INNER JOIN ASSET ast ON ast.ASSET_SYS_ID = amod.ASSET_SYS_ID

WHERE amod.MOD_MATURITY_DATE IS NOT NULL 
AND amod.ACT_MOD_DATE IS NOT NULL
AND amod.MOD_STATUS>0

UNION

SELECT DISTINCT ast.ASSET_ID, nt.ORIG_MATURITY_DATE, -1
FROM NOTE nt
INNER JOIN ASSET ast ON ast.ASSET_SYS_ID = nt.ASSET_SYS_ID)

---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------

SELECT DISTINCT
CASE 
WHEN vast.PORTFOLIO_NAME = 'CDO Capmark VII' THEN 'CDO'
ELSE vast.PORTFOLIO_TYPE 
END	PORT_TYPE,
REPLACE(vast.PORTFOLIO_NAME,',',';') 'Portfolio Name',
CASE 
WHEN (SELECT MIN(x.EVENT_DATE) FROM vxAST_MVT x
WHERE x.ASSET_ID = vxast.ASSET_ID AND
x.EVENT_TYPE = 0 AND
x.EVENT_DATE <= vxast.EVENT_DATE) IS NOT NULL THEN 'REO'
ELSE 'LOAN'
END	 'LOAN_TYPE',
vxast.ASSET_ID,
vxast.EVENT_TYPE,	
vxast.EVENT_DATE,
vxast.EVENT_UPB,
vxast.FC_ACTUAL_DATE,
CASE WHEN vxast.EVENT_TYPE = 1 THEN
CASE WHEN vxast.EVENT_DESC = 'Delinquent Monthly or Balloon Loan Payment' THEN
CASE
WHEN (SELECT MAX(MAT_DATE)
FROM vxMATURITY t2 WHERE t2.ASSET_id=vxast.ASSET_ID
AND t2.MOD_DATE<vxast.EVENT_DATE) < vxast.EVENT_DATE	 THEN 'Maturity Default'

WHEN (SELECT MAX(MAT_DATE)
FROM vxMATURITY t2 WHERE t2.ASSET_id=vxast.ASSET_ID
AND t2.MOD_DATE<vxast.EVENT_DATE) > vxast.EVENT_DATE

AND ((SELECT MAX(MAT_DATE)
FROM vxMATURITY t2 WHERE t2.ASSET_id=vxast.ASSET_ID
AND t2.MOD_DATE<vxast.EVENT_DATE) - 100) < vxast.EVENT_DATE	 THEN 'Imminent Maturity Default'

ELSE 'Delinquent Monthly Payment'
END
WHEN vxast.EVENT_DESC = 'Involuntary Bankruptcy or Appointment of Receiver'	 THEN 'Bankruptcy'	
WHEN vxast.EVENT_DESC = 'Commencement of Foreclosure'	 THEN 'Imminent Default'
WHEN vxast.EVENT_DESC = 'Admission from Borrower Inability to Pay Loan'	 THEN 'Imminent Default'
WHEN vxast.EVENT_DESC = 'Non-Monetary Default Materially Affects Interests of Certificate holders' THEN 'Non Monetary Default'
WHEN vxast.EVENT_DESC = 'Non-Monetary Default Materially Impairs Value of Property'	 THEN 'Non Monetary Default'
WHEN vxast.EVENT_DESC = 'Servicing Transfer'	 THEN 'Servicing Transfer'
WHEN vxast.EVENT_DESC = 'Monetary Default Likely to Occur' THEN 
CASE
WHEN (SELECT MAX(MAT_DATE)
FROM vxMATURITY t2 WHERE t2.ASSET_id=vxast.ASSET_ID
AND t2.MOD_DATE<vxast.EVENT_DATE) > vxast.EVENT_DATE

AND ((SELECT MAX(MAT_DATE)
FROM vxMATURITY t2 WHERE t2.ASSET_id=vxast.ASSET_ID
AND t2.MOD_DATE<vxast.EVENT_DATE) - 100) < vxast.EVENT_DATE	 THEN 'Imminent Maturity Default'
ELSE 'Imminent Default'
END
ELSE vxast.EVENT_DESC 
END
ELSE vxast.EVENT_DESC
END	 'EVENT_DESC',

(SELECT MAX(MAT_DATE) 
FROM vxMATURITY t2 
WHERE t2.ASSET_id=vxast.ASSET_ID
AND t2.MOD_DATE<vxast.EVENT_DATE)	 'MATURITY',
p.CURR_APP_VALUE,
p.CURR_APP_DATE


FROM vxAST_MVT vxast

LEFT OUTER JOIN ASSET ast
ON vxast.ASSET_ID = vxast.ASSET_ID

LEFT OUTER JOIN V_US_ASSET vast
ON vxast.ASSET_ID = vast.ASSET_ID

LEFT OUTER JOIN NOTE nt
ON ast.ASSET_SYS_ID = nt.ASSET_SYS_ID

left outer join V_US_PROPERTY p
on p.ASSET_ID = vxast.ASSET_ID

WHERE 
vxast.EVENT_DATE>= '12/31/2010' AND
vxast.EVENT_DATE<='9/30/2011'and
vxast.event_type = -1 and
vxast.event_desc = 'REO Sale'

ORDER BY vxast.EVENT_DATE

quote:

---

Originally posted by rajarajan

Visaksh I go with wat u say ,coz when u see the query. zooby has use union in the query but basic when we use union all columns should be same . correct me if I go wrong.

Regards,
Rajan

---

quote:

---

Originally posted by zooby

How are you doing visakh? I need your help with something please, I am using SQL server 2008 and every week I need to run a query and copy and paste the data into excel. Could you please explain to me how I can go about automating that process? I am using MS Office 2003, and I know that you can automate this process using macros. But I am just not quite sure how to go about this process. Your help is greatly appreciated!

---

quote:

---

Originally posted by zooby

Thanks again for your quick reply, Visakh would you mind if I get your email address so I may email you progress of my work if I run into an issue?

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by zooby

Thanks again for your quick reply, Visakh would you mind if I get your email address so I may email you progress of my work if I run into an issue?

---

No problem
you're welcome
You can post whatever problems you face in forum itself. It will increase the chance of getting quick solutions as there are lot of people in this forum who are much smarter and would give expert advice to you even if I'm not able to see your reply sooner.
Also as Brett pointed out you might be better off posting new unrelated questions in separate threads which will give oppurtunity for people to look and answer soon.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---