Hi,

I have trying to combine two queries in one. Firstly, I'm looking tickets assigned to specific technician and in the second one, I try to get the data of the field "elapsed time".

 

If  separated each one, the are working well so I understand the problem is in the combination. Can anyone help me?

 

Thanks in advance,

 

SELECT wo.WORKORDERID "Request ID",qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",wof.UDF_CHAR2 "Invoiced Society " FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID WHERE  (ti.FIRST_NAME = N'Laura Sanjuan Quer' COLLATE SQL_Latin1_General_CP1_CI_AS)  AND wo.ISPARENT=1
UNION
SELECT wo.WORKORDERID "Request ID",max(aau.FIRST_NAME) "Requester",max(wo.TITLE) "Subject",SUM(rc.MM2COMPLETEREQUEST)/3600000 "Sum of Time Elapsed in HRS" FROM WorkOrder wo LEFT JOIN RequestCharges rc ON wo.WORKORDERID=rc.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID WHERE (wo.ISPARENT=1) and (dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,'2012-09-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,'2012-09-30 23:59',21))
GROUP BY wo.workorderid