February 15, 2013 at 8:35 am
Hi,
I have the following query:
USE ICP;
GO
DECLARE @beginDatum Datetime
DECLARE @eindDatum Datetime
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
ORDER BY UA.Useraccount_First_Name, UA.Useraccount_Last_Name, A.Description, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTime
Now I need to add another column in the result based on a calculation using other aliases:
Tijdsduur - WrapUp - OnHold AS Talk
What's the best way to accomplish this?
I tried following query, but that does not work:
USE ICP;
GO
DECLARE @beginDatum Datetime
DECLARE @eindDatum Datetime
SELECT Tijdsduur, WrapUp, OnHold, Tijdsduur - WrapUp - OnHold AS Talk, T.Ticket_ID, UA.Useraccount_First_Name, UA.Useraccount_Last_Name, A.Description, T.Ticket_Accepted_DateTime, T.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) AS dtSums
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
ORDER BY UA.Useraccount_First_Name, UA.Useraccount_Last_Name, A.Description, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTime
February 17, 2013 at 3:11 pm
You should put the whole initial query into the derived table, except for ORDER BY clause:
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
_____________
Code for TallyGenerator
February 28, 2013 at 4:37 am
Thank you very much Sergiy. This query works great and as expected.
Thank you very much for your help.
Geert
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply