Get opened and closed workitems per day

Stephane_Bouillon

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

Stephane_Bouillon

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_Ross

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_Bouillon

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_Bouillon

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:

Date	Action	Workitem	Opened	Closed
2017-10-21	New
2017-10-22	New
2017-10-23	Closed	IN15324 - 0052-Facture via le worflow NOK	2017-10-06T09:12:27.403	2017-10-23T07:02:55
2017-10-23	New	IN17590 - 454 - Webreservation	2017-10-23T08:32:59.41	2017-10-25T13:19:28
2017-10-23	Processed	IN17526 - 147-Mycarenet nok	2017-10-23T07:26:05.013	2017-10-23T09:36:36
2017-10-23	Processed	IN17531 - 438-MO qui ne s'affichent pas	2017-10-23T07:33:01.213	2017-10-23T10:08:28
2017-10-23	Processed	IN17626 - 046 - nieuwe cnk code aanmaken	2017-10-23T10:02:38.337	2017-10-23T10:04:19
2017-10-23	Processed	IN17681 - 138 phitouch reste figé dès qu'elle veut mettre la ristourne au patient	2017-10-23T13:29:19.037	2017-10-23T13:53:14
2017-10-23	Closed	IN15976 - 090-PROBLEME INTERNET , mycarenet bc nok	2017-10-11T07:49:37.417	2017-10-23T14:02:10
2017-10-23	Closed	IN17415 - 718-Lecteur carte ID NOK	2017-10-20T10:00:52.67	2017-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_Etzel

Hello Stephane,

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

Thanks,

Brett

Stephane_Bouillon

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_Bouillon

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_Bouillon

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

Stephane_Bouillon

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_Etzel

Both work great! Thank you very much, Stephane.

Brett_Etzel

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_Etzel

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

Peter_Miklian

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_Bouillon

These are my settings: