- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 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
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 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