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

    0

    1. 1
    delete e from  [Emails] as e where not exists (select 1 from [Emails] group by body having MAX(id)=e.Id)

    говеное удаление дублей

    techlead_seneor_228, 26 Апреля 2022

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

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

    0

    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
    42. 42
    43. 43
    44. 44
    45. 45
    46. 46
    47. 47
    48. 48
    49. 49
    50. 50
    51. 51
    52. 52
    53. 53
    54. 54
    55. 55
    56. 56
    57. 57
    58. 58
    59. 59
    60. 60
    61. 61
    62. 62
    63. 63
    64. 64
    65. 65
    66. 66
    67. 67
    68. 68
    69. 69
    70. 70
    ttt as (
                    select o.tovarid, o.edizmerid, o.cena, 
                           o.vos, o.cena*o.vos as sum_vos,  
                           coalesce(p.sum_kl,0.0)  as _prih_kl, coalesce( p.sum_prih,0.0) as  sum_prih, 
                           coalesce( p.sum_prih_docs,0.0) as  sum_prih_docs,
                           coalesce(r.sum_kl,0.0) as _rash_kl, coalesce(r.sum_rash,0.0)  as  sum_rash ,
                           coalesce(r.sum_rash_docs,0.0)  as  sum_rash_docs ,
                           coalesce(rd.sum_kl,0.0) as _rash_doc_kl, coalesce(rd.sum_rash_doc,0.0) as sum_rash_doc, 
                           coalesce(rd.sum_rash_doc_docs, 0.0) as sum_rash_doc_docs ,
                 --          coalesce(rs.sum_kl,0.0) as _rash_storno_kl, coalesce(rs.sum_rash,0.0) as sum_rash_storno ,
                           o.ostid, o.ss, o.sb 
                    from ost_ o
                    left join prih p on (o.tovarid = p.tovarid 
                                     and o.edizmerid=p.edizmerid
                                     and o.ss = p.ss
                                     and o.sb = p.sb)   
                    left join rash r on (o.tovarid =r.tovarid 
                                     and o.edizmerid=r.edizmerid
                                     and o.ss = r.ss
                                     and o.sb = r.sb) 
                    left join rash_doc rd on (o.tovarid =rd.tovarid 
                                          and o.edizmerid=rd.edizmerid
                                          and o.ss = rd.ss_
                                          and o.sb = rd.sb_) 
                   -- left join rash_old_072018 ro on (o.tovarid =ro.tovarid 
                                      --    and o.edizmerid=ro.edizmerid) 
              -- 08.2018     left join rash_storno rs on (o.tovarid =rs.tovarid    and o.edizmerid=rs.edizmerid) 
--                    where   o.vos+p.sum_kl>0  
                  ),
                tt as (    
                  select *, ttt.vos + ttt._prih_kl - ttt._rash_kl as ost_fact ,
--                    case when  (ttt.vos+ttt._prih_kl<>0 )  then
--                        ( ttt.sum_vos+ttt.sum_prih -ttt.sum_rash_storno)/(ttt.vos+ttt._prih_kl-ttt._rash_storno_kl)
--                      else ttt.cena end   as new_cen from ttt
          --          case when  (ttt.vos+ttt._prih_kl+ttt._rash_storno_kl-ttt._rash_kl-ttt._rash_doc_kl<>0 )  then
          --              ( ttt.sum_vos+ttt.sum_prih +ttt.sum_rash_storno-ttt.sum_rash-ttt.sum_rash_doc)/(ttt.vos+ttt._prih_kl-ttt._rash_storno_kl-ttt._rash_kl-ttt._rash_doc_kl)
                         case when (ttt.vos+ttt._prih_kl-ttt._rash_kl-ttt._rash_doc_kl<>0 ) then
                           ( ttt.sum_vos+ttt.sum_prih -ttt.sum_rash-ttt.sum_rash_doc)/(ttt.vos+ttt._prih_kl-ttt._rash_kl-ttt._rash_doc_kl)
                         else ttt.cena end as new_cen 
                  from ttt
                )        
      select tt.tovarid,
             tt.cena,
             tt.edizmerid,
             t.kt,
             t.nt,
             tt.vos,
             round(sum_vos,2), 
             u.name_u,
             tt._prih_kl as _prih_kl, 
      --       round( tt.sum_prih,2)  as sum_prih ,
              tt.sum_prih_docs  as sum_prih ,
             tt._rash_kl as _rash_kl, 
       --      round( tt.sum_rash,2)  as sum_rash ,
             tt.sum_rash_docs  as sum_rash ,
             tt.ost_fact,
