fsv2k5
Advanced Member | Редактировать | Профиль | Сообщение | ICQ | Цитировать | Сообщить модератору
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
|
Всего записей: 1050 | Зарегистр. 30-04-2005 | Отправлено: 11:07 08-10-2007 | Исправлено: Maz, 11:26 19-04-2019 |
|
