Home SQL Server

SCSM DW Query - Billable Time

Adam_DzyackyAdam_Dzyacky Customer Contributor Monkey ✭✭✭✭✭
edited June 2016 in SQL Server
The Time Tracker App rocks. So in the event you're using it and still messing around with SQL queries (or just looking to have a starting point) I figured this was worth sharing. The following SQL query is to be run against your DWDataMart and will retrieve the BillableTime against Incidents, Manual Activities, Review Activities, and Change Requests. It probably goes without saying, but said query could then be dropped into Report Builder and then published into the SCSM Console. That way you could have a pie chart for the analyst/date range that you pick to show where most of their work is going. I think combined with the awesomesauce fantastic post answer made by @joivan_hedrick on integrating the Time Tracker App into the portal you stand to gain some worthwhile data to mine.

The following takes 3 parameters:
StartDate (datetime)
EndDate (datetime)
Username (string)


SELECT Activity.id AS [Id]
  , Activity.Title AS [Title]
  , CASE
   WHEN Activity.Id LIKE 'MA%' THEN 'Manual Activity'
   WHEN Activity.Id LIKE 'RA%' THEN 'Review Activity'
   ELSE 'Other Activity'
  END AS [Work Item Type]
  , ActivityAreaStrings.DisplayName [Class/Area]
  , AssignedUser.DisplayName AS [Worked By]
  , BT.TimeInMinutes AS [Minutes Worked]
FROM  ActivityDimvw AS Activity
INNER JOIN WorkItemDimvw AS WI ON Activity.EntityDimKey = WI.EntityDimKey
INNER JOIN WorkItemHASBillableTimeFactvw AS WIBT ON WI.WorkItemDimKey = WIBT.WorkItemDimKey
INNER JOIN BillableTimeDimvw AS BT ON WIBT.WorkItemHASBillableTime_BillableTimeDimKey = BT.BillableTimeDimKey
INNER JOIN BillableTimeHasWorkingUserFactvw AS BTWU ON wibt.WorkItemHASBillableTime_BillableTimeDimKey = btwu.BillableTimeDimKey
INNER JOIN UserDimvw AS AssignedUser ON BTWU.BillableTimeHASWorkingUser_UserDimKey = AssignedUser.UserDimKey
INNER JOIN  ActivityAreavw AS ActivityAreaOutrigger ON ActivityAreaOutrigger.ActivityAreaId = Activity.Area_ActivityAreaId
 INNER JOIN DisplayStringDimvw ActivityAreaStrings ON ActivityAreaStrings.BaseManagedEntityId = ActivityAreaOutrigger.EnumTypeId
WHERE Activity.CreatedDate > @StartDate
   AND Activity.CreatedDate < @EndDate
   AND bt.TimeInMinutes IS NOT NULL
   AND AssignedUser.UserName = @Analyst
   AND ActivityAreaStrings.LanguageCode = 'ENU'
UNION
SELECT CR.Id
  , CR.Title
  , 'Change Request'
  , CRAreaStrings.DisplayName
  , AssignedUser.DisplayName
  , bt.TimeInMinutes
FROM  ChangeRequestDimvw CR
INNER JOIN WorkItemDimvw WI ON WI.EntityDimKey = CR.EntityDimKey
INNER JOIN WorkItemHASBillableTimeFactvw WIBT ON WI.WorkItemDimKey = WIBT.WorkItemDimKey
INNER JOIN BillableTimeDimvw BT ON WIBT.WorkItemHASBillableTime_BillableTimeDimKey = BT.BillableTimeDimKey
INNER JOIN BillableTimeHASWorkingUserFactvw BTWU ON wibt.WorkItemHASBillableTime_BillableTimeDimKey = BTWU.BillableTimeDimKey
INNER JOIN UserDimvw AssignedUser ON BTWU.BillableTimeHASWorkingUser_UserDimKey = AssignedUser.UserDimKey
INNER JOIN ChangeAreavw CRAreaOutrigger ON CRAreaOutrigger.ChangeAreaId = CR.Area_ChangeAreaId
 INNER JOIN DisplayStringDimvw CRAreaStrings ON CRAreaOutrigger.EnumTypeId = CRAreaStrings.BaseManagedEntityId
WHERE CR.CreatedDate > @StartDate
  AND CR.CreatedDate < @EndDate
  AND BT.TimeInMinutes IS NOT NULL
  AND AssignedUser.UserName = @Analyst
  AND CRAreaStrings.LanguageCode = 'ENU'
UNION
select ir.id, WI.Title, 'Incident', irClassStrings.DisplayName, AssignedUser.DisplayName, bt.TimeInMinutes
from incidentdimvw AS ir
 INNER JOIN WorkItemDimvw AS wi ON wi.EntityDimKey = ir.EntityDimKey
 INNER JOIN WorkItemHASBillableTimeFactvw AS wibt ON wi.WorkItemDimKey = wibt.WorkItemDimKey
 INNER JOIN BillableTimeDimvw AS bt ON wibt.WorkItemHASBillableTime_BillableTimeDimKey = bt.BillableTimeDimKey
 INNER JOIN BillableTimeHASWorkingUserFactvw AS btwu ON wibt.WorkItemHasBillableTime_BillableTimeDimKey = btwu.BillableTimeDimKey
 INNER JOIN UserDimvw AS AssignedUser ON btwu.BillableTimeHASWorkingUser_UserDimKey = AssignedUser.UserDimKey
 INNER JOIN IncidentClassificationvw AS irClassOutrigger ON ir.ClASsificatiON_IncidentClASsificatiONId = irClassOutrigger.IncidentClASsificatiONId
  INNER JOIN DisplayStringDimvw AS irClassStrings ON irClassOutrigger.enumtypeid = irClassStrings.BaseManagedEntityId
where ((ir.CreatedDate >= @StartDate) and (ir.CreatedDate < @EndDate + 1))
 and AssignedUser.UserName = @Analyst
 and irClassStrings.LanguageCode = 'ENU'
 AND BT.TimeInMinutes IS NOT NULL
ORDER BY [Id]


Comments

  • AJ_WittenbrinkAJ_Wittenbrink Customer IT Monkey ✭
    edited June 2017
    Nice work!

    I have been using a more, aggregate approach (I port data to a staging etc..) based on each work item.  Within the pull I use a sub-select query to pull aggregate time and number of analysts against a work item (I use WorkItemDimvw view (alias of WI) to tie it in).

    I will probably steal your code once the requests for more detail comes in.

    --Billable Time	(SELECT 	SUM(CAST(BT.TimeInMinutes AS DECIMAL(10,3))) / 60	FROM BillableTimeDimvw BT		LEFT OUTER JOIN WorkItemHasBillableTimeFactvw WI2BT
    			ON WI2BT.WorkItemHasBillableTime_BillableTimeDimKey = BT.BillableTimeDimKey	WHERE WI2BT.WorkItemDimKey = WI.WorkItemDimKey	GROUP BY WI2BT.WorkItemDimKey		) AS 'Analyst Effort',
    
Sign In or Register to comment.