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)

  2. SQL / Говнокод #2674

    −154.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
    WITH days(num, aday) AS 
(
	SELECT 1 AS num, 1 AS aday
	UNION ALL 
	SELECT num, aday+1 AS aday FROM days
	WHERE days.[aday] < DAY( DATEADD(DAY, -DAY(@date), DATEADD(MONTH, 1 , @date)))
)
	SELECT     SUM(day1) AS d1, SUM(day2) AS d2, SUM(day3) AS d3, SUM(day4) AS d4, SUM(day5) AS d5, SUM(day6) AS d6, SUM(day7) AS d7, SUM(day8) AS d8, 
						  SUM(day9) AS d9, SUM(day10) AS d10, SUM(day11) AS d11, SUM(day12) AS d12, SUM(day13) AS d13, SUM(day14) AS d14, SUM(day15) AS d15, 
						  SUM(day16) AS d16, SUM(day17) AS d17, SUM(day18) AS d18, SUM(day19) AS d19, SUM(day20) AS d20, SUM(day21) AS d21, SUM(day22) AS d22, 
						  SUM(day23) AS d23, SUM(day24) AS d24, SUM(day25) AS d25, SUM(day26) AS d26, SUM(day27) AS d27, SUM(day28) AS d28, SUM(day29) AS d29, 
						  SUM(day30) AS d30, SUM(day31) AS d31
	FROM         (SELECT     CASE aday WHEN 1 THEN 1 ELSE 0 END AS day1, CASE aday WHEN 2 THEN 2 ELSE 0 END AS day2, 
												  CASE aday WHEN 3 THEN 3 ELSE 0 END AS day3, CASE aday WHEN 4 THEN 4 ELSE 0 END AS day4, 
												  CASE aday WHEN 5 THEN 5 ELSE 0 END AS day5, CASE aday WHEN 6 THEN 6 ELSE 0 END AS day6, 
												  CASE aday WHEN 7 THEN 7 ELSE 0 END AS day7, CASE aday WHEN 8 THEN 8 ELSE 0 END AS day8, 
												  CASE aday WHEN 9 THEN 9 ELSE 0 END AS day9, CASE aday WHEN 10 THEN 10 ELSE 0 END AS day10, 
												  CASE aday WHEN 11 THEN 11 ELSE 0 END AS day11, CASE aday WHEN 12 THEN 12 ELSE 0 END AS day12, 
												  CASE aday WHEN 13 THEN 13 ELSE 0 END AS day13, CASE aday WHEN 14 THEN 14 ELSE 0 END AS day14, 
												  CASE aday WHEN 15 THEN 15 ELSE 0 END AS day15, CASE aday WHEN 16 THEN 16 ELSE 0 END AS day16, 
												  CASE aday WHEN 17 THEN 17 ELSE 0 END AS day17, CASE aday WHEN 18 THEN 18 ELSE 0 END AS day18, 
												  CASE aday WHEN 19 THEN 19 ELSE 0 END AS day19, CASE aday WHEN 20 THEN 20 ELSE 0 END AS day20, 
												  CASE aday WHEN 21 THEN 21 ELSE 0 END AS day21, CASE aday WHEN 22 THEN 22 ELSE 0 END AS day22, 
												  CASE aday WHEN 23 THEN 23 ELSE 0 END AS day23, CASE aday WHEN 24 THEN 24 ELSE 0 END AS day24, 
												  CASE aday WHEN 25 THEN 25 ELSE 0 END AS day25, CASE aday WHEN 26 THEN 26 ELSE 0 END AS day26, 
												  CASE aday WHEN 27 THEN 27 ELSE 0 END AS day27, CASE aday WHEN 28 THEN 28 ELSE 0 END AS day28, 
												  CASE aday WHEN 29 THEN 29 ELSE 0 END AS day29, CASE aday WHEN 30 THEN 30 ELSE 0 END AS day30, 
												  CASE aday WHEN 31 THEN 31 ELSE 0 END AS day31
						   FROM          days) AS col

    Думаю в комментариях не нуждается T_T.

    Karasb, 26 Февраля 2010

    Комментарии (0)

  3. SQL / Говнокод #2537

    −149.2

    1. 1
    2. 2
    3. 3
    4. 4
    create table money_transaction(
...
summa clob
);

    подготовка к гиперинфляции

    dim1r, 03 Февраля 2010

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

  4. SQL / Говнокод #2518

    −859.4

    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
    SET @DD = DATEPART(dd, GETDATE())
SET @MM = DATEPART(mm, GETDATE())
SET @YYYY = DATEPART(yyyy, GETDATE())

IF LEN(@DD) = '1'
	BEGIN
		SET @DD = '0' + @DD
	END
IF LEN(@MM) = '1'
	BEGIN
		SET @MM = '0' + @MM
	END

SET @DT_D = @DD + '.' + @MM + '.' + @YYYY

    Изврат по превращению текущей даты в нужный формат в MSSQL

    Junior, 01 Февраля 2010

    Комментарии (1)

  5. SQL / Говнокод #2451

    −861.2

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    6. 6
    7. 7
    8. 8
    9. 9
    SELECT * FROM obj WHERE telephon LIKE '***' AND off = 0;

