- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
SELECT
CASE WHEN Subtask.ParentTask_ID = 1
THEN 0 ELSE CASE WHEN
(
SELECT SUM([Percent]) AS SumOfPercent
FROM Reports GROUP BY Zadanie_ID
HAVING (Zadanie_ID = SubTask.SubTask_ID)) IS NULL
THEN 0 ELSE (SELECT SUM([Percent]) AS SumOfPercent
FROM Reports GROUP BY Zadanie_ID
HAVING (Zadanie_ID = SubTask.SubTask_ID)
) END
END
AS SumOfPercent,
CASE WHEN Subtask.isContinued <> 1
THEN ((persons_1.Baza / 0.25) * (
CASE WHEN Subtask.dateEnding IS NULL
THEN CAST(SubTask.SubTask_EndDate - DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS integer)
ELSE CAST(SubTask.SubTask_EndDate - SubTask.DateEnding AS integer)
END -
DATEDIFF(ww, CASE WHEN Subtask.dateending IS NOT NULL THEN Subtask.dateending ELSE getdate() END,
SubTask.SubTask_EndDate) * 2) / 8 * CAST( Priority.Priority_Name AS numeric) / 1000)
ELSE CASE WHEN ((persons_1.Baza / 0.25)* (CASE WHEN Subtask.dateEnding IS NULL THEN
CAST(SubTask.SubTask_EndDate - DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS integer)
ELSE CAST(SubTask.SubTask_EndDate - SubTask.DateEnding AS integer)END -
DATEDIFF(ww, CASE WHEN Subtask.dateending IS NOT NULL THEN Subtask.dateending ELSE getdate() END,
SubTask.SubTask_EndDate) * 2)/ 8 * CAST( Priority.Priority_Name AS numeric)/ 1000) > 0 THEN 0
ELSE (persons_1.Baza / 0.25) * (CASE WHEN Subtask.dateEnding IS NULL
THEN CAST(SubTask.SubTask_EndDate - DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS integer)
ELSE CAST(SubTask.SubTask_EndDate - SubTask.DateEnding AS integer) END -
DATEDIFF(ww, CASE WHEN Subtask.dateending IS NOT NULL THEN Subtask.dateending ELSE getdate() END,
SubTask.SubTask_EndDate) * 2) / 8 * CAST( Priority.Priority_Name AS numeric) / 1000 END END AS timebonus,
CASE WHEN Subtask.isContinued <> 1 THEN (CASE WHEN (CASE WHEN SubTask.executed = 0
THEN CAST(Subtask.Subtask_EndDate - DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS int)
ELSE CAST(Subtask.Subtask_EndDate - Subtask.DateEnding AS int) END >= 0)
THEN (0.40 * (persons_1.Baza * CASE WHEN
(SELECT SUM([Hours]) AS sumHours FROM Reports where (isreal=1) or (isReal=null) GROUP BY Zadanie_ID
HAVING (Zadanie_ID = Subtask.Subtask_ID)) IS NULL THEN 0 ELSE
(SELECT SUM([Hours]) AS sumHours FROM Reports where (isreal=1) or (isReal=null) GROUP BY Zadanie_ID
HAVING (Zadanie_ID = Subtask.Subtask_ID)) END))
* (CAST( Priority.Priority_Name AS numeric) / 1000) ELSE 0 END) ELSE 0 END AS PriorityBonus
...
Я знаю, это трудно понять, но это алгоритм расчета правду о Жизни, Вселенной и Всего Такого. Это SQL выполняется на каждом Login в систему.
Комментарии (8) RSS