IT Monkey:   Join the Cireson Community today for your chance to win $50!

Get opened and closed workitems per day

Stephane_BouillonStephane_Bouillon Customer 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 (
        SELECT
         EventDate = CONVERT(DATE,'09/01/2017')
        UNION ALL SELECT
         EventDate = DATEADD(DAY, 1, EventDate)
        FROM
         Dates
        WHERE
         EventDate < '10/31/2017'
) SELECT
 EventDate
FROM
 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 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 Super IT Monkey ✭✭✭✭✭
    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 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 (
            SELECT
             EventDate = DATEADD(DAY,-9,GETDATE())
            UNION ALL SELECT
             EventDate = DATEADD(DAY, 1, EventDate)
            FROM
             Dates
            WHERE
             EventDate < GETDATE()
    ) SELECT
     EventDate, (SELECT COUNT(wi.id) FROM workitem wi  WHERE wi.closeddate > CONVERT(DATE,EventDate) AND wi.closeddate < DATEADD(DAY, 1, EventDate)) [Closed], (SELECT COUNT(wi.id) FROM workitem wi  WHERE wi.created > CONVERT(DATE,EventDate) AND wi.created < DATEADD(DAY, 1, EventDate)) [Opened]
    FROM
     Dates OPTION (MAXRECURSION 100)
    Which gives me the table output I need:


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

    WITH Dates AS (
            SELECT
             EventDate = DATEADD(MONTH,-1,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'
    ELSE 'New' 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)
    OPTION (MAXRECURSION 100)
    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

Sign In or Register to comment.