1. 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

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

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