Time Worked report w/support group and created date

Liz_Ross

Hi all, so I'm working off a great query, which gives:

the assigned user's name,

the SR or IR #,

the category ,

title,

and time worked on each request (the "time worked" which shows on the Results or Resolution tab of any SR or IR, which is manually entered by analysts).

It's run against Service Manager. However there's a couple things I'm trying to figure out.

1) Can the time be converted to Hours in the query so I don't have to calculate it later in Excel formulas?

2) How to also pull in the assigned user's support group?

3) How to pull in Created Date

4) How to remove billable time portion of the query without breaking it.

Any help would be awesome, thank you!

All SR-IR Time Worked.txt

Justin_Workman

@Liz_Ross - Support group of the user will be challenging as there's not a relationship for that in the ServiceManager database. SupportGroup is a property of the Work Item itself, not the user so the support group that's present in the result set will be the support group of the work item in real time.

Here's an updated query with the time in the format I believe you're after as well as the initial created date of the work items:

with WorkItems as

(select sr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'WorkItemId', 

BaseManagedEntityId As 'Id', 

Area_0E2739F7_6889_09EF_B260_35CD954D3563 as 'Category',

Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 as 'Title',

sr.SupportGroup_766D07C2_2680_CD38_4638_D3A7813E6488 as 'SupportGroupId',

sr.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as 'Created'

 from MT_System$WorkItem$ServiceRequest sr

union all

select Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'WorkItemId', 

BaseManagedEntityId As 'Id', 

Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA as 'Category',

Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 as 'Title',

i.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C as 'SupportGroupId',

i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as 'Created'

from MT_System$WorkItem$Incident i

)

select wi.WorkItemId, wi.Title,

wi.Created,

lt.LTValue as 'Category',

btuc.DisplayName as 'Analyst',

sg.LTValue as 'SupportGroup',

--bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 as 'TotalTimeWorked',

case when bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 >= 60 THEN (

SELECT CAST(bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 / 60 as varchar(2)) + ':' +

case when (bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 % 60) > 0 THEN

case when (bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 % 60) < 10 THEN

'0' + cast((bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 % 60) as varchar(2))

else 

cast((bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 % 60) as varchar(2)) END

else '00'

end) 

else 

case when (bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 < 10) then

'00:0' + cast((bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 % 60) as varchar(2))

else '00:' + cast((bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 % 60) as varchar(2))

end

end as 'TotalTimeWorked'

from MT_System$WorkItem$BillableTime bt

join Relationship btr

on btr.TargetEntityId = bt.BaseManagedEntityId

join WorkItems wi

on btr.SourceEntityId = wi.Id

join Relationship btu

on btu.SourceEntityId = bt.BaseManagedEntityId

join MTV_System$Domain$User btuc

on btuc.BaseManagedEntityId = btu.TargetEntityId

join LocalizedText lt

on lt.LTStringId = wi.Category

and lt.LanguageCode = 'enu'

and lt.LTStringType = 1

join LocalizedText sg

on sg.LTStringId = wi.SupportGroupId

and sg.LanguageCode = 'enu'

and sg.LTStringType = 1

Liz_Ross

Sorry I took a while to get back - that worked perfectly thank you Justin!!!!!

Liz_Ross

Also thanks Justin Workman for all of his help so far!!

Jeff_Lang

@Liz_Ross

try changing

"bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 as 'TotalTimeWorked'"

to

"CONVERT(varchar(12), DATEADD(minute, bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0, 0), 114) as 'TotalTimeWorked'"

Liz_Ross

https://community.cireson.com/discussion/comment/19829#Comment_19829

Thanks Jeff! It's kind of giving too much info though if I really just need hours & minutes, any way to cut it off?

Example:

Liz_Ross

Oh also I don't actually need the "billable time" portion, I'm just not SQL savvy so I'm not sure where I can start taking that out without messing up the whole query.

Liz_Ross

Did a quick updated to the first post, looking for:

1) Can the time be converted to Hours in the query so I don't have to calculate it later in Excel formulas?

2) How to also pull in the assigned user's support group?

3) How to pull in Created Date

4) how to remove "billable time" portion of the query

Justin_Workman

@Liz_Ross - Support group of the user will be challenging as there's not a relationship for that in the ServiceManager database. SupportGroup is a property of the Work Item itself, not the user so the support group that's present in the result set will be the support group of the work item in real time.

