Home› General Discussion

SCSM SSRS - SQL Help - Affected User / Assigned User - Please help

CaterhamITSupportCaterhamITSupport Member Advanced IT Monkey ✭✭✭
edited May 2020 in General Discussion

Dear All,

I am having issues with getting reports with affected user and assigned to user. I have the following code and when I add the deleted date part into the where part it doesn't show all the incidents..

AND (wiau.DeletedDate is null)

AND (wiat.DeletedDate is null)

When i remove them it shows me duplicates with other people assigned in the past, can anyone help me with this. Its not giving me the correct results and I'm struggling with this one.

Thanks

Daniel


LEFT OUTER JOIN entitydim as e on IncidentDim.EntityDimKey = e.EntityDimKey

LEFT OUTER JOIN workitemdim as wi on e.EntityDimKey = wi.EntityDimKey

LEFT OUTER JOIN workitemaffecteduserfactvw as wiau on wi.WorkItemDimKey = wiau.WorkItemDimKey

LEFT OUTER JOIN UserDim as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey

LEFT OUTER JOIN WorkItemAssignedToUserFactvw as wiat on wi.WorkItemDimKey = wiat.WorkItemDimKey

LEFT OUTER JOIN UserDim as AssignedToUser on wiat.WorkItemAssignedToUser_UserDimKey = AssignedToUser.UserDimKey


WHERE    


AND (wiau.DeletedDate is null)

AND (wiat.DeletedDate is null)

ORDER BY IncidentDim.CreatedDate DESC

Best Answer

  • CaterhamITSupportCaterhamITSupport Member Advanced IT Monkey ✭✭✭
    Answer ✓

    I managed to fix this by changing to by moving the where statement part to the joins:

    LEFT OUTER JOIN workitemaffecteduserfactvw as wiau on wi.WorkItemDimKey = wiau.WorkItemDimKey

    AND wiau.DeletedDate is null

    LEFT OUTER JOIN UserDim as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey


    LEFT OUTER JOIN WorkItemAssignedToUserFactvw as wiat on wi.WorkItemDimKey = wiat.WorkItemDimKey

    AND wiat.DeletedDate IS NULL

    LEFT OUTER JOIN UserDim as AssignedToUser on wiat.WorkItemAssignedToUser_UserDimKey = AssignedToUser.UserDimKey

Answers

Sign In or Register to comment.