Add Unique WorkItem Value to Billable Time Table

Daniel_Polivka1

I'd like to see a value in the Billable Time Table in the ServiceManagement DB that allows for easy lookup of the Work Item ID that the billable time is related to. Right now, these two tables don't share any commonality between themselves, and it seems like a simple oversight that would be capable of producing literally any reports and analytics surrounding billable time.

1. workitem
2. cachert.csnCached_MT_System_WorkItem_BillableTime

Justin_Workman

@Daniel_Polivka1 - The Cached_MT_System_WorkItem_BillableTime table isn't purposely built in the same way that the WorkItem table is. It is just a cache of the MT_System$WorkItem$BillableTime table from ServiceManager. Your best bet for matching billable time to work items is using ServiceManager:

SELECT i.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C 'WorkItemId'
,bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 'TimeInMinutes'
,u.DisplayName 'WorkingUser'
FROM
MT_System$WorkItem$BillableTime bt WITH (NOLOCK)
JOIN Relationship wr WITH (NOLOCK)
ON wr.TargetEntityId = bt.BaseManagedEntityId
JOIN MT_System$WorkItem$Incident i WITH (NOLOCK)
ON wr.SourceEntityId = i.BaseManagedEntityId
JOIN Relationship ur WITH (NOLOCK)
ON ur.SourceEntityId = bt.BaseManagedEntityId
JOIN MT_System$Domain$User u WITH (NOLOCK)
ON ur.TargetEntityId = u.BaseManagedEntityId

Daniel_Polivka1

Thank you!! I have been playing in the ServiceManagement DB quite a bit, but haven't really plunged into the SCSM DB yet. I wanted to get all the work items that could have billable time, so i did a quick and dirty UNION ALL with your query. Not sure if there's a better way, but your piece gave me that step I was missing. Thanks again!

SELECT 
IR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'ID',
BT.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 as 'Time Worked',
USR.DisplayName as 'Analyst',
BT.LastUpdated_A33608CA_CD35_3278_8D5D_0F44B01717F1 as 'Last Updated'
FROM 
MT_System$WorkItem$BillableTime as BT WITH (NOLOCK)
JOIN Relationship as WR WITH (NOLOCK) ON WR.TargetEntityId = BT.BaseManagedEntityId
JOIN MT_System$WorkItem$Incident as IR WITH (NOLOCK) ON WR.SourceEntityId = IR.BaseManagedEntityId
JOIN Relationship as UR WITH (NOLOCK) ON UR.SourceEntityId = BT.BaseManagedEntityId
JOIN MT_System$Domain$User as USR WITH (NOLOCK) ON ur.TargetEntityId = USR.BaseManagedEntityId

UNION ALL

SELECT 
CR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'ID',
BT.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 as 'Time Worked',
USR.DisplayName as 'Analyst',
BT.LastUpdated_A33608CA_CD35_3278_8D5D_0F44B01717F1 as 'Last Updated'
FROM 
MT_System$WorkItem$BillableTime as BT WITH (NOLOCK)
JOIN Relationship as WR WITH (NOLOCK) ON WR.TargetEntityId = BT.BaseManagedEntityId
JOIN MT_System$WorkItem$ChangeRequest as CR WITH (NOLOCK) ON WR.SourceEntityId = CR.BaseManagedEntityId
JOIN Relationship as UR WITH (NOLOCK) ON UR.SourceEntityId = BT.BaseManagedEntityId
JOIN MT_System$Domain$User as USR WITH (NOLOCK) ON ur.TargetEntityId = USR.BaseManagedEntityId

UNION ALL

SELECT 
PR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'ID',
BT.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 as 'Time Worked',
USR.DisplayName as 'Analyst',
BT.LastUpdated_A33608CA_CD35_3278_8D5D_0F44B01717F1 as 'Last Updated'
FROM 
MT_System$WorkItem$BillableTime as BT WITH (NOLOCK)
JOIN Relationship as WR WITH (NOLOCK) ON WR.TargetEntityId = BT.BaseManagedEntityId
JOIN MT_System$WorkItem$Problem as PR WITH (NOLOCK) ON WR.SourceEntityId = PR.BaseManagedEntityId
JOIN Relationship as UR WITH (NOLOCK) ON UR.SourceEntityId = BT.BaseManagedEntityId
JOIN MT_System$Domain$User as USR WITH (NOLOCK) ON ur.TargetEntityId = USR.BaseManagedEntityId

UNION ALL

SELECT 
SR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'ID',
BT.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 as 'Time Worked',
USR.DisplayName as 'Analyst',
BT.LastUpdated_A33608CA_CD35_3278_8D5D_0F44B01717F1 as 'Last Updated'
FROM 
MT_System$WorkItem$BillableTime as BT WITH (NOLOCK)
JOIN Relationship as WR WITH (NOLOCK) ON WR.TargetEntityId = BT.BaseManagedEntityId
JOIN MT_System$WorkItem$ServiceRequest as SR WITH (NOLOCK) ON WR.SourceEntityId = SR.BaseManagedEntityId
JOIN Relationship as UR WITH (NOLOCK) ON UR.SourceEntityId = BT.BaseManagedEntityId
JOIN MT_System$Domain$User as USR WITH (NOLOCK) ON ur.TargetEntityId = USR.BaseManagedEntityId