Part of my sub-query for [bankruptcyloanswithnopayment], it repeats the the query below from the main query.
Main Query
SELECT COUNT(L.LOANID) AS numberofloans ,SUM (case when ST.StatusTransactionTypeID=5 then 1 else 0 end ) as numberofbankruptcyloans ,SUM (case when ST.StatusTransactionTypeID=6 then 1 else 0 end ) as numberofwriteoffloans , ( SELECT COUNT(LOANID)as bankruptcyloanswithnopayment FROM ( SELECT MAX(STATUSTRANSACTIONID) AS ID,STATUSTRANSACTIONLOANID FROM tblStatusTransactions GROUP BY StatusTransactionLoanID )MAXID INNER JOIN tblStatusTransactions ST ON MAXID.ID = ST.StatusTransactionID INNER JOIN #LOANID L ON L.LOANID=MAXID.StatusTransactionLoanID WHERE STATUSTRANSACTIONTYPEID=5 AND LOANID NOT IN (SELECT R.LOANID FROM tblReportingTransactions R WHERE R.TransactionTypeID IN (1,2,3,11,23)) ) as bankruptcyloanswithnopayment ,(SELECT COUNT (LOANMAXDATEID.LOANID) AS NUMBEROFLATEPAYMENT FROM ( SELECT MAX(REPORTINGTRANSACTIONID) AS MAXDATEID ,OPENLOANS.LOANID FROM ( SELECT ST.StatusTransactionLoanID AS LOANID FROM ( SELECT MAX(STATUSTRANSACTIONID) AS ID,STATUSTRANSACTIONLOANID FROM tblStatusTransactions GROUP BY StatusTransactionLoanID )MAXID INNER JOIN tblStatusTransactions ST ON MAXID.ID = ST.StatusTransactionID INNER JOIN #LOANID L ON L.LOANID=MAXID.StatusTransactionLoanID WHERE StatusTransactionTypeID IN (1,7) )OPENLOANS INNER JOIN tblReportingTransactions RT ON RT.LoanID = OPENLOANS.LOANID Group by OPENLOANS.LoanID ) LOANMAXDATEID LEFT JOIN tblReportingTransactions RT ON RT.ReportingTransactionID =LOANMAXDATEID.MAXDATEID WHERE DATEDIFF (d,nextduedate, GETDATE()) >=30 ) as numberoflatepaymentFROM ( SELECT MAX(STATUSTRANSACTIONID) AS ID,STATUSTRANSACTIONLOANID FROM tblStatusTransactions GROUP BY StatusTransactionLoanID )MAXID INNER JOIN tblStatusTransactions ST ON MAXID.ID = ST.StatusTransactionID INNER JOIN #LOANID L ON L.LOANID=MAXID.StatusTransactionLoanID
Is there any simple way to get [bankruptcyloanswithnopayment] without repeating the former query?
Thank you.
, ( SELECT COUNT(LOANID)as bankruptcyloanswithnopayment FROM ( SELECT MAX(STATUSTRANSACTIONID) AS ID,STATUSTRANSACTIONLOANID FROM tblStatusTransactions GROUP BY StatusTransactionLoanID )MAXID INNER JOIN tblStatusTransactions ST ON MAXID.ID = ST.StatusTransactionID INNER JOIN #LOANID L ON L.LOANID=MAXID.StatusTransactionLoanID WHERE STATUSTRANSACTIONTYPEID=5 AND LOANID NOT IN (SELECT R.LOANID FROM tblReportingTransactions R WHERE R.TransactionTypeID IN (1,2,3,11,23)) ) as bankruptcyloanswithnopayment