SQL: 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s