I have a fairly specific set of data I am trying to display.
I have a dataset of helpdesk ticket data. I would like to display a line chart that shows tickets opened and tickets closed by day, over the last 30 days. I've been able to display one of these just fine, by adding an expression to my Category group:
The 'Created' line displays just fine, yet the 'Closed' line seems to jump around at random. It seems that I can't further group by day on the 'Closed' column; this breaks the chart even further. Here's an example image:
Please let me know if I haven't been clear in any way.
EDIT: I would like to be clear that I've explored the option of creating a new dataset for this chart. I've been able to group counts by day, but only for 'Created' or 'Closed', not both. For example:
If there's a way to combine these into one SQL query, that would fix my problem.
Here's is the query that has given me the data I need. Thanks, mmarie!
WITH X AS (SELECT TOP (30) n = ROW_NUMBER() OVER (ORDER BY m1.number) - 1 FROM [master].dbo.spt_values AS m1 CROSS JOIN [master].dbo.spt_values AS m2), last30days AS (SELECT dt = cast(DATEADD(DAY, (- 1 * n), getdate()) AS date) FROM X) SELECT dt.dt, CASE WHEN tcr.CreatedCount IS NULL THEN 0 ELSE tcr.CreatedCount END AS 'CreatedCount', CASE WHEN tcl.ClosedCount IS NULL THEN 0 ELSE tcl.ClosedCount END AS 'ClosedCount' FROM last30days dt LEFT JOIN (SELECT COUNT(a.TicketNbr) AS CreatedCount, CAST(a.date_entered AS date) AS 'DayCreated' FROM v_rpt_Service a WHERE (closed_by LIKE @tech + '%') GROUP BY CAST(date_entered AS date)) tcr ON dt.dt = tcr.DayCreated LEFT JOIN (SELECT COUNT(a.TicketNbr) AS ClosedCount, CAST(a.date_closed AS date) AS 'DayClosed' FROM v_rpt_Service a WHERE (closed_by LIKE @tech + '%') GROUP BY CAST(date_closed AS date)) tcl ON dt.dt = tcl.DayClosed