1. SQL / Говнокод #2676

    −151.7

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    39. 39
    40. 40
    41. 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 в систему.

    Запостил: dzver, 26 Февраля 2010

    Комментарии (8) RSS

    Добавить комментарий