ALTER proc [dbo].[report_Resourses1]
@XmlParam xml = null,
@DrillDownField varchar(max) = null,
@DrillDownParams varchar(max) = null,
@UserID int
AS
BEGIN
DECLARE @Date date
SET @Date=dateadd(m,-1,getdate())
DECLARE @workhour int
DECLARE @workday date
SET @workday = DATEADD(day, -Day(getdate())+1, SysDateTime())
SET @workhour=([dbo].[tc_DiffWorkingHours]( DATEADD (month, -1, @workday),@workday))
SELECT 'Задачи подразделения' nam,
m.fact*100/m.minutall allTasks,
m.SurName
FROM
(SELECT u.SurName,
SUM(ISNULL(f.amount,0)) fact,
@workhour*60 minutall
FROM TaskResourceFactEntries f
JOIN UserNames u WITH(NOLOCK) ON u.UserId = f.PerformerUserId
JOIN Tasks t WITH(NOLOCK) ON t.TaskID = f.TaskId
JOIN [UserOrgStructureUnit] uo ON uo.UserID = u.UserID
LEFT JOIN [Subcategories] sub ON sub.SubcatID = t.SubcatID
WHERE sub.CategoryID IN (111,222,333) AND -- список ID категорий, принадлежащих подразделению
month(f.date) = month(@Date) AND year(f.date) = year(@Date) AND
uo.OrgStructureUnitId IN (88,99) -- список ID орг. единицы сотрудников подразделения
GROUP BY u.SurName
) m
UNION
SELECT 'Задачи из других разделов',
m.fakcat*100/m.minutall,
m.SurName
FROM (SELECT u.SurName,
@workhour*60 minutall,
ISNULL((SELECT SUM(ISNULL(f1.Amount,0))
FROM TaskResourceFactEntries f1
JOIN UserNames u1 WITH(NOLOCK) ON u1.UserId = f1.PerformerUserId
JOIN Tasks t1 WITH(NOLOCK) ON t1.TaskID = f1.TaskId
JOIN [UserOrgStructureUnit] uo1 ON uo1.UserID=u1.UserID
LEFT JOIN [Subcategories] sub1 ON sub1.SubcatID=t1.SubcatID
WHERE sub1.CategoryID NOT IN (111,222,333) AND
month(f1.date) = month(@Date) AND year(f1.date) = year(@Date) AND
uo1.OrgStructureUnitId in (88,99) AND u.SurName=u1.SurName),0) fakcat
FROM TaskResourceFactEntries f
JOIN UserNames u WITH(NOLOCK) ON u.UserId = f.PerformerUserId
JOIN Tasks t WITH(NOLOCK) ON t.TaskID = f.TaskId
JOIN [UserOrgStructureUnit] uo ON uo.UserID = u.UserID
WHERE month(f.date) = month(@Date) AND year(f.date) = year(@Date) AND
uo.OrgStructureUnitId IN (88,99)
GROUP BY u.SurName
) m
END
|
