DW and problemws with assigned to user

Bostjan_Cernjak

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'

Conner_Wood

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_Cernjak

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
Boštjan