Q: Microsoft SQL Server 2017 - producing a count of tickets that are still open per month by team I have a faults table which contains the ticket information for calls raised by our I.T. Service Desk.
Faults table:




DateOccured
Datecleared
Status
Team
Faultid




10/06/2022
04/09/2022
Closed
Service Desk
1234


11/06/2022

With user
Networks
1235


01/07/2022
06/08/2022
Closed
D&D
1236


04/07/2022
01/08/2022
Closed
Networks
1237


04/07/2022

Open
D&D
1238


24/07/2022

Open
D&D
1239


04/08/2022

Open
D&D
1240


04/08/2022

Open
D&D
1241


04/08/2022

Open
D&D
1242




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




date_id
date_year
month_nm
first_day_of_month
last_day_of_month
date_day




01/06/2022
2022
June
01/06/2022
30/06/2022
1




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:




Team
June
July
August
September




Service Desk
1
1
1
0


Networks
1
2
1
1


D&D
0
3
5
5




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


A: 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.
