SELECT
md_readings.dt as dt,
min(md_readings.number_of_inputs) as number_of_input,
max(md_readings.number_of_inputs) as max_number_of_input,
,
SUM(COALESCE( md_readings.consumption, md_readings.indication, 0 )) as sum
,
SUM(
CASE
WHEN voc_md_volumes.type = 'M'
AND md_readings.target ILIKE '%подающий%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) AS m1
, SUM(
CASE
WHEN voc_md_volumes.type = 'M'
AND md_readings.target ILIKE '%обратный%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) AS m2
,
SUM(
CASE
WHEN voc_md_volumes.type = 'M'
AND md_readings.target ILIKE '%подающий%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) - SUM(
CASE
WHEN voc_md_volumes.type = 'M'
AND md_readings.target ILIKE '%обратный%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) AS m1_m2
,
SUM(
CASE
WHEN voc_md_volumes.type = 'V'
AND md_readings.target ILIKE '%подающий%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) AS v1
, SUM(
CASE
WHEN voc_md_volumes.type = 'V'
AND md_readings.target ILIKE '%обратный%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) AS v2
,
SUM(
CASE
WHEN voc_md_volumes.type = 'V'
AND md_readings.target ILIKE '%подающий%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) - SUM(
CASE
WHEN voc_md_volumes.type = 'V'
AND md_readings.target ILIKE '%обратный%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) AS v1_v2
,
AVG(
CASE
WHEN voc_md_volumes.type = 'PA'
AND md_readings.target ILIKE '%подающий%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) AS pa1
, AVG(
CASE
WHEN voc_md_volumes.type = 'PA'
AND md_readings.target ILIKE '%обратный%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) AS pa2
, AVG(
CASE
WHEN voc_md_volumes.type = 'PA'
AND md_readings.target ILIKE '%подающий%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) - AVG(
CASE
WHEN voc_md_volumes.type = 'PA'
AND md_readings.target ILIKE '%обратный%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) AS pa1_pa2
,
AVG(
CASE
WHEN voc_md_volumes.type = 'T'
AND md_readings.target ILIKE '%подающий%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) AS t1
, AVG(
CASE
WHEN voc_md_volumes.type = 'T'
AND md_readings.target ILIKE '%обратный%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) AS t2
, AVG(
CASE
WHEN voc_md_volumes.type = 'THV'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) AS thv
, AVG(
CASE
WHEN voc_md_volumes.type = 'T'
AND md_readings.target ILIKE '%подающий%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) - AVG(
CASE
WHEN voc_md_volumes.type = 'T'
AND md_readings.target ILIKE '%обратный%'
THEN COALESCE( md_readings.consumption, md_readings.indication, 0 )
END
) AS t1_t2
,
SUM(md_readings.consumption) as sum
FROM
md_readings
INNER JOIN md_com_resources ON (
md_com_resources.id = md_readings.id_md_com_resource)
LEFT JOIN voc_md_resources ON (
md_com_resources.id_voc_com_resource = voc_md_resources.id_voc_com_resource
AND
voc_md_resources.fake = 0)
LEFT JOIN voc_md_volumes ON (
voc_md_volumes.id = md_readings.id_voc_md_volume
AND
voc_md_volumes.fake = 0)
WHERE
md_readings.fake = 0
AND
md_readings.id_metering_device = ?
AND
md_com_resources.id_voc_com_resource = ?
AND
md_readings.dt IS NOT NULL
AND (
voc_md_volumes.type = 'W'
OR
voc_md_volumes.type = 'M'
OR
voc_md_volumes.type = 'M'
OR
voc_md_volumes.type = 'V'
OR
voc_md_volumes.type = 'V'
OR
voc_md_volumes.type = 'PA'
OR
(voc_md_volumes.type = 'T' OR voc_md_volumes.type = 'THV')
)
GROUP BY
md_readings.dt
LIMIT
1
",
8767,
10