--             round(tt.ost_fact* tt.new_cen,5),
             round(tt.ost_fact* tt.new_cen,2),
             t.priz,
             round(tt.new_cen,5),
             tt.ostid,
             tt._rash_doc_kl,
--             tt.sum_rash_doc,
             tt.sum_rash_doc_docs,
             t.dk_2010,
             coalesce( tt.ss, '0'),
             coalesce( tt.sb, '0')
          from tt
          left join material.tovar t on (t.tovarid=tt.tovarid)
          left join nsi.units u  on (u.id=tt.edizmerid);

    Называется найди разницу между ttt, tt и t

    Baiumka, 31 Января 2022

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

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

    0

    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
    42. 42
    43. 43
    44. 44
    45. 45
    46. 46
    47. 47
    48. 48
    49. 49
    50. 50
    51. 51
    52. 52
    53. 53
    54. 54
    55. 55
    56. 56
    57. 57
    58. 58
    59. 59
    60. 60
    61. 61
    62. 62
    63. 63
    64. 64
    65. 65
    66. 66
    67. 67
    68. 68
    69. 69
    70. 70
    71. 71
    72. 72
    73. 73
    74. 74
    75. 75
    76. 76
    77. 77
    78. 78
    79. 79
    80. 80
    81. 81
    82. 82
    83. 83
    84. 84
    85. 85
    86. 86
    87. 87
    88. 88
    89. 89
    90. 90
    91. 91
    92. 92
    93. 93
    94. 94
    95. 95
    96. 96
    97. 97
    98. 98
    99. 99
    return QUERY
      with a as 
     (    
         with pr as 
         (
           select p.ss, p.sb, 
             sum(sum_prih)  as sum_prih
           from   material.prihod_by_all_sklads_by_doc_period(dt_ostatki)  p               
           group by 1,2
         ),
          r AS
         (
           select p.ss, p.sb, 
                  sum(p.sum_by_cen)  as sum_rash
           from   material.rashod_by_all_sklads_by_doc_period(dt_ostatki)  p               
           group by 1,2
         ),
         os AS
         (  
            select o.ss, o.sb, 
       --     case  when trim(o.sb)='1150' then 'Запчастини ВКМ,ВПК1,ВПК2,ВПК3,сб. 1150'
      --            when trim(o.sb)='1050' then 'Запчастини ВКМ,ВПК1 сб. 1050' 
   --               when trim(o.sb)='1105' then 'Запчастини ВКМ,ВПК1,ВПК2,ВПК3,сб. 1105'
     --        else  o.name_sklad end as name_skl, 
            sum(o.sum_ost_doc)  as sum_ost_doc--,
           from material.ostatki_by_all_sklads(dt_ostatki) o
           left join material.sklad s  on (s.skladid = o.skladid) 
           group by 1,2
         ),
         topl AS
         (
        select o.ss, o.sb,
              sum(o.ost_sum)   as sum_ost_doc,
              sum(o.vos_sum)   as sum_vos,
              sum(o.prih_sum)   as sum_prih,
              sum(o.rash_sum)   as sum_rash
              from material.ostatki_by_all_toplot(dt_ostatki) o 
              group by 1,2  
         )
        select os.ss, os.sb,
               os.sum_ost_doc,  
               coalesce ( pr.sum_prih,0) as  sum_prih,
                coalesce (r.sum_rash,0) as  sum_rash 
         from os 
         left join pr on (os.ss= pr.ss and os.sb= pr.sb ) 
         left join r on (os.ss= r.ss and os.sb= r.sb )   
        UNION
        select os.ss, os.sb,
               os.sum_ost_doc,  
--                os.sum_vos + os.sum_prih - os.sum_rash  as sum_ost_doc ,
               os.sum_prih,
        --       os.sum_rash
--               case when period_dt$>='2018.08.01'  then COALESCE( r.sum_rash,0) 
--                else  os.sum_vos-os.sum_ost_doc + os.sum_prih end    as sum_rash --os.sum_rash END 
  --             r.sum_rash  
          os.sum_vos-os.sum_ost_doc + os.sum_prih
         
        from topl os left join r on (os.ss= r.ss and os.sb= r.sb )   
/*      
        select o.ss, o.sb,
              sum(o.ost_sum)   as sum_ost_doc,
              sum(o.prih_sum)   as sum_prih,
              sum(o.rash_sum)   as sum_rash
              from material.ostatki_by_all_toplot(dt_ostatki) o  left JOIN r on (r.ss =o.ss and r.sb=o.sb)
              group by 1,2  
  */            
   /*    UNION
       select  ob.ss, ob.sus,
               '112'  as    depart_name,
               '112',
               sum(ob.ost_sum),  
               sum(ob.pr_sum),
               sum(ob.rm_sum)    
       
             from  material.oborot_by_ss_112_by_sklads(period_dt, dt_ostatki)   ob           
             group by 1,2,3,4  */
     ),
     b as
     (
      select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'112')  bb where bb.sb = '0007'
      UNION
      select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'153')  bb
      UNION
  --      select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'152')  bb  
      select bb.ss,bb.sb,bb.sisb_d ,bb.sisb_k, bb.sdob, 0 from cont.bbz_sald_by_sb(y,m,'152')  bb
      UNION
      select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'201')  bb
      UNION
      select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'207')  bb 
      UNION
      select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'209')  bb 
      UNION
      select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'203')  bb 
      UNION
      select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'221')  bb 
     -- UNION
     -- select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'231')  bb 
     -- UNION
     -- select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'232')  bb

    Весь запрос не влез...

    Baiumka, 10 Января 2022

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

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

    +2

    1. 1
    select sum(sma) as ism from Jur_7.ss2022_1 where ch=1 and gd*100+ms<2022*100+1

    Нашел БД с ОТДЕЛЬНОЙ таблицей на каждый месяц (начиная с 2008 года). А тут еще и дата определяется....

    Baiumka, 06 Января 2022

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

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

    0

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    SELECT *
FROM TRANSACTION
WHERE status = 'in-check'
  AND created >= '2021-11-17 12:40'
  AND created <= '2021-11-17 01:10';

    Когда очень полюбили 12-часовой формат

    alex_ok, 19 Ноября 2021

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

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

    +3

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    6. 6
    7. 7
    INSERT INTO Receipt VALUES ('2', '01.06.2020', 
(SELECT ProdPrice FROM Products WHERE ProdID=2), 
(((SELECT ProdPrice FROM Products WHERE ProdID=2)*20)/100), 
(SELECT ProdPrice FROM Products WHERE ProdID=2)+(((SELECT ProdPrice FROM Products WHERE ProdID=2)*20)/100), '1');


