Avatar of tanj1035
tanj1035
 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

Microsoft SQL Server

Avatar of undefined
Last Comment
tanj1035

8/22/2022 - Mon
SOLUTION
Olaf Doschke

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
tanj1035

ASKER
Thank you , guys, very helpful.
Your help has saved me hundreds of hours of internet surfing.
fblack61