Alex Alex - 2 months ago 14
SQL Question

COUNT Multiple tables with relationships and users

I've got a group of tables in SQL Server that I need to count on with specific counting criteria for each table, the problem I'm having that I need to group this by users, which are contained in one table and are mapped to the tables I need to count on via a relationship table.

This is the relationship table

Relationship User Work Item
Analyst 1 IR1
Analyst 2 IR2
Analyst 2 IR3
Analyst 1 IR4
User 3 IR1
Analyst 1 SR2
Analyst 1 SR3
Analyst 2 SR4


This is the IR table (the SR table is identical)

ID Status
IR1 Active
IR2 Active
IR3 Closed
IR4 Active


This is the user table

User Name
1 Dave
2 Jim
3 Karl


What I need is a table like below counting only the active items

Name IR Count SR Count
Dave 2 2
Jim 1 1


All I seem to be able to do currently is count all of the users regardless of status, I think this may be due to the left joins. I basically had:

Select u.name,
count (ir),
count (sr) from user u
Inner join relationship r on r.user=u.user and r.relationship = 'Analyst'
Left Join IR on r.workitem=ir.id and ir.Status = 'Active'
Left Join SR on r.workitem=sr.id and sr.Status = 'Active'
Group by u.name


I have simplified the above as much as possible. This is the actual query:

SELECT
u.DisplayName as Analyst,
u.BaseManagedEntityId as AUsername,
COUNT(distinct i.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C) AS 'Active Incidents',
COUNT(distinct sr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C) as 'Active Service Requests',
COUNT(distinct cr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C) as 'Active Change Requests',
COUNT(distinct ma.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C) as 'Active Manual Activities'
FROM MTV_System$Domain$User u
INNER JOIN RelationshipView r ON r.TargetEntityId = u.BaseManagedEntityId AND r.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' AND r.IsDeleted ='0'
LEFT JOIN MTV_System$WorkItem$Incident i ON r.SourceEntityId = i.BaseManagedEntityId AND (i.Status_785407A9_729D_3A74_A383_575DB0CD50ED != '2B8830B6-59F0-F574-9C2A-F4B4682F1681' AND i.Status_785407A9_729D_3A74_A383_575DB0CD50ED != 'BD0AE7C4-3315-2EB3-7933-82DFC482DBAF')
LEFT JOIN MTV_System$WorkItem$ServiceRequest sr ON r.SourceEntityId = SR.BaseManagedEntityId AND (sr.Status_6DBB4A46_48F2_4D89_CBF6_215182E99E0F = '72B55E17-1C7D-B34C-53AE-F61F8732E425' OR sr.Status_6DBB4A46_48F2_4D89_CBF6_215182E99E0F = '59393F48-D85F-FA6D-2EBE-DCFF395D7ED1' OR sr.Status_6DBB4A46_48F2_4D89_CBF6_215182E99E0F = '05306BF5-A6B9-B5AD-326B-BA4E9724BF37')
LEFT JOIN MTV_System$WorkItem$ChangeRequest cr on r.SourceEntityId = cr.BaseManagedEntityId AND (cr.Status_72C1BC70_443C_C96F_A624_A94F1C857138 = '6D6C64DD-07AC-AAF5-F812-6A7CCEB5154D' or cr.Status_72C1BC70_443C_C96F_A624_A94F1C857138 = 'DD6B0870-BCEA-1520-993D-9F1337E39D4D')
LEFT JOIN MTV_System$WorkItem$Activity$ManualActivity MA on r.SourceEntityId = ma.BaseManagedEntityId AND (ma.Status_8895EC8D_2CBF_0D9D_E8EC_524DEFA00014 = '11FC3CEF-15E5-BCA4-DEE0-9C1155EC8D83' OR ma.Status_8895EC8D_2CBF_0D9D_E8EC_524DEFA00014 = 'D544258F-24DA-1CF3-C230-B057AAA66BED')

GROUP BY u.DisplayName,u.BaseManagedEntityId
Order by u.DisplayName

Answer

your over simplification seems to have lost your what appears to be your issue from some of your comments. Using left joins would include any of the users even if they don't have a count of one of your other tables. However if you want your result set to only include usres that have at least 1 Incident and/or 1 Request and/or 1 Change Request. Etc. you can either filter the aggretation you are doing after the fact to remove when Incidents + Requests + ... = 0. Or you can filter them out by adding a WHERE statement that says WHEN NOT all of those other tables are null which is the same as OR IS NOT NULL...

 SELECT 
 u.DisplayName as Analyst, 
 u.BaseManagedEntityId as AUsername,
 COUNT(distinct i.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C) AS 'Active Incidents',
 COUNT(distinct sr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C) as 'Active Service Requests',
 COUNT(distinct cr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C) as 'Active Change Requests',
 COUNT(distinct ma.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C) as 'Active Manual Activities'
 FROM
    MTV_System$Domain$User u 
    INNER JOIN RelationshipView r
    ON r.TargetEntityId = u.BaseManagedEntityId
    AND r.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' 
    AND  r.IsDeleted ='0'
    LEFT JOIN  MTV_System$WorkItem$Incident i
    ON r.SourceEntityId = i.BaseManagedEntityId
    AND i.Status_785407A9_729D_3A74_A383_575DB0CD50ED NOT IN ('2B8830B6-59F0-F574-9C2A-F4B4682F1681','BD0AE7C4-3315-2EB3-7933-82DFC482DBAF')
    LEFT JOIN MTV_System$WorkItem$ServiceRequest sr
    ON r.SourceEntityId = SR.BaseManagedEntityId
    AND sr.Status_6DBB4A46_48F2_4D89_CBF6_215182E99E0F IN ('72B55E17-1C7D-B34C-53AE-F61F8732E425','59393F48-D85F-FA6D-2EBE-DCFF395D7ED1','05306BF5-A6B9-B5AD-326B-BA4E9724BF37')
    LEFT JOIN MTV_System$WorkItem$ChangeRequest cr
    ON r.SourceEntityId = cr.BaseManagedEntityId
    AND cr.Status_72C1BC70_443C_C96F_A624_A94F1C857138 IN ('6D6C64DD-07AC-AAF5-F812-6A7CCEB5154D','DD6B0870-BCEA-1520-993D-9F1337E39D4D')
    LEFT JOIN MTV_System$WorkItem$Activity$ManualActivity MA
    ON r.SourceEntityId = ma.BaseManagedEntityId
    AND ma.Status_8895EC8D_2CBF_0D9D_E8EC_524DEFA00014 IN ('11FC3CEF-15E5-BCA4-DEE0-9C1155EC8D83','D544258F-24DA-1CF3-C230-B057AAA66BED')
WHERE
    i.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C IS NOT NULL
    OR sr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C IS NOT NULL
    OR cr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C IS NOT NULL
    OR ma.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C IS NOT NULL
GROUP BY u.DisplayName,u.BaseManagedEntityId
 Order by u.DisplayName

Also note the user of IN and NOT IN in the join conditions instead of OR all of the time.