Home Analytics

Get opened and closed workitems per day

Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭
I've been researching how I can configure a SQL Table Widget so that it shows how many workitems have been created and closed the same day for the last two months. I've started with generating a row for each date using the following statement

WITH Dates AS (<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EventDate = CONVERT(DATE,'09/01/2017')<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UNION ALL SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EventDate = DATEADD(DAY, 1, EventDate)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dates<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EventDate < '10/31/2017'<br>) SELECT<br>&nbsp;EventDate<br>FROM<br>&nbsp;Dates OPTION (MAXRECURSION 100)

This gives me the following output

2017-09-01T00:00:00
2017-09-02T00:00:00
2017-09-03T00:00:00
...
2017-10-31T00:00:00

I now want to add SQL code to add a column counting the number of workitems that were created on that day, and a column indicating the number that were closed. I'm struggling with the syntax to get this output. Can anybody give me a pointer ?

Thanks, Stephane

Answers

  • Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭
    My goal is to achieve a graph that looks a bit like the one I created in Excel where the blue bars are number of closed items and the red line is the number of created items for any given day. Bonus points for adding the trend line ;)


  • Geoff_RossGeoff_Ross Cireson Consultant O.G.
    Hi @Stephane_Bouillon,

    I'm not sure that exact graph is possible with a widget. You may need two graphs one for Created and one for Completed.

    The following may help, it returns data with a row for each work item and the month it was completed (you could tweak this to be day). Then create a SQL Chart widget with this query and group by Date.

    SELECT WorkItemId Id, RIGHT('00' + CAST(DATEPART (mm,CompletedDate) AS varchar(2)), 2) + ' ' + DATENAME (year,CompletedDate) [Date]
    FROM WorkItem
    If thats not right or you do need all on one graph, then a custom page would be required.

    Geoff
  • Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭
    I have made some progress to show the Opened and Closed workitems for the past 10 days in a single table. I still need to take the time into account, but I'll find how to do that. The more important next step is to add a chart, but I can't find the right settings for the best chart type and the group by parameters. My SQL statement looks like this:

    WITH Dates AS (<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EventDate = DATEADD(DAY,-9,GETDATE())<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UNION ALL SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EventDate = DATEADD(DAY, 1, EventDate)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dates<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EventDate < GETDATE()<br>) SELECT<br>&nbsp;EventDate, (SELECT COUNT(wi.id) FROM workitem wi&nbsp; WHERE wi.closeddate > CONVERT(DATE,EventDate) AND wi.closeddate < DATEADD(DAY, 1, EventDate)) [Closed], (SELECT COUNT(wi.id) FROM workitem wi&nbsp; WHERE wi.created > CONVERT(DATE,EventDate) AND wi.created < DATEADD(DAY, 1, EventDate)) [Opened]<br>FROM<br>&nbsp;Dates OPTION (MAXRECURSION 100)
    Which gives me the table output I need:


  • Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭
    So, I finally have a query that gives me the data.

    WITH Dates AS (<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EventDate = DATEADD(MONTH,-1,CONVERT(DATE,GETDATE()))<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UNION ALL SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EventDate = DATEADD(DAY, 1, EventDate)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dates<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EventDate < CONVERT(DATE,GETDATE())<br>)<br>SELECT FORMAT (EventDate, 'yyyy-MM-dd') [Date], CASE WHEN CONVERT(DATE, wi.closeddate) = EventDate AND CONVERT(DATE, wi.created) = EventDate THEN 'Processed' <br>WHEN CONVERT(DATE, wi.closeddate) = EventDate THEN 'Closed'<br>ELSE 'New' END AS [Action], wi.DisplayName [Workitem], wi.created [Opened], wi.closeddate [Closed]<br>FROM Dates<br>LEFT JOIN workitem wi ON EventDate=CONVERT(DATE,wi.closeddate) OR EventDate=CONVERT(DATE,wi.created)<br>OPTION (MAXRECURSION 100)<br>
    This gives me a table like this:

    DateActionWorkitemOpenedClosed
    2017-10-21New
    2017-10-22New
    2017-10-23ClosedIN15324 - 0052-Facture via le worflow NOK2017-10-06T09:12:27.4032017-10-23T07:02:55
    2017-10-23NewIN17590 - 454 - Webreservation2017-10-23T08:32:59.412017-10-25T13:19:28
    2017-10-23ProcessedIN17526 - 147-Mycarenet nok2017-10-23T07:26:05.0132017-10-23T09:36:36
    2017-10-23ProcessedIN17531 - 438-MO qui ne s'affichent pas 2017-10-23T07:33:01.2132017-10-23T10:08:28
    2017-10-23ProcessedIN17626 - 046 - nieuwe cnk code aanmaken2017-10-23T10:02:38.3372017-10-23T10:04:19
    2017-10-23ProcessedIN17681 - 138 phitouch reste figé dès qu'elle veut mettre la ristourne au patient2017-10-23T13:29:19.0372017-10-23T13:53:14
    2017-10-23ClosedIN15976 - 090-PROBLEME INTERNET , mycarenet bc nok2017-10-11T07:49:37.4172017-10-23T14:02:10
    2017-10-23ClosedIN17415 - 718-Lecteur carte ID NOK2017-10-20T10:00:52.672017-10-23T14:55:15


    When I use this query as input for a SQL Chart Widget using the Stacked Column type, grouped by Date and stacked by Action, I get the following output:

    I'm not entirely satisfied, because I'd prefer a graph that shows the Opened and Closed not stacked but in separate columns. I suppose I'd suggest that as a feature request. Also I haven't found an elegant solution to show items that have been opened and closed the same day in both the New and Closed buckets, which is why I used the Processed bucket as a workaround.

    I'm very new to all this, and the past few days I've learned there is some really cool stuff you can do, but the chart widget is still lacking some basic functionality that would make it so much more useful.

    Stephane

  • Brett_EtzelBrett_Etzel Customer IT Monkey ✭

    Hello Stephane,

    Would you be willing to share the SQL query you used to create the stacked chart above?

    Thanks,

    Brett

  • Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭

    Of course, here it is. This only deals with incident requests (classid). The tierid selects only the support groups of the IT department, which is why I added those classid's in as well.

    I did start to notice a few small misalignments with the actual numbers that I suspect are due to tickets that have been open for a very long time (over a year)

    ------------------------------------------------

    WITH Dates AS ( SELECT EventDate = DATEADD(MONTH,-2,CONVERT(DATE,GETDATE())) UNION ALL SELECT EventDate = DATEADD(DAY, 1, EventDate) FROM Dates WHERE EventDate < CONVERT(DATE,GETDATE()))

    SELECT EventDate [Date],

    (SELECT COUNT(wi.id) FROM workitem wi

    WHERE CONVERT(DATE,wi.created) <= EventDate AND wi.ClassId= 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' AND

    wi.tierid IN ('4f2aa56c-ea20-3111-601a-7c5a3c00b13a','c52cfd69-7557-66ab-cbec-9d5fca856150','c2ad2601-2fa5-08af-a018-6f3bf3be0723','7598539c-77f6-48f9-8a01-d2cc68a8623f','df3896f5-3145-0546-4d25-e485de6765af')) [Opened],

    (SELECT COUNT(wi.id) FROM workitem wi 

    WHERE CONVERT(DATE,wi.closedDate) <= EventDate AND wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' AND

    wi.tierid IN ('4f2aa56c-ea20-3111-601a-7c5a3c00b13a','c52cfd69-7557-66ab-cbec-9d5fca856150','c2ad2601-2fa5-08af-a018-6f3bf3be0723','7598539c-77f6-48f9-8a01-d2cc68a8623f','df3896f5-3145-0546-4d25-e485de6765af')) [Closed],

     (SELECT COUNT(wi.id) - 

     (SELECT COUNT(wi.id) FROM workitem wi

      WHERE CONVERT(DATE,wi.closedDate) <= EventDate AND 

      wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' AND

      wi.tierid IN ('4f2aa56c-ea20-3111-601a-7c5a3c00b13a','c52cfd69-7557-66ab-cbec-9d5fca856150','c2ad2601-2fa5-08af-a018-6f3bf3be0723','7598539c-77f6-48f9-8a01-d2cc68a8623f','df3896f5-3145-0546-4d25-e485de6765af'))

    FROM workitem wi

    WHERE CONVERT(DATE,wi.created) <= EventDate AND wi.ClassId= 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' AND

    wi.tierid IN ('4f2aa56c-ea20-3111-601a-7c5a3c00b13a','c52cfd69-7557-66ab-cbec-9d5fca856150','c2ad2601-2fa5-08af-a018-6f3bf3be0723','7598539c-77f6-48f9-8a01-d2cc68a8623f','df3896f5-3145-0546-4d25-e485de6765af')) [Backlog] FROM Dates

    Group By EventDate

    OPTION (MAXRECURSION 500)

    --------------------------------------------------------------------------

    This is good enough for us, but I'm quite sure the performance could be optimized by a real dba. I had to move along and work on other priorities. If you find a better way, please let me know.

    Stephane

  • Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭

    So, to give a bit more context:

    Opened is the incidents that were created today, but have not (yet) been closed.

    Closed are the incidents that were created before today, and that have been closed today.

    Backlog / Processed is the number of tickets that were created today and have been closed the same day

    Stephane

  • Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭

    Wait, I may have given you the SQL for the trend histogram


  • Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭

    So the correct SQL for the bar chart is:

    -------------------------------------------------------------

    WITH Dates AS (SELECT EventDate = DATEADD(MONTH,-2,CONVERT(DATE,GETDATE())) UNION ALL SELECT EventDate = DATEADD(DAY, 1, EventDate) FROM Dates WHERE EventDate < CONVERT(DATE,GETDATE()))


    SELECT FORMAT (EventDate, 'yyyy-MM-dd') [Date], CASE WHEN CONVERT(DATE, wi.closeddate) = EventDate AND CONVERT(DATE, wi.created) = EventDate THEN 'Processed' 

    WHEN CONVERT(DATE, wi.closeddate) = EventDate THEN 'Closed' 

    WHEN CONVERT(DATE, wi.created) = EventDate THEN 'New' 

    ELSE '' END AS [Action], wi.DisplayName [Workitem], wi.created [Opened], wi.closeddate [Closed]

    FROM Dates

    LEFT JOIN workitem wi ON (EventDate=CONVERT(DATE,wi.closeddate) OR EventDate=CONVERT(DATE,wi.created)) AND (wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' OR wi.ClassId = '04B69835-6343-4DE2-4B19-6BE08C612989') AND wi.tierid IN ('4f2aa56c-ea20-3111-601a-7c5a3c00b13a','0b29d425-97c1-bf7e-37dc-aa35f28c68ba','c52cfd69-7557-66ab-cbec-9d5fca856150','0e16b089-65ef-592b-927e-b9cd20403487','52fa3b51-66d1-b307-e747-9b129516df15','c2ad2601-2fa5-08af-a018-6f3bf3be0723','1b1da19a-8f18-b135-24d0-a35a5a2f3f47','7598539c-77f6-48f9-8a01-d2cc68a8623f','dceb5efa-8989-5977-4c48-1518aeca5bf2','df3896f5-3145-0546-4d25-e485de6765af')

    OPTION (MAXRECURSION 100)

    -------------------------------------------------------------------------------

    Take what you need,

    Stephane

  • Brett_EtzelBrett_Etzel Customer IT Monkey ✭

    Both work great! Thank you very much, Stephane.

  • Brett_EtzelBrett_Etzel Customer IT Monkey ✭

    I'm having trouble making since of how to setup the SQL Chart Widget. Any guidance you'd be willing to pass along on that? Thanks!


  • Brett_EtzelBrett_Etzel Customer IT Monkey ✭

    Failed to mention, that the above is for your "trend histogram" query.

  • Peter_MiklianPeter_Miklian Customer Advanced IT Monkey ✭✭✭

    I've read similar post recently: https://cireson.com/blog/dashboards-300

    It would be very cool if Cireson would implement all those nice graphs into default Dashboards/Analytics of future portal versions 😉

  • Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭

    These are my settings:


Sign In or Register to comment.