Two Table joing time

Question

Hello Everyone,
below is my sting with filtering between two dates and department after filtering total record is showing 2161 but its take 1sec to filter this where i am wrong in string

SELECT A.Jid, B.NoPices, B.ASCaptuWt FROM
(SELECT JID, CustId FROM tblJobCard_Mst WHERE JobCardDate BETWEEN '20200101' AND '20211019' AND OrderByDeptId IN (2)) A
INNER JOIN
(SELECT tblLabelPrint.Jid AS JobCardId, SUM(NoPices) NoPices, SUM(CaptuWt - coreweight) ASCaptuWt, custID
FROM tblLabelPrint GROUP BY Jid, custID) B
ON A.Jid = B.JobCardId AND A.CustId = B.custID

Helping about this string appreciate
Hitesh Patel

Answer 1

Hi @Hiteshkumar Patel ,

Welcome to the microsoft TSQL Q&A forum!

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result? So that we’ll get a right direction and make some test.

In addition, it will be easier to read your code as a CTE:

;WITH A AS  
(SELECT JID, CustId   
     FROM tblJobCard_Mst   
     WHERE JobCardDate   
  BETWEEN '20200101' AND '20211019'   
  AND OrderByDeptId = 2)   
,B AS   
(SELECT tblLabelPrint.Jid AS JobCardId,   
SUM(NoPices) NoPices,   
SUM(CaptuWt - coreweight) ASCaptuWt, custID  
FROM tblLabelPrint   
GROUP BY Jid, custID)  
  
SELECT A.Jid, B.NoPices, B.ASCaptuWt   
FROM A  
INNER JOIN B  
ON A.Jid = B.JobCardId AND A.CustId = B.custID  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.