SQLTeam.com | Weblogs | Forums

Two calculated columns


#1

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()

GROUP BY [AppUser].Surname


#2

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;

#3

James, thank you so much.


#4

How do I filter out people who have when both 'Active' and 'Overdue' equal 0?


#5

Add a HAVING clause at the very end.

......
.....
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.


#6

Seems worked for me adding it to WHERE clause.

Thanks for your kind help, really appreciated.