Руководство администратора

ALTER proc [dbo].[report_Resourses]
  @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.fact,
    m.minutall
FROM (SELECT SUM(ISNULL(f.amount,0)) fact,
     @workhour*60*ISNULL((SELECT COUNT(UserID) FROM [UserOrgStructureUnit] WHERE OrgStructureUnitId IN (88,99)),0) 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 орг. единицы сотрудников подразделения
  ) m
 
UNION
 
SELECT 'Задачи из других разделов',
    m.fakcat*100/m.minutall,
    m.fakcat,
    m.minutall
FROM (SELECT @workhour*60*ISNULL((SELECT COUNT(UserID) FROM [UserOrgStructureUnit] WHERE OrgStructureUnitId IN (88,99)),0) minutall,
        ISNULL((SELECT SUM(ISNULL(f.Amount,0))
              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 NOT IN (111,222,333) AND
              month(f.date) = month(@Date) AND year(f.date) = year(@Date) AND
              uo.OrgStructureUnitId IN (88,99)),
              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 (2260,2681)
  ) m
 
UNION
 
SELECT 'Оставшееся время',
     100-m.fakcat*100/m.minutall-m.fact*100/m.minutall,
    m.fakcat,
    m.minutall
FROM (SELECT SUM(ISNULL(f.amount,0)) fact,
        @workhour*60*ISNULL((SELECT COUNT(UserID) FROM [UserOrgStructureUnit] WHERE OrgStructureUnitId IN (2260,2681)),0) minutall,
        ISNULL((SELECT SUM(ISNULL(f.Amount,0))
          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 NOT IN (111,222,333) AND
          month(f.date) = month(@Date) AND year(f.date) = year(@Date) AND
          uo.OrgStructureUnitId IN (88,99))
        ,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
  LEFT JOIN [Subcategories] sub ON sub.SubcatID=t.SubcatID
  WHERE sub.CategoryID IN (111,222,333) AND
  month(f.date) = month(@Date) AND year(f.date) = year(@Date) AND
  uo.OrgStructureUnitId IN (88,99)
  ) m
END