I have a faults table which contains the ticket information for calls raised by our I.T. Service Desk.

Faults table:

There is also a calendar table, which has the date information. It has an entry per day.

Calendar table:

I want to be able to report on the number of tickets per month, grouped by team, that were still open (any status where the 'datecleared' is not filled in) at the end of that month.

So if ticket 1234 was raised in June and didn't get closed until September, there would be an entry for it in June, July and August figures.

This needs grouping by team and a column for each month, for the last 6 months.

This would be the desired output if we were using the example fault table shown at the start.

Desired output:

I've attempted some solutions, like Open Ticket Count Per Day, however there are some functions (like variables) that I'm stuck with. Also I don't know what's possible with Microsoft SQL Server 2017.

The SQL below only brings back tickets that were raised in a month and still remained open in that same month. (It's not tracking tickets from previous months.)

SELECT top 10000
    first_day_of_month as [FDOM],
    convert(nvarchar(10), Last_Day_Of_Month, 103) as [Last Day of the Month],
    count(faultid) AS [Number of Tickets],
    team
FROM
    Calendar
LEFT JOIN Faults
ON DateOccured BETWEEN first_day_of_month AND last_day_of_month
WHERE 
    Date_Day = 1
    AND dateoccured<last_day_of_month
    AND datecleared>last_day_of_month
GROUP BY first_day_of_month, last_day_of_month, team
ORDER BY last_day_of_month desc

CodePudding user response：

It sounds like you want to do a pivot. Example:

SELECT TOP (10000)
    team,
    March,
    April,
    May,
    June,
    July,
    August
FROM (
    SELECT team, month_nm, faultid
    FROM Calendar
    LEFT JOIN Faults ON DateOccured BETWEEN first_day_of_month AND last_day_of_month
    WHERE Date_Day = 1
    AND dateoccured<last_day_of_month
    AND datecleared>last_day_of_month
) cal
PIVOT
(
    COUNT(faultid)
    FOR month_nm IN (March, April, May, June, July, August)
) pvt;

This takes a header column (the months in the year in your example) and a value column (the count of tickets) and "rotates" them, so the header column becomes the column names, and the aggregate on the value column becomes their values.

This would work fine as long as your report only covers a single year. However, since you only want the last 6 months, you would need to generate the query as dynamic SQL in order to avoid having to manually update the query every month, since the columns in a pivot have to be explicitly named.