SELECT obj.* FROM 
obj 
INNER JOIN obj2ls ON obj.ObjID = obj2ls.ObjKod AND obj2ls.Bdate <= CURDATE() AND obj2ls.Edate >= CURDATE() AND obj2ls.off = 0 
INNER JOIN ls ON obj2ls.LsKod = ls.lsid AND ls.off = 0
INNER JOIN ls2contr ON ls.lsid = ls2contr.lskod AND ls2contr.bdate <= CURDATE() AND ls2contr.edate >= CURDATE() AND ls2contr.off = 0
INNER JOIN contr ON ls2contr.contrkod = contr.contrid AND contr.off = 0
WHERE obj.telephon LIKE '***' AND obj.off = 0 AND contr.contr_type = 2;

    О плохом проектировании базы данных.
    Был простой запрос, к нему добавили проверку 1го(!) доп. условия.
    Еще умиляют JOINы в духе *Kod = *Id.

    Mihard, 19 Января 2010

    Комментарии (1)

  6. SQL / Говнокод #2442

    −852.1

    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
    CREATE TABLE account(
	balance INT DEFAULT 0,
	name VARCHAR(60) NOT NULL,
	surname VARCHAR(60) DEFAULT NULL,
	login VARCHAR(60) NOT NULL,
	password VARCHAR(256) NOT NULL,
	active BOOLEAN DEFAULT FALSE,
	UNIQUE KEY login (login),
	id INT AUTO_INCREMENT PRIMARY KEY 
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8

CREATE TABLE email(
	email VARCHAR(255) NOT NULL,
	user_id INT NOT NULL,
	UNIQUE KEY email (email),
	id INT AUTO_INCREMENT PRIMARY KEY,
	FOREIGN KEY (user_id) REFERENCES account (id)
		ON DELETE CASCADE
		ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8

CREATE TABLE prefered_email(
	email_id VARCHAR(255) NOT NULL,
	user_id INT NOT NULL,
	id INT AUTO_INCREMENT PRIMARY KEY,
	FOREIGN KEY (user_id) REFERENCES account (id)
		ON DELETE CASCADE
		ON UPDATE CASCADE,
	FOREIGN KEY (email_id) REFERENCES email (id)
		ON DELETE CASCADE
		ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8

    При попытке создать prefered_email получаю ошибку: Error: Can't create table './nightfol_print/prefered_phone.frm' (errno: 150)
    В чем может быть беда?
    Максимум что нашел, это что http://www.sql.ru/forum/actualthread.aspx?tid=626099 типы id должны быть одинаковы ... но они и есть. И все таблицы InnoDB.
    Подскажите, пожалуйста.

    SilentImp, 18 Января 2010

    Комментарии (7)

  7. SQL / Говнокод #2351

    −861.7

    1. 1
    2. 2
    3. 3
    select ...
where ISNULL(e2e.ItemId, -1) = (case when @itemId < 1 then ISNULL(e2e.ItemId, -1) else @itemId end) 
and ISNULL(e2e.LeftId, -1) = (case when @parentItemId < 1 then ISNULL(e2e.LeftId, -1) else @parentItemId) end

    workgss, 29 Декабря 2009

    Комментарии (3)

  8. SQL / Говнокод #2315

    −863.1

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    6. 6
    FOR v_word IN 
        SELECT word FROM list                     
        AS word                              
    LOOP
    	UPDATE list SET pos=strpos(v_text, v_word) WHERE word=v_word;
    END LOOP;

    nomad, 23 Декабря 2009

    Комментарии (6)

  9. SQL / Говнокод #2305

    −125.1

    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
    create procedure [dbo].[pbsp_GetClientUsers]

	(
        @ClientId int,
        @fname varchar(100),
        @lname varchar(100),
        @email varchar(150)
	)

AS
	
DECLARE @SQL varchar(1000)

Set @SQL = 'Select TOP 500 *, tblRoles.title AS Role from tblUser INNER JOIN
        tbl_mtm_UserRoles ON tblUser.UserId = tbl_mtm_UserRoles.UserId INNER JOIN
        tblRoles ON tbl_mtm_UserRoles.RoleId = tblRoles.Id where tblUser.ClientId = ' + STR(@ClientId) + ' ' 

if LEN(@fname) > 0
    Set @SQL = @SQL + ' AND tblUser.fName like ''' + @fname + '%'' '
    
if LEN(@lname) > 0
    Set @SQL = @SQL + ' AND tblUser.lName like ''' + @lname + '%'' '
    
if LEN(@email) > 0
    Set @SQL = @SQL + ' AND tblUser.Email like ''' + @email + '%'' '        	
    
Set @SQL = @SQL + ' Order by tblUser.lName, tblUser.Fname'        	
exec (@SQL)

    По долгу работы приходится местами переписывать унаследованный код. Я держалась неделю, но после этого шедевра все таки зарегилась на сайте и решила поделиться перлом. Интересно, что бы делали, если бы параметров еще штук пускай даже двадцать добавить?

    Крендель, 21 Декабря 2009

    Комментарии (20)

  10. SQL / Говнокод #2296

    −876

    1. 1
    INSERT INTO `x_world` VALUES (157282,-115,204,1,206320,'Жопа Волосатая',68797,'Чере',3277,'WarSky',595);

    Омг!
    Код взят с http://www.google.com/codesearch/p?hl=ru#Vh-Pv7tO-E0/map.sql&q=%D0%B6%D0%BE%D0%BF%D0%B0&sa=N& cd=8&ct=rc

    x0wl, 18 Декабря 2009

    Комментарии (4)