Trying to create two calculated columns of count of actions by person, each should count BEFORE and AFTER current date. Query works with one calculated column, but if I add another (commented out), it stops. Can any one help to find error, please? Sorry, had to put spaces here B1. ID and here B2. ID, because forum recognizes them as links.
SELECT [AppUser].Surname
,count(B1. ID) AS Active
--,count(B2. ID) AS Overdue
FROM [AppUser]
JOIN [AppUserRole_RelatedItem] ON [AppUser].ID = [AppUserRole_RelatedItem].UserRole_ID
JOIN [ActionPlan] AS B1 ON [AppUserRole_RelatedItem].Module_ID = B1. ID
--JOIN [ActionPlan] AS B2 ON [AppUserRole_RelatedItem].Module_ID = B2. ID
WHERE [AppUser].Surname = 'Duggan'
AND B1.Proposed_Resolution_Date <= getdate()
--AND B2.Proposed_Resolution_Date > getdate()
The two WHERE clauses filter out all rows from the table. Use a left join or outer apply with the date filter in the join condition - for example, like this:
SELECT [AppUser].Surname ,
COUNT(B1.ID) AS Active ,
COUNT(B2.ID) AS Overdue
FROM [AppUser]
JOIN [AppUserRole_RelatedItem] ON [AppUser].ID = [AppUserRole_RelatedItem].UserRole_ID
LEFT JOIN [ActionPlan] AS B1 ON
[AppUserRole_RelatedItem].Module_ID = B1.ID AND
B1.Proposed_Resolution_Date <= GETDATE()
LEFT JOIN [ActionPlan] AS B2 ON
[AppUserRole_RelatedItem].Module_ID = B2.ID
AND B2.Proposed_Resolution_Date > GETDATE()
WHERE [AppUser].Surname = 'Duggan'
GROUP BY [AppUser].Surname;
......
.....
GROUP BY [AppUser].Surname
HAVING COUNT(B1.ID) > 0 OR COUNT(B2.ID) > 0;
Having is sort of like a WHERE clause, but logically, having is applied after the aggregation, but where is applied before the aggregation. So you cannot use aggregate functions (such as COUNT) in the WHERE clause.