SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Tickets(
  TicketID INT,
  StatusID INT,
  AffectationDate DATE
);

INSERT INTO Tickets VALUES
(1, 1, '2017-01-01'),
(1, 2, '2017-01-02'),
(2, 1, '2017-01-03'),
(2, 2, '2017-01-01'),
(2, 3, '2017-01-02'),
(3, 1, '2017-01-02'),
(3, 1, '2017-01-04'),
(3, 2, '2017-01-05'),
(3, 2, '2017-01-04'),
(3, 3, '2017-01-05');

10 rows affected

SELECT TicketID,
       AffectationDate,
       SUM(CASE WHEN StatusID = 1 THEN 1 ELSE 0 END) InProgress,
       SUM(CASE WHEN StatusID = 2 THEN 1 ELSE 0 END) OuOfTime,
       SUM(CASE WHEN StatusID = 3 THEN 1 ELSE 0 END) Closed,
       COUNT(1) Total
FROM Tickets
GROUP BY TicketID,
         AffectationDate
ORDER BY TicketID,
         AffectationDate;


TicketID
AffectationDate
InProgress
OuOfTime
Closed
Total


1
01/01/2017 00:00:00
1
0
0
1

1
02/01/2017 00:00:00
0
1
0
1

2
01/01/2017 00:00:00
0
1
0
1

2
02/01/2017 00:00:00
0
0
1
1

2
03/01/2017 00:00:00
1
0
0
1

3
02/01/2017 00:00:00
1
0
0
1

3
04/01/2017 00:00:00
1
1
0
2

3
05/01/2017 00:00:00
0
1
1
2


SELECT AffectationDate,
       SUM(CASE WHEN StatusID = 1 THEN 1 ELSE 0 END) TotalInProgress,
       SUM(CASE WHEN StatusID = 2 THEN 1 ELSE 0 END) TotalOutOfTime,
       SUM(CASE WHEN StatusID = 3 THEN 1 ELSE 0 END) TotalClosed,
       COUNT(1) TotalStatusThisDate
FROM Tickets
GROUP BY AffectationDate
ORDER BY AffectationDate;



AffectationDate
TotalInProgress
TotalOutOfTime
TotalClosed
TotalStatusThisDate


01/01/2017 00:00:00
1
1
0
2

02/01/2017 00:00:00
1
1
1
3

03/01/2017 00:00:00
1
0
0
1

04/01/2017 00:00:00
1
1
0
2

05/01/2017 00:00:00
0
1
1
2

TicketID	AffectationDate	InProgress	OuOfTime	Closed	Total
1	01/01/2017 00:00:00	1	0	0	1
1	02/01/2017 00:00:00	0	1	0	1
2	01/01/2017 00:00:00	0	1	0	1
2	02/01/2017 00:00:00	0	0	1	1
2	03/01/2017 00:00:00	1	0	0	1
3	02/01/2017 00:00:00	1	0	0	1
3	04/01/2017 00:00:00	1	1	0	2
3	05/01/2017 00:00:00	0	1	1	2