ого

    Кровь из глаз. Четыре подзапроса вместо одного, да ещё и имена колонок для вставки не указаны.
    Я уже молчу про то, как считается НДС.

    cpubaker, 20 Сентября 2021

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

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

    0

    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
    42. 42
    43. 43
    44. 44
    45. 45
    46. 46
    47. 47
    48. 48
    49. 49
    50. 50
    51. 51
    52. 52
    53. 53
    54. 54
    SELECT sum(t4.value) as "План", STR_TO_DATE(t4.date, '%d-%m-%Y') AS "time" FROM
(SELECT  t.id, t.subject, t3.depth, t.value, 
   CASE WHEN t.field_name = "Август 2020 (план)" THEN "2020-08-01"
  WHEN t.field_name = "Август 2020 (факт)" THEN "2020-08-01"
  WHEN t.field_name = "Апрель 2020 (план)" THEN "2020-04-01"
  WHEN t.field_name = "Апрель 2020 (факт)" THEN "2020-04-01"
  WHEN t.field_name = "Декабрь 2020 (план)" THEN "2020-12-01"
  WHEN t.field_name = "Декабрь 2020 (факт)" THEN "2020-12-01"
  WHEN t.field_name = "Июль 2020 (план)" THEN "2020-07-01"
  WHEN t.field_name = "Июль 2020 (факт)" THEN "2020-07-01"
  WHEN t.field_name = "Июнь 2020 (план)" THEN "2020-06-01"
  WHEN t.field_name = "Июнь 2020 (факт)" THEN "2020-06-01"
  WHEN t.field_name = "Май 2020 (план)" THEN "2020-05-01"
  WHEN t.field_name = "Май 2020 (факт)" THEN "2020-05-01"
  WHEN t.field_name = "Март 2020 (план)" THEN "2020-03-01"
  WHEN t.field_name = "Март 2020 (факт)" THEN "2020-03-01"
  WHEN t.field_name = "Ноябрь 2020 (план)" THEN "2020-11-01"
  WHEN t.field_name = "Ноябрь 2020 (факт)" THEN "2020-11-01"
  WHEN t.field_name = "Октябрь 2020 (план)" THEN "2020-10-01"
  WHEN t.field_name = "Октябрь 2020 (факт)" THEN "2020-10-01"
  WHEN t.field_name = "Сентябрь 2020 (план)" THEN "2020-09-01"
  WHEN t.field_name = "Сентябрь 2020 (факт)" THEN "2020-09-01"
  WHEN t.field_name = "Февраль 2020 (план)" THEN "2020-02-01"
  WHEN t.field_name = "Февраль 2020 (факт)" THEN "2020-02-01"
  WHEN t.field_name = "Январь 2020 (план)" THEN "2020-01-01"
  WHEN t.field_name = "Январь 2020 (факт)" THEN "2020-01-01" end AS DATE
   FROM (
          SELECT i.id AS id, i.subject AS subject, i.updated_on as updated_on,
            cf.name AS field_name,
            cv.value AS value
          FROM issues i
        LEFT JOIN custom_values cv
            ON i.id = cv.customized_id
        LEFT JOIN custom_fields cf
            ON cv.custom_field_id=cf.id      
         WHERE cv.customized_type="Issue" and (i.project_id = 2284)) t 
   LEFT join
   (SELECT  t2.id,
  GROUP_CONCAT(DISTINCT(CASE WHEN t2.field_name = "Код бюджета" THEN t2.value else null END)) AS depth   
   FROM (
          SELECT i.id AS id, i.subject AS subject, i.updated_on as updated_on,
            cf.name AS field_name,
            cv.value AS value
          FROM issues i
        LEFT JOIN custom_values cv
            ON i.id = cv.customized_id
        LEFT JOIN custom_fields cf
            ON cv.custom_field_id=cf.id      
         WHERE cv.customized_type="Issue" and (i.project_id = 2284)) t2
   GROUP BY t2.id) t3        
   ON t.id=t3.id
   WHERE INSTR(t.field_name, "план")>0 ) t4
   WHERE substr(t4.date,1,7) in ($time)  and t4.value!=0 and t4.depth=1 and t4.subject = 'Себестоимость реализованной готовой продукции (товаров, работ, услуг)'
   group by t4.date

    https://t.me/dba_ru/131122

    Fike, 19 Февраля 2021

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

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

    −1

    1. 1
    2. 2
    3. 3
    Falcon Space - это платформа для создания веб-решений с управлением через SQL. 
Все создается и управляется на SQL. 
Телеграм бот управляется полностью 1 хранимой процедурой на SQL!

    https://falcon.web-automation.ru/docs/telegram-boty-i-otpravka-soobshcheniy-v-telegram

    Fike, 06 Февраля 2021

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

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

    0

    1. 1
    2. 2
    3. 3
    if @OWNER_PHONE <> @OLD_PHONE set @PHONE = @OWNER_PHONE
      if @PHONE <> @OLD_PHONE set @OWNER_PHONE = @PHONE
      set @PHONE = @OWNER_PHONE

    tucvbif, 05 Февраля 2021

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

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

    +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
    declare @NL table
 (ARTICLE_ID int primary key,
  M int,
  DS datetime,
  DD datetime,
  RAS money,
  REST money,
  NWS money,
  NWA money,
  LD_AMOUNT money,
  LD_DATE datetime,
  IS_NL bit,
  SCC_ID int,
  IS_NOT_MARKDOWN bit)

    Double Side,Single Density / Double Side, Double Density — это понятно. Но почему datetime?

    tucvbif, 09 Декабря 2020

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