Here's an updated query with the time in the format I believe you're after as well as the initial created date of the work items:

with WorkItems as

(select sr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'WorkItemId', 

BaseManagedEntityId As 'Id', 

Area_0E2739F7_6889_09EF_B260_35CD954D3563 as 'Category',

Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 as 'Title',

sr.SupportGroup_766D07C2_2680_CD38_4638_D3A7813E6488 as 'SupportGroupId',

sr.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as 'Created'

 from MT_System$WorkItem$ServiceRequest sr

union all

select Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'WorkItemId', 

BaseManagedEntityId As 'Id', 

Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA as 'Category',

Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 as 'Title',

i.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C as 'SupportGroupId',

i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as 'Created'

from MT_System$WorkItem$Incident i

)

select wi.WorkItemId, wi.Title,

wi.Created,

lt.LTValue as 'Category',

btuc.DisplayName as 'Analyst',

sg.LTValue as 'SupportGroup',

--bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 as 'TotalTimeWorked',

case when bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 >= 60 THEN (

SELECT CAST(bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 / 60 as varchar(2)) + ':' +

case when (bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 % 60) > 0 THEN

case when (bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 % 60) < 10 THEN

'0' + cast((bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 % 60) as varchar(2))

else 

cast((bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 % 60) as varchar(2)) END

else '00'

end) 

else 

case when (bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 < 10) then

'00:0' + cast((bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 % 60) as varchar(2))

else '00:' + cast((bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 % 60) as varchar(2))

end

end as 'TotalTimeWorked'

from MT_System$WorkItem$BillableTime bt

join Relationship btr

on btr.TargetEntityId = bt.BaseManagedEntityId

join WorkItems wi

on btr.SourceEntityId = wi.Id

join Relationship btu

on btu.SourceEntityId = bt.BaseManagedEntityId

join MTV_System$Domain$User btuc

on btuc.BaseManagedEntityId = btu.TargetEntityId

join LocalizedText lt

on lt.LTStringId = wi.Category

and lt.LanguageCode = 'enu'

and lt.LTStringType = 1

join LocalizedText sg

on sg.LTStringId = wi.SupportGroupId

and sg.LanguageCode = 'enu'

and sg.LTStringType = 1

Liz_Ross

Sorry I took a while to get back - that worked perfectly thank you Justin!!!!!

Liz_Ross

@Justin_Workman Not sure if you (or anyone else) can help, or if I should submit an actual ticket -

I noticed the query won't pull up any current requests that I enter time worked into, even though our system admin says all tickets should be writing to the Service Manager DB daily..But the only tickets the query is pulling up in SQL Management Studio (like 6 - 10) AREN'T actually showing up if I search for them in the Service Manager 2019 Console under Work Items (SR or IRs), they ONLY show up in the Cireson web portal. 

So I'm not sure where the disconnect is or if this is a sign that we have a bigger problem with data not actually writing to Service Manager DB...

Liz_Ross

Does every field have to have data for this to work? For example, if some don't have a category should they still show up in this list?

Justin_Workman

It sounds like maybe you're looking at two different databases? You mention the work items don't show up in the console, but do in the portal. If you click on them in the portal do they open up?

Liz_Ross

Okay so we must have fixed something because now they are showing in the console. Sorry about that.

So now the only question is why the ones I'm adding time worked to in the past 1 - 2 months will not show up in the query? Some (many) don't have an area/classification, not sure if that's a factor.

Justin_Workman

Does the time show up on the work item either in the console or in the portal?

Liz_Ross

Okay so:

1. The query results in SQL mgmt studio show me 1 SR and 11 IR's from the past 2 - 3 years. The most recent is from March 2019.
2. I can see the time worked on all of these in the Portal
3. I can ONLY see the time worked on the 11 IR's in the Console.
4. I see no time worked field on any SR in the console, even ones not filled in with a time. Not sure where that field should be? It's not on the resolution tab...
5. I confirmed that the time I just added to another IR in the Portal showed up in the Console, BUT I reran the query and it doesn't show up in the query results. And there are a few other ones I've definitely added time worked to from the past couple months that also aren't showing up in the query results either.

Liz_Ross

@Justin_Workman I did just check the MT_System$WorkItem$BillableTime table and in the top 1000 rows there's at least 49 entries for time in minutes, including the ones I updated today and a couple days ago. So it seems like for some reason the query isn't pulling all in that table?