troubleshooting Question

SQL. How to simply the query.

Avatar of tanj1035
tanj1035 asked on
Microsoft SQL Server
3 Comments2 Solutions107 ViewsLast Modified:
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 

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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros