Tuesday 10 July 2012

SQL Query: Join Query from same table with different where condition



SELECT DISTINCT TOP (100) PERCENT dbo.tblFeedbackAssignment.AssignmentReference, T.Total, S.Started, N.NotStarted, C.Completed
FROM         dbo.tblFeedbackAssignment LEFT OUTER JOIN
                          (SELECT DISTINCT TOP (100) PERCENT AssignmentReference, COUNT(AssignmentID) AS Total
                            FROM          dbo.tblFeedbackAssignment AS tblFeedbackAssignment_1
                            GROUP BY AssignmentReference
                            ORDER BY AssignmentReference) AS T ON dbo.tblFeedbackAssignment.AssignmentReference = T.AssignmentReference LEFT OUTER JOIN
                          (SELECT     AssignmentReference, COUNT(AssignmentID) AS NotStarted
                            FROM          dbo.tblFeedbackAssignment AS tblFeedbackAssignment_2
                            WHERE      (AssignmentStartDate IS NULL)
                            GROUP BY AssignmentReference) AS N ON dbo.tblFeedbackAssignment.AssignmentReference = N.AssignmentReference LEFT OUTER JOIN
                          (SELECT DISTINCT TOP (100) PERCENT AssignmentReference, COUNT(AssignmentID) AS Started
                            FROM          dbo.tblFeedbackAssignment AS tblFeedbackAssignment_3
                            WHERE      (NOT (AssignmentStartDate IS NULL))
                            GROUP BY AssignmentReference
                            ORDER BY AssignmentReference) AS S ON dbo.tblFeedbackAssignment.AssignmentReference = S.AssignmentReference LEFT OUTER JOIN
                          (SELECT DISTINCT TOP (100) PERCENT AssignmentReference, COUNT(AssignmentID) AS Completed
                            FROM          dbo.tblFeedbackAssignment AS tblFeedbackAssignment_4
                            WHERE      (NOT (AssignmentStartDate IS NULL)) AND (NOT (AssignmentEndDate IS NULL))
                            GROUP BY AssignmentReference
                            ORDER BY AssignmentReference) AS C ON dbo.tblFeedbackAssignment.AssignmentReference = C.AssignmentReference
ORDER BY dbo.tblFeedbackAssignment.AssignmentReference

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home