Query taking more 10 mins
One of the query is taking long time to execute. Its taking more than 10 mins – 15 mins.
There is no baseline for this query but checking if we can optimize this query and bring down the execution time.
Thank you.
Complete query:
SELECT
C0 AS Institution_Number,
C1 AS Portfolio,
C2 AS DDA_Account,
C3 AS Branch_Number,
C4 AS Branch_Description,
C5 AS Class_Code,
C6 AS Class_Code_Desc,
C7 AS Product_Number,
C8 AS Product_Description,
C9 AS Responsibility_Code,
C10 AS Responsibility_Code_Desc,
C11 AS Statement_Rate,
C12 AS Opened_By_Responsibility_Code,
C13 AS Opened_By_Responsibility_Code_Desc,
C14 AS Miscellaneous_Code,
C15 AS Miscellaneous_Code_Desc,
C16 AS Reg_D_Account_Flag,
C17 AS Reg_D_Exception_Flag,
C18 AS New_Account_Flag,
C19 AS New_Reporting_Flag,
C20 AS Notification_Code,
C21 AS Account_Type_Code,
C22 AS Status_Code,
C23 AS Handling_Code,
C24 AS Employee_Officer_Director,
C25 AS Accounting_Group,
C26 AS Accounting_Group_Desc,
C27 AS Cost_Center,
C28 AS Cost_Center_Desc,
C29 AS Deposit_Rate_Index,
C30 AS Closed_Account_Flag,
C31 AS Account_Type,
C32 AS Date_Opened,
C33 AS Date_Closed,
C34 AS Date_Last_Updated,
C35 AS Date_Accrued_Thru,
C36 AS Current_Balance,
C37 AS Initial_Deposit,
C38 AS Closing_Balance,
C39 AS Effective_Interest_Rate,
C40 AS Curr_MTD_Service_Charges,
C41 AS Curr_MTD_Service_Charges_Waived,
C42 AS Curr_MTD_Other_Charges,
C43 AS Curr_MTD_Other_Charges_Waived,
C44 AS Curr_MTD_Interest_Earned,
C45 AS Curr_MTD_Interest_Paid,
C46 AS Curr_Average_Balance,
C47 AS Prev_MTD_Service_Charges,
C48 AS Prev_MTD_Service_Charge_Waived,
C49 AS Prev_MTD_Other_Charges,
C50 AS Prev_MTD_Other_Charges_Waived,
C51 AS Prev_MTD_Interest_Earned,
C52 AS Prev_MTD_Interest_Paid,
C53 AS Prev_Average_Balance,
C54 AS Curr_YTD_Service_Charges,
C55 AS Curr_YTD_Service_Charge_Waived,
C56 AS Curr_YTD_Other_Charges,
C57 AS Curr_YTD_Other_Charges_Waived,
C58 AS Curr_YTD_Interest_Earned,
C59 AS Curr_YTD_Interest_Paid,
C60 AS Curr_YTD_Average_Balance,
C61 AS Prev_YTD_Serv_Chrg,
C62 AS Prev_YTD_Serv_Chrg_Waived,
C63 AS Prev_YTD_Other_Charges,
C64 AS Prev_YTD_Other_Charges_Waived,
C65 AS Prev_YTD_Interest_Earned,
C66 AS Prev_YTD_Interest_Paid,
C67 AS Prev_YTD_Average_Balance,
C68 AS Curr_YTD_Aggregate_Balance,
C69 AS Curr_YTD_Aggregate_Avail_Balance,
C70 AS Curr_YTD_Days,
C71 AS Prev_YTD_Aggregate_Balance,
C72 AS Prev_YTD_Aggregate_Avail_Balance,
C73 AS Prev_YTD_Days,
C74 AS Collateral_Pledged_Code,
C75 AS Collateral_Pledged_Code_Desc,
C76 AS Referral_Responsibility_Code,
C77 AS Referral_Responsibility_Code_Desc,
C78 AS Rate_Adjuster,
C79 AS Date_Last_Contact,
C80 AS Curr_MTD_Aggregate_Balance,
C81 AS Curr_MTD_Days,
C82 AS Prev_MTD_Aggregate_Balance,
C83 AS Prev_MTD_Days,
C84 AS NAICS_Code,
C85 AS NAICS_Description,
C86 AS Risk_Ranking,
C87 AS Risk_Score1,
C88 AS Risk_Score2,
C89 AS Curr_MTD_Days_OD,
C90 AS Curr_MTD_Number_Of_Days_NSF,
C91 AS Curr_MTD_Number_Of_Items_NSF,
C92 AS Curr_MTD_Number_Of_Returned_NSF,
C93 AS Prev_MTD_Days_OD,
C94 AS Prev_MTD_Number_Of_Days_NSF,
C95 AS Prev_MTD_Number_Of_Items_NSF,
C96 AS Prev_MTD_Number_Of_Returned_NSF,
C97 AS Prev_MTD_NSF_Fees_Waived,
C98 AS Curr_MTD_NSF_Fees_Waived,
C99 AS Curr_MTD_NSF_Fees_Refunded,
C100 AS Prev_MTD_NSF_Fees_Refunded,
C101 AS Curr_YTD_Days_OD,
C102 AS Curr_YTD_Number_Of_Days_NSF,
C103 AS Curr_YTD_Number_Of_Times_OD,
C104 AS Curr_YTD_Number_Of_Items_NSF,
C105 AS Curr_YTD_Number_Of_Returned_NSF,
C106 AS Prev_YTD_Days_OD,
C107 AS Prev_YTD_Number_Of_Days_NSF,
C108 AS Prev_YTD_Number_Of_Times_OD,
C109 AS Prev_YTD_Number_Of_Items_NSF,
C110 AS Prev_YTD_Number_Of_Return_NSF,
C111 AS Curr_YTD_Average_Avail_Balance,
C112 AS Prev_YTD_Average_Avail_Balance,
C113 AS Prev_YTD_NSF_Fee,
C114 AS Prev_YTD_NSF_Returned_Fee,
C115 AS Prev_YTD_NSF_Fees_Waived,
C116 AS Curr_YTD_NSF_Fee,
C117 AS Curr_YTD_NSF_Returned_Fee,
C118 AS Curr_YTD_NSF_Fees_Waived,
C119 AS Curr_YTD_Overdraft_Fee_Amount,
C120 AS Curr_YTD_Other_Miscellaneous_Fee,
C121 AS Curr_YTD_NSF_Fees_Refunded,
C122 AS Prev_YTD_Overdraft_Fee_Amount,
C123 AS Prev_YTD_Other_Miscellaneous_Fee,
C124 AS Prev_YTD_NSF_Fees_Refunded,
C125 AS [Reg_E_ATM / POS_Overdraft_Balance_Limit],
C126 AS [Reg_E_ATM / POS_Overdraft_Balance_Option],
C127 AS [Reg_E_ATM / POS_Consent_Notification_Option],
C128 AS [Reg_E_ATM / POS_Overdraft_Balance],
C129 AS Reg_E_OD_Notice_Clock,
C130 AS Reg_E_OD_Charge_Clock,
C131 AS [Reg_E_ATM / POS_Opt_In / Opt_Out_Status_Date],
C132 AS Curr_MTD_Number_Of_Deposit,
C133 AS Curr_MTD_Number_Of_Checks,
C134 AS Curr_MTD_Amount_Of_Deposits,
C135 AS Curr_MTD_Amount_Of_Checks,
C136 AS Prev_MTD_Number_Of_Deposit,
C137 AS Prev_MTD_Number_Of_Checks,
C138 AS Prev_MTD_Amount_Of_Deposits,
C139 AS Prev_MTD_Amount_Of_Checks,
C140 AS Curr_MTD_Number_Of_Reg_D_Type1,
C140 AS Curr_MTD_Number_Of_Reg_D_Type2,
C140 AS Prev_MTD_Number_Of_Reg_D_Type1,
C140 AS Prev_MTD_Number_Of_Reg_D_Type2,
C141 AS Document_Distribution_Group,
C142 AS Date_Overdrawn,
C143 AS Number_Of_Days_Overdrawn,
C144 AS ODP_Comments,
C145 AS ODP_Contact_Date,
C146 AS ODP_Contact_Method,
C147 AS ODP_Counsel_Date,
C148 AS ODP_Current_Limit_Count,
C149 AS ODP_Limit_Date,
C150 AS ODP_Notification_Option,
C151 AS ODP_Previous_Counsel_Date,
C152 AS ODP_Previous_Limit_Count,
C153 AS ODP_Previous_Limit_Date,
C154 AS ODP_Status_Date,
C155 AS ODP_Status_Option,
C156 AS Overdraft_Balance_Limit,
C157 AS Overdraft_Date_Reviewed,
C158 AS Overdraft_Limit_Option,
C159 AS Overdraft_Limit_Priority,
C160 AS Overdraft_Rating_Code,
C161 AS Overdraft_Type_Code,
C162 AS Current_YTD_ODP_Contact_Attempts,
C163 AS Previous_YTD_ODP_Contact_Attempts,
C164 AS Interest_Effective_Date,
C165 AS Compounding_Code,
C166 AS Curr_YTD_State_W_H,
C167 AS Curr_YTD_Federal_W_H,
C168 AS Prev_YTD_State_W_H,
C169 AS Prev_YTD_Federal_W_H,
C170 AS YTD_Fed_W_H_Adjusted_This_Run,
C171 AS YTD_Fed_W_H_Reported_This_Run,
C172 AS YTD_Fed_W_H_Prev_Run,
C173 AS [YTD_State_W / H_Adjusted_This_Run],
C174 AS YTD_State_W_H_Prev_Run,
C175 AS [YTD_State_W / H_Reported_This_Run],
C176 AS Tax_Name_Line,
C177 AS Tax_Name_ID,
C178 AS Name_Line_1,
C179 AS Name_ID_1,
C180 AS Name_Line_2,
C181 AS Name_ID_2,
C182 AS Name_Line_3,
C183 AS Name_ID_3,
C184 AS Address_Line,
C185 AS Address_ID
FROM
(
SELECT
DDA_ACCT.INSTITUTION_NUMBER AS C0,
DDA_ACCT.Portfolio AS C1,
DDA_ACCT.DDA_Account AS C2,
DDA_CODES.BRANCH_NUMBER AS C3,
DDA_CODES_BRANCH_DESC.BRANCH_DESCRIPTION AS C4,
DDA_CODES.CLASS_CODE AS C5,
DDA_CODES_CLASS_CODE_DESC_1.CLASS_DESCRIPTION AS C6,
DDA_CODES.PRODUCT_NUMBER AS C7,
DDA_CODES_PRODUCT_DESC.PRODUCT_DESCRIPTION AS C8,
DDA_CODES.RESP_CODE AS C9,
DDA_RESP_CODE_DESC_1.EXTERNAL_VALUE AS C10,
SUM(DDA_CODES.STATEMENT_RATE) AS C11,
DDA_CODES.OPENED_BY_RESP_CODE AS C12,
DDA_OPENED_BY_RESP_CODE_DESC_1.EXTERNAL_VALUE AS C13,
DDA_CODES.MISC_CODE AS C14,
DDA_MISC_CODE_DESC_1.EXTERNAL_VALUE AS C15,
DDA_CODES.REG_D_ACCT_FLAG AS C16,
DDA_CODES.REG_D_EXCEPTION_FLAG AS C17,
DDA_CODES.NEW_ACCT_FLAG AS C18,
DDA_CODES.NEW_REPORTING_FLAG AS C19,
DDA_CODES.NOTIFICATION_CODE AS C20,
DDA_CODES.ACCT_TYPE_CODE AS C21,
DDA_CODES.STATUS_CODE AS C22,
DDA_CODES.HANDLING_CODE AS C23,
DDA_CODES.EMPLOYEE_OFFICER_DIRECTOR AS C24,
DDA_CODES.ACCOUNTING_GROUP AS C25,
DDA_ACCOUNTING_GRP_DESC_1.EXTERNAL_VALUE AS C26,
DDA_CODES.COST_CENTER AS C27,
DDA_COST_CENTER_CODE_DESC_1.EXTERNAL_VALUE AS C28,
DDA_INTEREST.DEPOSIT_RATE_INDEX AS C29,
DDA_CODES.CLOSED_ACCT_FLAG AS C30,
1 AS C31,
DDA_ACCT.Date_Opened AS C32,
DDA_ACCT.Date_Closed AS C33,
DATEADD([DAY], – 1, DATEADD([MONTH], 1, DATEADD([DAY], – DAY(DDA_ACCT.Date_Last_Updated) + 1, DDA_ACCT.Date_Last_Updated))) AS C34,
DDA_ACCT.Date_Accrued_Thru AS C35,
SUM(DDA_ACCT.Current_Balance) AS C36,
SUM(DDA_ACCT.Initial_Deposit) AS C37,
SUM(DDA_ACCT.Closing_Balance) AS C38,
SUM(DDA_INTEREST.EFFECTIVE_INTEREST_RATE) AS C39,
SUM(DDA_MTD.CMTD_SERVICE_CHARGES) AS C40,
SUM(DDA_MTD.CMTD_SERVIVE_CHARGE_WAIVED) AS C41,
SUM(DDA_MTD.CMTD_OTHER_CHARGES) AS C42,
SUM(DDA_MTD.CMTD_OTHER_CHARGES_WAIVED) AS C43,
SUM(DDA_MTD.CMTD_INTEREST_EARNED) AS C44,
SUM(DDA_MTD.CMTD_INTEREST_PAID) AS C45,
SUM(DDA_MTD.CURRENT_AVERAGE_BALANCE) AS C46,
SUM(DDA_MTD.PMTD_SERVICE_CHARGE) AS C47,
SUM(DDA_MTD.PMTD_SERVICE_CHARGE_WAIVED) AS C48,
SUM(DDA_MTD.PMTD_OTHER_CHARGES) AS C49,
SUM(DDA_MTD.PMTD_OTHER_CHARGES_WAIVED) AS C50,
SUM(DDA_MTD.PMTD_INTEREST_EARNED) AS C51,
SUM(DDA_MTD.PMTD_INTEREST_PAID) AS C52,
SUM(DDA_MTD.PREVIOUS_AVERAGE_BALANCE) AS C53,
SUM(DDA_YTD.CYTD_SERVICE_CHARGES) AS C54,
SUM(DDA_YTD.CYTD_SERVIVE_CHARGE_WAIVED) AS C55,
SUM(DDA_YTD.CYTD_OTHER_CHARGES) AS C56,
SUM(DDA_YTD.CYTD_OTHER_CHARGES_WAIVED) AS C57,
SUM(DDA_YTD.CYTD_INTEREST_EARNED) AS C58,
SUM(DDA_YTD.CYTD_INTEREST_PAID) AS C59,
SUM(DDA_YTD.CYTD_AVERAGE_BALANCE) AS C60,
SUM(DDA_YTD.PYTD_SERVICE_CHARGE) AS C61,
SUM(DDA_YTD.PYTD_SERVICE_CHARGE_WAIVED) AS C62,
SUM(DDA_YTD.PYTD_OTHER_CHARGES) AS C63,
SUM(DDA_YTD.PYTD_OTHER_CHARGES_WAIVED) AS C64,
SUM(DDA_YTD.PYTD_INTEREST_EARNED) AS C65,
SUM(DDA_YTD.PYTD_INTEREST_PAID) AS C66,
SUM(DDA_YTD.PYTD_AVERAGE_BALANCE) AS C67,
SUM(DDA_YTD.CYTD_AGG_BALANCE) AS C68,
SUM(DDA_YTD.CYTD_AGG_AVAIL_BALANCE) AS C69,
SUM(DDA_YTD.CYTD_DAYS) AS C70,
SUM(DDA_YTD.PYTD_AGG_BALANCE) AS C71,
SUM(DDA_YTD.PYTD_AGG_AVAIL_BALANCE) AS C72,
SUM(DDA_YTD.PYTD_DAYS) AS C73,
DDA_CODES.COLLATERAL_PLEDGED_CODE AS C74,
DDA_COLL_PLEDGED_CODE_DESC_1.EXTERNAL_VALUE AS C75,
DDA_CODES.REFERRAL_RESP_CODE AS C76,
DDA_REF_RESP_CODE_DESC_1.EXTERNAL_VALUE AS C77,
SUM(DDA_INTEREST.RATE_ADJUSTER) AS C78,
DDA_ACCT.Date_Last_Contact AS C79,
SUM(DDA_MTD.CMTD_AGG_BALANCE) AS C80,
SUM(DDA_MTD.CMTD_DAYS) AS C81,
SUM(DDA_MTD.PMTD_AGG_BALANCE) AS C82,
SUM(DDA_MTD.PMTD_DAYS) AS C83,
DDA_CODES.NAICS_CODE AS C84,
DDA_CODES_NAICS_DESC.NAICS_DESCRIPTION AS C85,
DDA_CODES.RISK_RANKING AS C86,
DDA_CODES.RISK_SCORE1 AS C87,
DDA_CODES.RISK_SCORE2 AS C88,
SUM(DDA_MTD.CMTD_DAYS_OD) AS C89,
SUM(DDA_MTD.CMTD_NUMBER_OF_DAYS_NSF) AS C90,
SUM(DDA_MTD.CMTD_NUMBER_OF_ITEMS_NSF) AS C91,
SUM(DDA_MTD.CMTD_NUMBER_OF_RETURNED_NSF) AS C92,
SUM(DDA_MTD.PMTD_DAYS_OD) AS C93,
SUM(DDA_MTD.PMTD_NUMBER_OF_DAYS_NSF) AS C94,
SUM(DDA_MTD.PMTD_NUMBER_OF_ITEMS_NSF) AS C95,
SUM(DDA_MTD.PMTD_NUMBER_OF_RETURNED_NSF) AS C96,
SUM(DDA_MTD.PMTD_NSF_FEES_WAIVED) AS C97,
SUM(DDA_MTD.CMTD_NSF_FEES_WAIVED) AS C98,
SUM(DDA_MTD.CMTD_NSF_FEES_REFUNDED) AS C99,
SUM(DDA_MTD.PMTD_NSF_FEES_REFUNDED) AS C100,
SUM(DDA_YTD.CYTD_DAYS_OD) AS C101,
SUM(DDA_YTD.CYTD_NUMBER_OF_DAYS_NSF) AS C102,
SUM(DDA_YTD.CYTD_NUMBER_OF_TIMES_OVERDRAWN) AS C103,
SUM(DDA_YTD.CYTD_NUMBER_OF_ITEMS_NSF) AS C104,
SUM(DDA_YTD.CYTD_NUM_OF_RETURNED_NSF) AS C105,
SUM(DDA_YTD.PYTD_DAYS_OD) AS C106,
SUM(DDA_YTD.PYTD_NUMBER_OF_DAYS_NSF) AS C107,
SUM(DDA_YTD.PYTD_NUMBER_OF_TIMES_OVERDRAWN) AS C108,
SUM(DDA_YTD.PYTD_NUMBER_OF_ITEMS_NSF) AS C109,
SUM(DDA_YTD.PYTD_NUM_OF_RETURNED_NSF) AS C110,
SUM(DDA_YTD.CYTD_AVERAGE_AVAIL_BALANCE) AS C111,
SUM(DDA_YTD.PYTD_AVERAGE_AVAIL_BALANCE) AS C112,
SUM(DDA_YTD.PYTD_NSF_FEE_AMT) AS C113,
SUM(DDA_YTD.PYTD_NSF_RET_FEE_AMT) AS C114,
SUM(DDA_YTD.PYTD_NSF_FEES_WAIVED) AS C115,
SUM(DDA_YTD.CYTD_NSF_FEE_AMT) AS C116,
SUM(DDA_YTD.CYTD_NSF_RET_FEE_AMT) AS C117,
SUM(DDA_YTD.CYTD_NSF_FEES_WAIVED) AS C118,
SUM(DDA_YTD.CYTD_OVERDRAFT_FEE_AMT) AS C119,
SUM(DDA_YTD.CYTD_OTHER_MISC_FEE) AS C120,
SUM(DDA_YTD.CYTD_NSF_FEES_REFUNDED) AS C121,
SUM(DDA_YTD.PYTD_OVERDRAFT_FEE_AMT) AS C122,
SUM(DDA_YTD.PYTD_OTHER_MISC_FEE) AS C123,
SUM(DDA_YTD.PYTD_NSF_FEES_REFUNDED) AS C124,
SUM(DDA_ACCT.Reg_E_Overdraft_Balance_Limit) AS C125,
DDA_ACCT.Reg_E_Overdraft_Bal_Option AS C126,
DDA_ACCT.Reg_E_Consent_Notif_Option AS C127,
SUM(DDA_ACCT.Reg_E_Overdraft_Balance) AS C128,
DDA_CODES.REG_E_OD_NOTICE_CLOCK AS C129,
DDA_CODES.REG_E_OD_CHARGE_CLOCK AS C130,
DDA_CODES.REG_E_STATUS_DATE AS C131,
SUM(DDA_MTD.CMTD_NUMBER_OF_DEPOSITS) AS C132,
SUM(DDA_MTD.CMTD_NUMBER_OF_CHECKS) AS C133,
SUM(DDA_MTD.CMTD_AMOUNT_OF_DEPOSITS) AS C134,
SUM(DDA_MTD.CMTD_AMOUNT_OF_CHECKS) AS C135,
SUM(DDA_MTD.PMTD_NUMBER_OF_DEPOSITS) AS C136,
SUM(DDA_MTD.PMTD_NUMBER_OF_CHECKS) AS C137,
SUM(DDA_MTD.PMTD_AMOUNT_OF_DEPOSITS) AS C138,
SUM(DDA_MTD.PMTD_AMOUNT_OF_CHECKS) AS C139,
0 AS C140,
DDA_CODES.DOCUMENT_DISTRIBUTION_GROUP AS C141,
DDA_ACCT.Date_Overdrawn AS C142,
SUM(DDA_ACCT.Number_of_Days_Overdrawn) AS C143,
DDA_ACCT.ODP_Comments AS C144,
DDA_ACCT.ODP_Contact_Date AS C145,
CASE
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] IS NULL
OR [DDA_ACCT].[ODP_CONTACT_METHOD] = 0
THEN
'No Contact'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 1
THEN
'Telephone'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 2
THEN
'In Person'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 3
THEN
'Email'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 4
THEN
'Text'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 5
THEN
'Notice'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 6
THEN
'Statement'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 7
THEN
'Other'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 9
THEN
'No Further Contact'
ELSE
CAST([DDA_ACCT].[ODP_CONTACT_METHOD] AS CHAR(2))
END
AS C146, DDA_ACCT.ODP_Counsel_Date AS C147, DDA_ACCT.ODP_Current_Limit_Count AS C148, DDA_ACCT.ODP_Limit_Date AS C149,
CASE
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] IS NULL
OR [DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 0
THEN
'No Notice / No Statement / No Account Exception'
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 1
THEN
'No Notice / No Statement / Account Exception'
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 2
THEN
'Notice / No Statement / No Account Exception'
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 3
THEN
'No Notice / Statement / No Account Exception'
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 4
THEN
'Notice / Statement / No Account Exception'
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 5
THEN
'No Notice / Statement / Account Exception'
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 6
THEN
'Notice / No Statement / Account Exception'
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 7
THEN
'Notice / Statement / Account Exception'
ELSE
CAST([DDA_ACCT].[ODP_NOTIFICATION_OPTION] AS CHAR(2))
END
AS C150, DDA_ACCT.ODP_Prev_Counsel_Date AS C151, DDA_ACCT.ODP_Previous_Limit_Count AS C152, DDA_ACCT.ODP_Prev_Limit_Date AS C153, DDA_ACCT.ODP_Status_Date AS C154,
CASE
WHEN
[DDA_ACCT].[ODP_STATUS_OPTION] IS NULL
OR [DDA_ACCT].[ODP_STATUS_OPTION] = 0
THEN
'Not ODP Account'
WHEN
[DDA_ACCT].[ODP_STATUS_OPTION] = 1
THEN
'ODP Account'
WHEN
[DDA_ACCT].[ODP_STATUS_OPTION] = 2
THEN
'Customer Opt In'
WHEN
[DDA_ACCT].[ODP_STATUS_OPTION] = 3
THEN
'Customer Opt Out'
WHEN
[DDA_ACCT].[ODP_STATUS_OPTION] = 9
THEN
'Institution Opt Out'
ELSE
CAST([DDA_ACCT].[ODP_STATUS_OPTION] AS CHAR(2))
END
AS C155, DDA_ACCT.Overdraft_Balance_Limit AS C156, DDA_ACCT.Overdraft_Date_Reviewed AS C157, DDA_ACCT.Overdraft_Limit_Option AS C158, DDA_ACCT.Overdraft_Limit_Priority AS C159, DDA_ACCT.Overdraft_Rating_Code AS C160, DDA_ACCT.Overdraft_Type_Code AS C161, DDA_ACCT.CYTD_ODP_Contact_Attempts AS C162, DDA_ACCT.PYTD_ODP_Contact_Attempts AS C163, DDA_INTEREST.INTEREST_EFFECTIVE_DATE AS C164, DDA_INTEREST.COMPOUNDING_CODE AS C165, SUM(DDA_YTD.CYTD_STATE_WITHHOLDING) AS C166, SUM(DDA_YTD.CYTD_FEDERAL_WITHHOLDING) AS C167, SUM(DDA_YTD.PYTD_STATE_WITHHOLDING) AS C168, SUM(DDA_YTD.PYTD_FEDERAL_WITHHOLDING) AS C169, SUM(DDA_YTD.YTD_FED_WH_ADJ_THIS_RUN) AS C170, SUM(DDA_YTD.YTD_FED_WH_RPT_THIS_RUN) AS C171, SUM(DDA_YTD.YTD_FEDERAL_WH_PREV_RUN) AS C172, SUM(DDA_YTD.YTD_STATE_WH_ADJ_THIS_RUN) AS C173, SUM(DDA_YTD.YTD_STATE_WH_PREV_RUN) AS C174, SUM(DDA_YTD.YTD_STATE_WH_RPT_THIS_RUN) AS C175, DDA_PRIMARY_TAX_NAME_1.PORT_SEQUENCE_NUMBER AS C176, DDA_PRIMARY_NAME.NAME_ID AS C177, DDA_STMT_NAME_1_1.PORT_SEQUENCE_NUMBER AS C178, DDA_NAME_1.NAME_ID AS C179, DDA_STMT_NAME_2_1.PORT_SEQUENCE_NUMBER AS C180, DDA_NAME_2.NAME_ID AS C181, DDA_STMT_NAME_3_1.PORT_SEQUENCE_NUMBER AS C182, DDA_NAME_3.NAME_ID AS C183, DDA_ADDRESS_STMT_1.PORT_SEQUENCE_NUMBER AS C184, DDA_ADDRESS.ADDRESS_ID AS C185
FROM
dbo.DDA_Acct AS DDA_ACCT
INNER JOIN
DDA_CODES AS DDA_CODES
ON DDA_ACCT.INSTITUTION_NUMBER = DDA_CODES.INSTITUTION_NUMBER
AND DDA_ACCT.DDA_Account = DDA_CODES.DDA_ACCOUNT
INNER JOIN
DDA_INTEREST AS DDA_INTEREST
ON DDA_ACCT.INSTITUTION_NUMBER = DDA_INTEREST.INSTITUTION_NUMBER
AND DDA_ACCT.DDA_Account = DDA_INTEREST.DDA_ACCOUNT
INNER JOIN
DDA_MTD AS DDA_MTD
ON DDA_ACCT.INSTITUTION_NUMBER = DDA_MTD.INSTITUTION_NUMBER
AND DDA_ACCT.DDA_Account = DDA_MTD.DDA_ACCOUNT
INNER JOIN
DDA_YTD AS DDA_YTD
ON DDA_ACCT.INSTITUTION_NUMBER = DDA_YTD.INSTITUTION_NUMBER
AND DDA_ACCT.DDA_Account = DDA_YTD.DDA_ACCOUNT
LEFT OUTER JOIN
(
SELECT
Institution_Number AS INSTITUTION_NUMBER,
External_Value AS EXTERNAL_VALUE,
From_Internal_Value_Numeric AS FROM_INTERNAL_VALUE_NUMERIC
FROM
dbo.DDA_Data_Desc AS DDA_ACCOUNTING_GRP_DESC
WHERE
(
Field_Code = 144
)
)
AS DDA_ACCOUNTING_GRP_DESC_1
ON DDA_CODES.INSTITUTION_NUMBER = DDA_ACCOUNTING_GRP_DESC_1.INSTITUTION_NUMBER
AND DDA_CODES.ACCOUNTING_GROUP = DDA_ACCOUNTING_GRP_DESC_1.FROM_INTERNAL_VALUE_NUMERIC
LEFT OUTER JOIN
(
SELECT
DDA_ADDRESS_STMT.INSTITUTION_NUMBER,
DDA_ADDRESS_STMT.ADDRESS_ID,
DDA_ADDRESS_STMT.ACCOUNT_NUMBER,
DDA_ADDRESS_STMT.PORT_SEQUENCE_NUMBER
FROM
STMT_ADDRESS_TO_DDA AS DDA_ADDRESS_STMT
WHERE
(
DDA_ADDRESS_STMT.SEQUENCE_NUMBER = 0
)
)
AS DDA_ADDRESS_STMT_1
ON DDA_ACCT.INSTITUTION_NUMBER = DDA_ADDRESS_STMT_1.INSTITUTION_NUMBER
AND DDA_ACCT.DDA_Account = DDA_ADDRESS_STMT_1.ACCOUNT_NUMBER
LEFT OUTER JOIN
CIF_ADDRESS AS DDA_ADDRESS
ON DDA_ADDRESS_STMT_1.INSTITUTION_NUMBER = DDA_ADDRESS.INSTITUTION_NUMBER
AND DDA_ADDRESS_STMT_1.ADDRESS_ID = DDA_ADDRESS.ADDRESS_ID
LEFT OUTER JOIN
BRANCH_DESCRIPTION AS DDA_CODES_BRANCH_DESC
ON DDA_CODES.INSTITUTION_NUMBER = DDA_CODES_BRANCH_DESC.INSTITUTION_NUMBER
AND DDA_CODES.BRANCH_NUMBER = DDA_CODES_BRANCH_DESC.BRANCH_NUMBER
LEFT OUTER JOIN
(
SELECT
DDA_CODES_CLASS_CODE_DESC.INSTITUTION_NUMBER,
DDA_CODES_CLASS_CODE_DESC.BRANCH_NUMBER,
DDA_CODES_CLASS_CODE_DESC.CLASS_CODE,
DDA_CODES_CLASS_CODE_DESC.CLASS_DESCRIPTION
FROM
CLASS_DESCRIPTION AS DDA_CODES_CLASS_CODE_DESC
WHERE
(
DDA_CODES_CLASS_CODE_DESC.APPLICATION_TYPE_CODE = 1
)
)
AS DDA_CODES_CLASS_CODE_DESC_1
ON DDA_CODES.CLASS_CODE = DDA_CODES_CLASS_CODE_DESC_1.CLASS_CODE
AND DDA_CODES.INSTITUTION_NUMBER = DDA_CODES_CLASS_CODE_DESC_1.INSTITUTION_NUMBER
AND DDA_CODES.BRANCH_NUMBER = DDA_CODES_CLASS_CODE_DESC_1.BRANCH_NUMBER
LEFT OUTER JOIN
NAICS_DESCRIPTION AS DDA_CODES_NAICS_DESC
ON DDA_CODES.NAICS_CODE = DDA_CODES_NAICS_DESC.NAICS_CODE
LEFT OUTER JOIN
PRODUCT_DESCRIPTION AS DDA_CODES_PRODUCT_DESC
ON DDA_CODES.PRODUCT_NUMBER = DDA_CODES_PRODUCT_DESC.PRODUCT_NUMBER
AND DDA_CODES.INSTITUTION_NUMBER = DDA_CODES_PRODUCT_DESC.INSTITUTION_NUMBER
AND DDA_CODES.ACCOUNT_TYPE = DDA_CODES_PRODUCT_DESC.APPLICATION_TYPE_CODE — DDA_CODES.BRANCH_NUMBER = DDA_CODES_PRODUCT_DESC.BRANCH_NUMBER LEFT OUTER JOIN
LEFT OUTER JOIN
(
SELECT
Institution_Number AS INSTITUTION_NUMBER,
External_Value AS EXTERNAL_VALUE,
From_Internal_Value_Numeric AS FROM_INTERNAL_VALUE_NUMERIC
FROM
dbo.DDA_Data_Desc AS DDA_COLL_PLEDGED_CODE_DESC
WHERE
(
Field_Code = 7
)
)
AS DDA_COLL_PLEDGED_CODE_DESC_1
ON DDA_CODES.INSTITUTION_NUMBER = DDA_COLL_PLEDGED_CODE_DESC_1.INSTITUTION_NUMBER
AND DDA_CODES.COLLATERAL_PLEDGED_CODE = DDA_COLL_PLEDGED_CODE_DESC_1.FROM_INTERNAL_VALUE_NUMERIC
LEFT OUTER JOIN
(
SELECT
Institution_Number AS INSTITUTION_NUMBER,
External_Value AS EXTERNAL_VALUE,
From_Internal_Value_Numeric AS FROM_INTERNAL_VALUE_NUMERIC
FROM
dbo.DDA_Data_Desc AS DDA_COST_CENTER_CODE_DESC
WHERE
(
Field_Code = 145
)
)
AS DDA_COST_CENTER_CODE_DESC_1
ON DDA_CODES.INSTITUTION_NUMBER = DDA_COST_CENTER_CODE_DESC_1.INSTITUTION_NUMBER
AND DDA_CODES.COST_CENTER = DDA_COST_CENTER_CODE_DESC_1.FROM_INTERNAL_VALUE_NUMERIC
LEFT OUTER JOIN
(
SELECT
Institution_Number AS INSTITUTION_NUMBER,
External_Value AS EXTERNAL_VALUE,
From_Internal_Value_Numeric AS FROM_INTERNAL_VALUE_NUMERIC
FROM
dbo.DDA_Data_Desc AS DDA_MISC_CODE_DESC
WHERE
(
Field_Code = 12
)
)
AS DDA_MISC_CODE_DESC_1
ON DDA_CODES.INSTITUTION_NUMBER = DDA_MISC_CODE_DESC_1.INSTITUTION_NUMBER
AND DDA_CODES.MISC_CODE = DDA_MISC_CODE_DESC_1.FROM_INTERNAL_VALUE_NUMERIC
LEFT OUTER JOIN
(
SELECT
Institution_Number AS INSTITUTION_NUMBER,
External_Value AS EXTERNAL_VALUE,
From_Internal_Value_Numeric AS FROM_INTERNAL_VALUE_NUMERIC
FROM
dbo.DDA_Data_Desc AS DDA_OPENED_BY_RESP_CODE_DESC
WHERE
(
Field_Code = 1
)
)
AS DDA_OPENED_BY_RESP_CODE_DESC_1
ON DDA_CODES.INSTITUTION_NUMBER = DDA_OPENED_BY_RESP_CODE_DESC_1.INSTITUTION_NUMBER
AND DDA_CODES.OPENED_BY_RESP_CODE = DDA_OPENED_BY_RESP_CODE_DESC_1.FROM_INTERNAL_VALUE_NUMERIC
LEFT OUTER JOIN
(
SELECT
DDA_PRIMARY_TAX_NAME.INSTITUTION_NUMBER,
DDA_PRIMARY_TAX_NAME.NAME_ID,
DDA_PRIMARY_TAX_NAME.ACCOUNT_NUMBER,
DDA_PRIMARY_TAX_NAME.PORT_SEQUENCE_NUMBER
FROM
dbo.DDA_Acct AS DDA_ACCT
LEFT OUTER JOIN
TAX_NAME_TO_DDA AS DDA_PRIMARY_TAX_NAME
ON DDA_ACCT.INSTITUTION_NUMBER = DDA_PRIMARY_TAX_NAME.INSTITUTION_NUMBER
AND DDA_ACCT.DDA_Account = DDA_PRIMARY_TAX_NAME.ACCOUNT_NUMBER
)
AS DDA_PRIMARY_TAX_NAME_1
ON DDA_ACCT.INSTITUTION_NUMBER = DDA_PRIMARY_TAX_NAME_1.INSTITUTION_NUMBER
AND DDA_ACCT.DDA_Account = DDA_PRIMARY_TAX_NAME_1.ACCOUNT_NUMBER
LEFT OUTER JOIN
CIF_NAME AS DDA_PRIMARY_NAME
ON DDA_PRIMARY_TAX_NAME_1.INSTITUTION_NUMBER = DDA_PRIMARY_NAME.INSTITUTION_NUMBER
AND DDA_PRIMARY_TAX_NAME_1.NAME_ID = DDA_PRIMARY_NAME.NAME_ID
LEFT OUTER JOIN
(
SELECT
Institution_Number AS INSTITUTION_NUMBER,
External_Value AS EXTERNAL_VALUE,
From_Internal_Value_Numeric AS FROM_INTERNAL_VALUE_NUMERIC
FROM
dbo.DDA_Data_Desc AS DDA_REF_RESP_CODE_DESC
WHERE
(
Field_Code = 1
)
)
AS DDA_REF_RESP_CODE_DESC_1
ON DDA_CODES.INSTITUTION_NUMBER = DDA_REF_RESP_CODE_DESC_1.INSTITUTION_NUMBER
AND DDA_CODES.REFERRAL_RESP_CODE = DDA_REF_RESP_CODE_DESC_1.FROM_INTERNAL_VALUE_NUMERIC
LEFT OUTER JOIN
(
SELECT
Institution_Number AS INSTITUTION_NUMBER,
External_Value AS EXTERNAL_VALUE,
From_Internal_Value_Numeric AS FROM_INTERNAL_VALUE_NUMERIC
FROM
dbo.DDA_Data_Desc AS DDA_RESP_CODE_DESC
WHERE
(
Field_Code = 1
)
)
AS DDA_RESP_CODE_DESC_1
ON DDA_CODES.INSTITUTION_NUMBER = DDA_RESP_CODE_DESC_1.INSTITUTION_NUMBER
AND DDA_CODES.RESP_CODE = DDA_RESP_CODE_DESC_1.FROM_INTERNAL_VALUE_NUMERIC
LEFT OUTER JOIN
(
SELECT
DDA_STMT_NAME_1.INSTITUTION_NUMBER,
DDA_STMT_NAME_1.NAME_ID,
DDA_STMT_NAME_1.ACCOUNT_NUMBER,
DDA_STMT_NAME_1.PORT_SEQUENCE_NUMBER
FROM
dbo.DDA_Acct AS DDA_ACCT
LEFT OUTER JOIN
STMT_NAME_TO_DDA AS DDA_STMT_NAME_1
ON DDA_ACCT.INSTITUTION_NUMBER = DDA_STMT_NAME_1.INSTITUTION_NUMBER
AND DDA_ACCT.DDA_Account = DDA_STMT_NAME_1.ACCOUNT_NUMBER
WHERE
(
DDA_STMT_NAME_1.NAME_LINE = 1
)
)
AS DDA_STMT_NAME_1_1
ON DDA_ACCT.INSTITUTION_NUMBER = DDA_STMT_NAME_1_1.INSTITUTION_NUMBER
AND DDA_ACCT.DDA_Account = DDA_STMT_NAME_1_1.ACCOUNT_NUMBER
LEFT OUTER JOIN
CIF_NAME AS DDA_NAME_1
ON DDA_STMT_NAME_1_1.INSTITUTION_NUMBER = DDA_NAME_1.INSTITUTION_NUMBER
AND DDA_STMT_NAME_1_1.NAME_ID = DDA_NAME_1.NAME_ID
LEFT OUTER JOIN
(
SELECT
DDA_STMT_NAME_2.INSTITUTION_NUMBER,
DDA_STMT_NAME_2.NAME_ID,
DDA_STMT_NAME_2.ACCOUNT_NUMBER,
DDA_STMT_NAME_2.PORT_SEQUENCE_NUMBER
FROM
dbo.DDA_Acct AS DDA_ACCT
LEFT OUTER JOIN
STMT_NAME_TO_DDA AS DDA_STMT_NAME_2
ON DDA_ACCT.INSTITUTION_NUMBER = DDA_STMT_NAME_2.INSTITUTION_NUMBER
AND DDA_ACCT.DDA_Account = DDA_STMT_NAME_2.ACCOUNT_NUMBER
WHERE
(
DDA_STMT_NAME_2.NAME_LINE = 2
)
)
AS DDA_STMT_NAME_2_1
ON DDA_ACCT.INSTITUTION_NUMBER = DDA_STMT_NAME_2_1.INSTITUTION_NUMBER
AND DDA_ACCT.DDA_Account = DDA_STMT_NAME_2_1.ACCOUNT_NUMBER
LEFT OUTER JOIN
CIF_NAME AS DDA_NAME_2
ON DDA_STMT_NAME_2_1.INSTITUTION_NUMBER = DDA_NAME_2.INSTITUTION_NUMBER
AND DDA_STMT_NAME_2_1.NAME_ID = DDA_NAME_2.NAME_ID
LEFT OUTER JOIN
(
SELECT
DDA_STMT_NAME_3.INSTITUTION_NUMBER,
DDA_STMT_NAME_3.NAME_ID,
DDA_STMT_NAME_3.ACCOUNT_NUMBER,
DDA_STMT_NAME_3.PORT_SEQUENCE_NUMBER
FROM
dbo.DDA_Acct AS DDA_ACCT
LEFT OUTER JOIN
STMT_NAME_TO_DDA AS DDA_STMT_NAME_3
ON DDA_ACCT.INSTITUTION_NUMBER = DDA_STMT_NAME_3.INSTITUTION_NUMBER
AND DDA_ACCT.DDA_Account = DDA_STMT_NAME_3.ACCOUNT_NUMBER
WHERE
(
DDA_STMT_NAME_3.NAME_LINE = 3
)
)
AS DDA_STMT_NAME_3_1
ON DDA_ACCT.INSTITUTION_NUMBER = DDA_STMT_NAME_3_1.INSTITUTION_NUMBER
AND DDA_ACCT.DDA_Account = DDA_STMT_NAME_3_1.ACCOUNT_NUMBER
LEFT OUTER JOIN
CIF_NAME AS DDA_NAME_3
ON DDA_STMT_NAME_3_1.INSTITUTION_NUMBER = DDA_NAME_3.INSTITUTION_NUMBER
AND DDA_STMT_NAME_3_1.NAME_ID = DDA_NAME_3.NAME_ID
GROUP BY
DDA_ACCT.INSTITUTION_NUMBER,
DDA_ACCT.Portfolio,
DDA_ACCT.DDA_Account,
DDA_CODES.BRANCH_NUMBER,
DDA_CODES_BRANCH_DESC.BRANCH_DESCRIPTION,
DDA_CODES.CLASS_CODE,
DDA_CODES_CLASS_CODE_DESC_1.CLASS_DESCRIPTION,
DDA_CODES.PRODUCT_NUMBER,
DDA_CODES_PRODUCT_DESC.PRODUCT_DESCRIPTION,
DDA_CODES.RESP_CODE,
DDA_RESP_CODE_DESC_1.EXTERNAL_VALUE,
DDA_CODES.OPENED_BY_RESP_CODE,
DDA_OPENED_BY_RESP_CODE_DESC_1.EXTERNAL_VALUE,
DDA_CODES.MISC_CODE,
DDA_MISC_CODE_DESC_1.EXTERNAL_VALUE,
DDA_CODES.REG_D_ACCT_FLAG,
DDA_CODES.REG_D_EXCEPTION_FLAG,
DDA_CODES.NEW_ACCT_FLAG,
DDA_CODES.NEW_REPORTING_FLAG,
DDA_CODES.NOTIFICATION_CODE,
DDA_CODES.ACCT_TYPE_CODE,
DDA_CODES.STATUS_CODE,
DDA_CODES.HANDLING_CODE,
DDA_CODES.EMPLOYEE_OFFICER_DIRECTOR,
DDA_CODES.ACCOUNTING_GROUP,
DDA_ACCOUNTING_GRP_DESC_1.EXTERNAL_VALUE,
DDA_CODES.COST_CENTER,
DDA_COST_CENTER_CODE_DESC_1.EXTERNAL_VALUE,
DDA_INTEREST.DEPOSIT_RATE_INDEX,
DDA_CODES.CLOSED_ACCT_FLAG,
DDA_ACCT.Date_Opened,
DDA_ACCT.Date_Closed,
DATEADD([DAY], – 1, DATEADD([MONTH], 1, DATEADD([DAY], – DAY(DDA_ACCT.Date_Last_Updated) + 1, DDA_ACCT.Date_Last_Updated))),
DDA_ACCT.Date_Accrued_Thru,
DDA_CODES.COLLATERAL_PLEDGED_CODE,
DDA_COLL_PLEDGED_CODE_DESC_1.EXTERNAL_VALUE,
DDA_CODES.REFERRAL_RESP_CODE,
DDA_REF_RESP_CODE_DESC_1.EXTERNAL_VALUE,
DDA_ACCT.Date_Last_Contact,
DDA_CODES.NAICS_CODE,
DDA_CODES_NAICS_DESC.NAICS_DESCRIPTION,
DDA_CODES.RISK_RANKING,
DDA_CODES.RISK_SCORE1,
DDA_CODES.RISK_SCORE2,
DDA_ACCT.Reg_E_Overdraft_Bal_Option,
DDA_ACCT.Reg_E_Consent_Notif_Option,
DDA_CODES.REG_E_OD_NOTICE_CLOCK,
DDA_CODES.REG_E_OD_CHARGE_CLOCK,
DDA_CODES.REG_E_STATUS_DATE,
DDA_CODES.DOCUMENT_DISTRIBUTION_GROUP,
DDA_ACCT.Date_Overdrawn,
DDA_ACCT.ODP_Comments,
DDA_ACCT.ODP_Contact_Date,
CASE
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] IS NULL
OR [DDA_ACCT].[ODP_CONTACT_METHOD] = 0
THEN
'No Contact'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 1
THEN
'Telephone'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 2
THEN
'In Person'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 3
THEN
'Email'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 4
THEN
'Text'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 5
THEN
'Notice'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 6
THEN
'Statement'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 7
THEN
'Other'
WHEN
[DDA_ACCT].[ODP_CONTACT_METHOD] = 9
THEN
'No Further Contact'
ELSE
CAST([DDA_ACCT].[ODP_CONTACT_METHOD] AS CHAR(2))
END
, DDA_ACCT.ODP_Counsel_Date, DDA_ACCT.ODP_Current_Limit_Count, DDA_ACCT.ODP_Limit_Date,
CASE
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] IS NULL
OR [DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 0
THEN
'No Notice / No Statement / No Account Exception'
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 1
THEN
'No Notice / No Statement / Account Exception'
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 2
THEN
'Notice / No Statement / No Account Exception'
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 3
THEN
'No Notice / Statement / No Account Exception'
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 4
THEN
'Notice / Statement / No Account Exception'
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 5
THEN
'No Notice / Statement / Account Exception'
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 6
THEN
'Notice / No Statement / Account Exception'
WHEN
[DDA_ACCT].[ODP_NOTIFICATION_OPTION] = 7
THEN
'Notice / Statement / Account Exception'
ELSE
CAST([DDA_ACCT].[ODP_NOTIFICATION_OPTION] AS CHAR(2))
END
, DDA_ACCT.ODP_Prev_Counsel_Date, DDA_ACCT.ODP_Previous_Limit_Count, DDA_ACCT.ODP_Prev_Limit_Date, DDA_ACCT.ODP_Status_Date,
CASE
WHEN
[DDA_ACCT].[ODP_STATUS_OPTION] IS NULL
OR [DDA_ACCT].[ODP_STATUS_OPTION] = 0
THEN
'Not ODP Account'
WHEN
[DDA_ACCT].[ODP_STATUS_OPTION] = 1
THEN
'ODP Account'
WHEN
[DDA_ACCT].[ODP_STATUS_OPTION] = 2
THEN
'Customer Opt In'
WHEN
[DDA_ACCT].[ODP_STATUS_OPTION] = 3
THEN
'Customer Opt Out'
WHEN
[DDA_ACCT].[ODP_STATUS_OPTION] = 9
THEN
'Institution Opt Out'
ELSE
CAST([DDA_ACCT].[ODP_STATUS_OPTION] AS CHAR(2))
END
, DDA_ACCT.Overdraft_Balance_Limit, DDA_ACCT.Overdraft_Date_Reviewed, DDA_ACCT.Overdraft_Limit_Option, DDA_ACCT.Overdraft_Limit_Priority, DDA_ACCT.Overdraft_Rating_Code, DDA_ACCT.Overdraft_Type_Code, DDA_ACCT.CYTD_ODP_Contact_Attempts, DDA_ACCT.PYTD_ODP_Contact_Attempts, DDA_INTEREST.INTEREST_EFFECTIVE_DATE, DDA_INTEREST.COMPOUNDING_CODE, DDA_PRIMARY_TAX_NAME_1.PORT_SEQUENCE_NUMBER, DDA_PRIMARY_NAME.NAME_ID, DDA_STMT_NAME_1_1.PORT_SEQUENCE_NUMBER, DDA_NAME_1.NAME_ID, DDA_STMT_NAME_2_1.PORT_SEQUENCE_NUMBER, DDA_NAME_2.NAME_ID, DDA_STMT_NAME_3_1.PORT_SEQUENCE_NUMBER, DDA_NAME_3.NAME_ID, DDA_ADDRESS_STMT_1.PORT_SEQUENCE_NUMBER, DDA_ADDRESS.ADDRESS_ID
)
AS T0
PS: the statement on the CommandText panel is not complete…