On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.
Select sm.BO_CODE as 'Customer Number', first_name as 'Customer First Name', last_name as 'Customer Last Name', address_ln as 'Customer Address', CITY as 'Customer City', STATE_ID as 'Customer State', POSTAL_ID as 'Customer Zip', sm.LOAN_CODE as 'Loan Number', LOAN_DATE as 'Loan Date', LOAN_AMT as 'Loan Amount', sm.DUE_DATE as 'Loan Due Date', asa.ACH_EFFECTIVE_DATE as 'ACH Effective Date', TOTAL_DUE as 'Amount Due' into #cust from USOnlineReporting.dbo.ST_LO_MASTER sm inner join USOnlineReporting.dbo.ACH_STAGING_ACTIVE asa on asa.LOAN_CODE = sm.LOAN_CODE inner join USOnlineReporting.dbo.BO_MASTER bm on bm.BO_CODE = sm.BO_CODE inner join usonlinereporting.dbo.bo_address ba on ba.bo_code = bm.bo_code inner join USOnlineReporting.dbo.BO_IN_APP_QUEUE bia on bia.APP_NO = sm.LOAN_CODE inner join USOnlineReporting.dbo.ST_COLLECTION_MASTER scm on scm.LOAN_CODE = sm.LOAN_CODE where CSLD_ELIGIBILITY_FLAG = 'y' and LOAN_STATUS_ID = 'opn' and CHECK_STATUS_ID = 'hld' and TRAN_ID = 'dp' and asa.ACH_EFFECTIVE_DATE > GETDATE() and asa.IS_HOLD = 'n' and CONTACT_STATUS_ID <> '6' and ( (STATE_ID = 'al' and sm.ROLL_OVER_COUNT = 1) or (STATE_ID = 'ca') or (STATE_ID = 'de' and sm.ROLL_OVER_COUNT = 4) or (STATE_ID = 'id' and sm.ROLL_OVER_COUNT = 7) or (STATE_ID = 'mo' and sm.ROLL_OVER_COUNT = 2) or (STATE_ID = 'oh' and sm.ROLL_OVER_COUNT = 7) or (STATE_ID = 'sd' and sm.ROLL_OVER_COUNT = 4) or (STATE_ID = 'ut' and sm.ROLL_OVER_COUNT = 3) ) and sm.bo_code not in ( Select BO_CODE from USOnlineReporting.dbo.BO_APP_LOAN_CUST_FLAGS balcf inner join USOnlineReporting.dbo.LT_IN_APPLICATION_LOAN_FLAGS lialf on lialf.FLAG_ID = balcf.flag_id where LOAN_FLAG_DESCRIPTION in ('bad cp','bad hp','bad wp','dnc-home phone','dnc-work phone','dnc-courtesy call') ) order by asa.ACH_EFFECTIVE_DATE asc Select BO_code, Phone_ID, Phone_Num into #Home from usonlinereporting.dbo.bo_phone inner join #cust on bo_phone.bo_code = #cust.[Customer Number] Where Phone_ID ='H' Select BO_code, Phone_ID, Phone_Num into #Cell from usonlinereporting.dbo.bo_phone inner join #cust on bo_phone.bo_code = #cust.[Customer Number] Where Phone_ID ='C' Select BO_code, Phone_ID, Phone_Num into #Work from usonlinereporting.dbo.bo_phone inner join #cust on bo_phone.bo_code = #cust.[Customer Number] Where Phone_ID ='W' Select [Customer Number], [Customer First Name], [Customer Last Name], [Customer Address], [Customer City], [Customer State], [Customer Zip], 'Home Phone' as 'Customer Phone Type1', #home.Phone_Num as 'Customer Phone Number1', 'Cell Phone' as 'Customer Phone Type2', #Cell.Phone_num as 'Customer Phone Number2', 'Work Phone' as 'Customer Phone Type3', #work.phone_Num as 'Customer Phone Number3', [Loan Number], [Loan Date], [Loan Amount] , [Loan Due Date], [ACH Effective Date], [Amount Due] from #cust left join #Home on #cust.[Customer Number] = #home.BO_Code left join #cell on #cust.[Customer Number] = #cell.BO_Code left join #Work on #cust.[Customer Number] = #work.BO_Code
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
|Help with Data Warehouse / Data Marts||4||42|
|SQL Server how to use a VARIABLE to link tables in a SQL Script?||3||41|
|SQL Simple Query Taking a Very Long Time||11||37|
|SQL Server Reports (SSRS 2014) - Reports Timing Out||11||118|
Join the community of 500,000 technology professionals and ask your questions.