Get opened and closed workitems per day
WITH Dates AS (<br> SELECT<br> EventDate = CONVERT(DATE,'09/01/2017')<br> UNION ALL SELECT<br> EventDate = DATEADD(DAY, 1, EventDate)<br> FROM<br> Dates<br> WHERE<br> EventDate < '10/31/2017'<br>) SELECT<br> EventDate<br>FROM<br> Dates OPTION (MAXRECURSION 100)
This gives me the following output
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 ?