April 21, 2010

JOIN two select results simlple example

SELECT ROW_NUMBER() OVER (ORDER BY B.BranchName) AS [SR No],B.BranchName As 'Branch Name' ,B.FranchiseeName AS 'Franchisee Name' ,B.TD as 'Total Data',ISNULL(A.TC,0) as 'Survey Done'from
(SELECT ComplaintDetails.BranchName,ComplaintDetails.FranchiseeName,COUNT(*) AS 'TD' FROM ComplaintDetails
WHERE
TDate BETWEEN @FROMDATE AND @TODATE
GROUP BY ComplaintDetails.BranchName,ComplaintDetails.FranchiseeName) AS B LEFT OUTER JOIN
(SELECT BranchName,FranchiseeName,COUNT(*) AS 'TC' FROM SurveyDetails
WHERE SurveyDate BETWEEN @FROMDATE AND @TODATE
GROUP BY BranchName,FranchiseeName)As A
ON
B.BranchName = A.BranchName
AND
B.FranchiseeName = A.FranchiseeName
ORDER BY
B.BranchName

No comments:

Post a Comment