USE ICP;
GO

DECLARE @beginDatum Datetime
DECLARE @eindDatum Datetime

SELECT dtSums.Tijdsduur, dtSums.WrapUp, dtSums.OnHold, dtSums.Tijdsduur - dtSums.WrapUp - dtSums.OnHold AS Talk, 
	dtSums.Ticket_ID, dtSums.Ticket_ID, dtSums.Useraccount_First_Name, dtSums.Useraccount_Last_Name, dtSums.Description, dtSums.Ticket_Accepted_DateTime, dtSums.Ticket_Closed_DateTime
FROM (
	SELECT T.Ticket_ID, UA.Useraccount_First_Name, UA.Useraccount_Last_Name, A.Description, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTime,
		DATEDIFF(second, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTime) AS Tijdsduur, SUM(CONVERT(DECIMAL(4, 3),
		TA.Ticket_Action_Description)) AS WrapUp, SUM(CONVERT(DECIMAL(4, 3), TAA.Ticket_Action_Description)) AS OnHold
	FROM Tickets AS T 
		LEFT OUTER JOIN Application_Useraccounts AS UA ON T.Ticket_Accepted_Useraccount_ID = UA.Useraccount_ID 
		LEFT OUTER JOIN Tickets_Actions AS TA ON T.Ticket_ID = TA.Ticket_ID AND TA.Ticket_Action_Type_ID = 3 
		LEFT OUTER JOIN Tickets_Actions AS TAA ON T.Ticket_ID = TAA.Ticket_ID AND TAA.Ticket_Action_Type_ID = 4 
		INNER JOIN Authorities AS A ON T.Authority_ID = A.Authority_ID
	WHERE (T.Ticket_Closed_DateTime BETWEEN @beginDatum AND DATEADD(day, 1, @eindDatum))
	GROUP BY T.Ticket_ID, UA.Useraccount_First_Name, UA.Useraccount_Last_Name, A.Description, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTime
	) AS dtSums 
ORDER BY dtSums.Useraccount_First_Name, dtSums.Useraccount_Last_Name, dtSums.Description, dtSums.Ticket_Accepted_DateTime, Ticket_Closed_DateTime