Home SQL Server

Time Worked report w/support group and created date

Liz_RossLiz_Ross Customer IT Monkey ✭
edited December 2019 in SQL Server

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!


Best Answers

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    Answer ✓

    @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_RossLiz_Ross Customer IT Monkey ✭
    Answer ✓

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

Answers

  • Liz_RossLiz_Ross Customer IT Monkey ✭

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

  • Jeff_LangJeff_Lang Customer Ninja IT Monkey ✭✭✭✭

    @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_RossLiz_Ross Customer IT Monkey ✭

    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_RossLiz_Ross Customer IT Monkey ✭

    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_RossLiz_Ross Customer IT Monkey ✭

    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_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    Answer ✓

    @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_RossLiz_Ross Customer IT Monkey ✭
    Answer ✓

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

  • Liz_RossLiz_Ross Customer IT Monkey ✭

    @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_RossLiz_Ross Customer IT Monkey ✭

    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_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    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_RossLiz_Ross Customer IT Monkey ✭

    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_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

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

  • Liz_RossLiz_Ross Customer IT Monkey ✭
    edited January 2020

    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_RossLiz_Ross Customer IT Monkey ✭

    @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?

Sign In or Register to comment.