Return workgroup name where the MaxDate is greater over 2 rows

Transact-SQL
1

Hi all, I have a table that holds the following;

Username, Workgroup, ActivationFlag & MaxDate

Table

I want to return the number of workgroups that are activated for the user if the Maxdate is the most recent and Activationflag = 1.

For example, I wouldn't return the workgroup "GBR 1471 Call In" in the results because the MaxDate of the deactivation (ActivationFlag = 0) is greater than the Activation (ActivationFlag = 1)

Table2

But I would want to return "GBR DJ Test Call In" because the max date of the activation (ActivationFlag = 1) is greater than the De-Activation (ActivationFlag = 0)

I'm hoping someone can help me, I've tried it all different ways but I'm yet to crack this one.

Thanks in advance

David

2

Please provide usable sample data ? help us help you

3 
SELECT Userid, COUNT(DISTINCT Workgroup) /* or COUNT(*) */ AS Workgroup_count
FROM dbo.your_table_name
GROUP BY Userid
HAVING MAX(CASE WHEN ActivationFlag = 1 THEN MaxDate END) > 
    MAX(CASE WHEN ActivationFlag = 0 THEN MaxDate ELSE 0 END)
2 Likes
4

Thanks Scott, much appreciated