Maz
Дед Мазай | Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору MSSQL - Структура: Спец-назн-закрыто-надо закрыть - '1970-01-01 02:00:00' - это GMT+2 (Можно через dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (@dateValue/1000),'1970-01-01 00:00:00')) - <from_yesterday> - <to_yesterday> - за вчера (В справочной информации Редактора запросов есть доп. инфа) Запрос: SELECT aaauser.first_name "Специалист", COUNT(workorder.workorderid)"Назначено", COUNT(closed.woclosed)"Закрыто", (COUNT(workorder.workorderid)- COUNT(closed.woclosed)) "Надо закрыть" FROM workorder LEFT JOIN (SELECT workorder.workorderid wodelay FROM workorder WHERE (workorder.completedtime IS NULL OR workorder.completedtime = 0) AND ROUND(CAST(DATEADD(ss,workorder.duebytime/1000,'1970-01-01 02:00:00')AS float),0,1) < ROUND(CAST(GETDATE() AS float),0,1) AND (workorder.duebytime >= 0) )delay ON workorder.workorderid = delay.wodelay LEFT JOIN (SELECT workorder.workorderid woclosed FROM workorder INNER JOIN workorderstates ON workorder.workorderid = workorderstates.workorderid WHERE workorderstates.statusid = 3) closed ON workorder.workorderid = closed.woclosed LEFT JOIN workorderstates ON workorder.workorderid = workorderstates.workorderid LEFT JOIN aaauser ON workorderstates.ownerid = aaauser.user_id LEFT JOIN workorder_queue ON workorder.workorderid = workorder_queue.WORKORDERID LEFT JOIN queuedefinition ON workorder_queue.QUEUEID = queuedefinition.QUEUEID WHERE ( (workorder.createdtime != 0) AND (workorder.createdtime IS NOT NULL) ) AND (workorder.createdtime >= <from_yesterday>) AND (workorder.createdtime <= <to_yesterday>) AND queuedefinition.queuename = 'Название Вашей Группы' GROUP BY aaauser.first_name |