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