Home SQL Server

DW and problemws with assigned to user

Bostjan_CernjakBostjan_Cernjak Customer IT Monkey ✭
hi,

i have a great querry :) which return all incidents with "management" wanted parameters. The only problem is it returns duplicate incidents if it was  reassigned. How can i show all incidents and show only the last assignedto user. If i use the deleteddate columen in workitemassignedtouser i only get active incidents. Thank you in advance.


select inc.Id,
 usr1.DisplayName as 'Operater',
 inc.CreatedDate,
 inc.ClosedDate,
 inc.ResolvedDate,
 usr.DisplayName as 'Uporabnik',
 usr.Department,
 usr.EmployeeId,
 usr.Company,
 usr.Office,
 incs.IncidentStatusValue,
 dsc.DisplayName as 'Clasification',
 dsc1.DisplayName as 'Support Group',
 left(inc.id,2) as 'tip',
 month (inc.CreatedDate) as 'MesecKreiranja',
 year( inc.CreatedDate) as 'LetoKreiranja',
 month (inc.ResolvedDate) as 'MesecResitve',
 year( inc.ResolvedDate) as 'LetoResitve'
from
IncidentDimvw INC
full join IncidentClassificationvw IC on inc.Classification_IncidentClassificationId = ic.IncidentClassificationId
full join IncidentStatus INCS on inc.Status_IncidentStatusId= incs.IncidentStatusId
full join IncidentTierQueuesvw IQ on inc.TierQueue_IncidentTierQueuesId = iq.IncidentTierQueuesId
full join WorkItemDimvw WI on inc.EntityDimKey = wi.EntityDimKey
full join WorkItemAffectedUserFactvw WIAf on wi.WorkItemDimKey = wiaf.WorkItemDimKey
full join WorkItemAssignedToUserFactvw WIas on wi.WorkItemDimKey = wias.WorkItemDimKey
full join UserDimvw USR on wiaf.WorkItemAffectedUser_UserDimKey = usr.UserDimKey
full join UserDimvw USR1 on wias.WorkItemAssignedToUser_UserDimKey = usr1.UserDimKey
full join DisplayStringDimvw DSC on ic.EnumTypeId = dsc.BaseManagedEntityId
full join DisplayStringDimvw DSC1 on iq.EnumTypeId = dsc1.BaseManagedEntityId
where dsc.LanguageCode = 'ENU' and inc.CreatedDate is not null and dsc1.LanguageCode = 'ENU'

Comments

  • Conner_WoodConner_Wood Customer Ninja IT Monkey ✭✭✭✭
    Your query has quite a few flaws in it for what you desire, my Russian Comrade.
    Fear not, like the hunting birds of past nobles you shall fly swiftly to your goal by my hand!
    USE [DWDataMart];
    
    WITH AffectedUserFact_Latest AS (
    	SELECT AffectedUserFact.[WorkItemDimKey], MAX(AffectedUserFact.[CreatedDate]) as 'MaxCreatedDate'
    	from [dbo].[WorkItemAffectedUserFactvw] AffectedUserFact (nolock)
    	group by AffectedUserFact.WorkItemDimKey
    ),
    AssignedUserFact_Latest AS (
    	SELECT AssignedUserFact.[WorkItemDimKey], MAX(AssignedUserFact.[CreatedDate]) as 'MaxCreatedDate'
    	from [dbo].[WorkItemAssignedToUserFactvw] AssignedUserFact (nolock)
    	group by AssignedUserFact.WorkItemDimKey
    )
    SELECT IR.Id
     ,AssignedUser.DisplayName as 'Assigned To'
     ,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), IR.CreatedDate) AS 'Created Date (Local Time)'
     ,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), IR.ClosedDate) AS 'Closed Date (Local Time)'
     ,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), IR.ResolvedDate) AS 'Resolved Date (Local Time)'
     ,AffectedUser.DisplayName as 'Affected User'
     ,AffectedUser.Department
     ,AffectedUser.EmployeeId
     ,AffectedUser.Company
     ,AffectedUser.Office
     ,incs.IncidentStatusValue
     ,dsc.DisplayName as 'Classification'
     ,dsc1.DisplayName as 'Support Group'
     ,left(IR.id,2) as 'Ticket Prefix'
    FROM [dbo].[IncidentDimvw] IR
    
    INNER JOIN [dbo].[WorkItemDimvw] WI
    ON IR.EntityDimKey = WI.EntityDimKey
    
    LEFT JOIN AffectedUserFact_Latest
    ON AffectedUserFact_Latest.WorkItemDimKey = WI.WorkItemDimKey
    
    LEFT JOIN 
    (
    	SELECT AffectedFact.WorkItemDimKey, AffectedFact.CreatedDate, AffectedUser.DisplayName, AffectedUser.Department, AffectedUser.EmployeeId, AffectedUser.Company, AffectedUser.Office
    	FROM [dbo].[WorkItemAffectedUserFactvw] AffectedFact (nolock)
    	JOIN [dbo].[UserDimvw] AffectedUser (nolock)
    	ON AffectedUser.UserDimKey = AffectedFact.WorkItemAffectedUser_UserDimKey
    ) AS AffectedUser ON AffectedUser.WorkItemDimKey = WI.WorkItemDimKey AND AffectedUser.CreatedDate = AffectedUserFact_Latest.MaxCreatedDate
    
    
    LEFT JOIN AssignedUserFact_Latest
    ON AssignedUserFact_Latest.WorkItemDimKey = WI.WorkItemDimKey
    
    LEFT JOIN 
    (
    	SELECT AssignedFact.WorkItemDimKey, AssignedFact.CreatedDate, Assignee.DisplayName
    	FROM [dbo].[WorkItemAssignedToUserFactvw] AssignedFact (nolock)
    	JOIN [dbo].[UserDimvw] Assignee (nolock)
    	ON Assignee.UserDimKey = AssignedFact.WorkItemAssignedToUser_UserDimKey
    ) AS AssignedUser ON AssignedUser.WorkItemDimKey = WI.WorkItemDimKey AND AssignedUser.CreatedDate = AssignedUserFact_Latest.MaxCreatedDate
    
    
    full join IncidentClassificationvw IC on IR.Classification_IncidentClassificationId = ic.IncidentClassificationId
    full join IncidentStatus INCS on IR.Status_IncidentStatusId= incs.IncidentStatusId
    full join IncidentTierQueuesvw IQ on IR.TierQueue_IncidentTierQueuesId = iq.IncidentTierQueuesId
    full join DisplayStringDimvw DSC on ic.EnumTypeId = dsc.BaseManagedEntityId
    full join DisplayStringDimvw DSC1 on iq.EnumTypeId = dsc1.BaseManagedEntityId
    
    WHERE dsc.LanguageCode = 'ENU' and IR.CreatedDate IS NOT NULL AND dsc1.LanguageCode = 'ENU'
    ORDER BY IR.CreatedDate
    I have focused on the user relationships to ensure you get the latest relationship, you may want to also check for {[IsDeleted] IS NOT NULL} on "AffectedFact" and "AssignedFact" but that was not specified as part of your criteria, only that you get the latest user relationship, regardless of if it was marked as deleted or not.

    ..... ( Fly! .... skreeeeeaghh!!!!!! )
  • Bostjan_CernjakBostjan_Cernjak Customer IT Monkey ✭
    Hi Conner,
    Thank you for this great solution.
    PS : and i do speak slovan language but i come from beautiful country of Slovenia (I feel SLOVEnia) . http://www.slovenia.info/?lng=1

    regards and:wink:
    Boštjan
Sign In or Register to comment.