Limit Report to a list of Technicians
Hi,
I have a report which tells me what Technicians have done last month. Currently this lists all Technicians. I want to limit it to a list of Technicians, so I can produce reports to Team Managers for their team only. Can anyone tell me how I can do this? I have tried several ways but I have run out of ideas.
This is the query:
select top 5000 AaaUser.FIRST_NAME 'Technician',
InboundTable.INBOUND 'Inbound',
CompletedTable.COMPLETED 'Completed',
((CompletedTable.COMPLETED*100)/InboundTable.INBOUND) 'Completed %',
SlaViolatedTable.SLA_VIOLATED 'SLA Violated Count' ,
( (SlaViolatedTable.SLA_VIOLATED*100)/CompletedTable.COMPLETED ) 'SLA Violated %' ,
SlaNotViolatedTable.SLA_NOT_VIOLATED 'SLA Not Violated',
( (SlaNotViolatedTable.SLA_NOT_VIOLATED*100)/CompletedTable.COMPLETED ) '% SLA Not Violated'
FROM Helpdeskcrew
LEFT JOIN AaaUser ON Helpdeskcrew.TECHNICIANID = AaaUser.USER_ID
LEFT JOIN
(
SELECT WorkOrderStates.OWNERID,
COUNT(WorkOrder.WORKORDERID) 'INBOUND' FROM WorkOrder
LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID
WHERE
WorkOrder.CREATEDTIME >= <from_lastmonth>
AND
WorkOrder.CREATEDTIME <= <to_lastmonth>
GROUP BY WorkOrderStates.OWNERID
) AS InboundTable ON Helpdeskcrew.TECHNICIANID = InboundTable.OWNERID
LEFT JOIN
(
SELECT WorkOrderStates.OWNERID,
COUNT(WorkOrder.WORKORDERID) 'COMPLETED' FROM WorkOrder
LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID
WHERE
WorkOrder.COMPLETEDTIME >= <from_lastmonth>
AND
WorkOrder.COMPLETEDTIME <= <to_lastmonth>
AND
WorkOrder.COMPLETEDTIME > 0
GROUP BY WorkOrderStates.OWNERID
) AS CompletedTable ON Helpdeskcrew.TECHNICIANID = CompletedTable.OWNERID
LEFT JOIN
(
SELECT WorkOrderStates.OWNERID,
COUNT(WorkOrder.WORKORDERID) 'SLA_VIOLATED' FROM WorkOrder
LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID
WHERE
WorkOrder.COMPLETEDTIME >= <from_lastmonth>
AND
WorkOrder.COMPLETEDTIME <= <to_lastmonth>
AND
WorkOrder.COMPLETEDTIME > 0
AND
WorkOrderStates.ISOVERDUE = 1
GROUP BY WorkOrderStates.OWNERID
) AS SlaViolatedTable ON Helpdeskcrew.TECHNICIANID = SlaViolatedTable.OWNERID
LEFT JOIN
(
SELECT WorkOrderStates.OWNERID,
COUNT(WorkOrder.WORKORDERID) 'SLA_NOT_VIOLATED' FROM WorkOrder
LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID
WHERE
WorkOrder.COMPLETEDTIME >= <from_lastmonth>
AND
WorkOrder.COMPLETEDTIME <= <to_lastmonth>
AND
WorkOrder.COMPLETEDTIME > 0
AND
WorkOrderStates.ISOVERDUE = 0
GROUP BY WorkOrderStates.OWNERID
) AS SlaNotViolatedTable ON Helpdeskcrew.TECHNICIANID = SlaNotViolatedTable.OWNERID
ORDER BY (SlaViolatedTable.SLA_VIOLATED*100)/CompletedTable.COMPLETED desc, (SlaNotViolatedTable.SLA_NOT_VIOLATED*100)/CompletedTable.COMPLETED desc
I have a report which tells me what Technicians have done last month. Currently this lists all Technicians. I want to limit it to a list of Technicians, so I can produce reports to Team Managers for their team only. Can anyone tell me how I can do this? I have tried several ways but I have run out of ideas.
This is the query:
select top 5000 AaaUser.FIRST_NAME 'Technician',
InboundTable.INBOUND 'Inbound',
CompletedTable.COMPLETED 'Completed',
((CompletedTable.COMPLETED*100)/InboundTable.INBOUND) 'Completed %',
SlaViolatedTable.SLA_VIOLATED 'SLA Violated Count' ,
( (SlaViolatedTable.SLA_VIOLATED*100)/CompletedTable.COMPLETED ) 'SLA Violated %' ,
SlaNotViolatedTable.SLA_NOT_VIOLATED 'SLA Not Violated',
( (SlaNotViolatedTable.SLA_NOT_VIOLATED*100)/CompletedTable.COMPLETED ) '% SLA Not Violated'
FROM Helpdeskcrew
LEFT JOIN AaaUser ON Helpdeskcrew.TECHNICIANID = AaaUser.USER_ID
LEFT JOIN
(
SELECT WorkOrderStates.OWNERID,
COUNT(WorkOrder.WORKORDERID) 'INBOUND' FROM WorkOrder
LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID
WHERE
WorkOrder.CREATEDTIME >= <from_lastmonth>
AND
WorkOrder.CREATEDTIME <= <to_lastmonth>
GROUP BY WorkOrderStates.OWNERID
) AS InboundTable ON Helpdeskcrew.TECHNICIANID = InboundTable.OWNERID
LEFT JOIN
(
SELECT WorkOrderStates.OWNERID,
COUNT(WorkOrder.WORKORDERID) 'COMPLETED' FROM WorkOrder
LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID
WHERE
WorkOrder.COMPLETEDTIME >= <from_lastmonth>
AND
WorkOrder.COMPLETEDTIME <= <to_lastmonth>
AND
WorkOrder.COMPLETEDTIME > 0
GROUP BY WorkOrderStates.OWNERID
) AS CompletedTable ON Helpdeskcrew.TECHNICIANID = CompletedTable.OWNERID
LEFT JOIN
(
SELECT WorkOrderStates.OWNERID,
COUNT(WorkOrder.WORKORDERID) 'SLA_VIOLATED' FROM WorkOrder
LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID
WHERE
WorkOrder.COMPLETEDTIME >= <from_lastmonth>
AND
WorkOrder.COMPLETEDTIME <= <to_lastmonth>
AND
WorkOrder.COMPLETEDTIME > 0
AND
WorkOrderStates.ISOVERDUE = 1
GROUP BY WorkOrderStates.OWNERID
) AS SlaViolatedTable ON Helpdeskcrew.TECHNICIANID = SlaViolatedTable.OWNERID
LEFT JOIN
(
SELECT WorkOrderStates.OWNERID,
COUNT(WorkOrder.WORKORDERID) 'SLA_NOT_VIOLATED' FROM WorkOrder
LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID
WHERE
WorkOrder.COMPLETEDTIME >= <from_lastmonth>
AND
WorkOrder.COMPLETEDTIME <= <to_lastmonth>
AND
WorkOrder.COMPLETEDTIME > 0
AND
WorkOrderStates.ISOVERDUE = 0
GROUP BY WorkOrderStates.OWNERID
) AS SlaNotViolatedTable ON Helpdeskcrew.TECHNICIANID = SlaNotViolatedTable.OWNERID
ORDER BY (SlaViolatedTable.SLA_VIOLATED*100)/CompletedTable.COMPLETED desc, (SlaNotViolatedTable.SLA_NOT_VIOLATED*100)/CompletedTable.COMPLETED desc