asked on

SQL. How to simply the query.

Hi Experts,

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  numberoflatepayment
	 							   
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

Open in new window

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

Open in new window

SOLUTION