1 2 3 4 5 6 7 8 9 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 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101
| SELECT
devicestatus.dev_id
, devicestatus.dev_name
, devicestatus.dev_group_id
, groups.grp_group_name
, DATE(NOW()) as 'Date_AD'
, D10.V_CONSO_meter_logdate AS 'Date_PD'
, D11.V_CONSO_meter_logdate AS 'Date_PD2'
, D0.V_CONSO_meter_value AS 'Index_AD'
, D10.V_CONSO_meter_value AS 'Index_PD'
, D11.Index_PD AS 'Index_PD2'
, COALESCE (D0.V_CONSO_meter_value - D1.Index_PD,0) AS 'Conso_AD'
, COALESCE (D10.V_CONSO_meter_value - D11.Index_PD,0) AS 'Conso_PD'
, date_format(DATE(NOW()), '%Y-%m') as 'Date_AM'
, M0.V_CONSO_meter_logdate AS 'Date_PM'
, M0.V_CONSO_meter_value AS 'Index_AM'
, M1.Index_PM AS 'Index_PM'
, COALESCE (M0.V_CONSO_meter_value - M1.Index_PM,0) AS 'Conso_AM'
, year(DATE(NOW())) as 'Date_AY'
, Y0.V_CONSO_meter_logdate AS 'Date_PY'
, Y0.V_CONSO_meter_value AS 'Index_AY'
, Y1.Index_PY AS 'Index_PY'
, COALESCE (Y0.V_CONSO_meter_value - Y1.Index_PY,0) AS 'Conso_AY'
, devicestatus.dev_nvalue
, devicestatus.dev_last_update
, devicestatus.dev_signal_level
, devicestatus.dev_battery_level
FROM `devicestatus`
LEFT JOIN groups
ON groups.grp_id = devicestatus.dev_group_id
LEFT JOIN v_conso_day D0
ON D0.V_CONSO_meter_devlog_id = devicestatus.dev_id
AND date(D0.V_CONSO_meter_logdate) = DATE( NOW()) OR date(D0.V_CONSO_meter_logdate) IS NULL
LEFT JOIN
(SELECT V_CONSO_meter_devlog_id
, V_CONSO_meter_logdate
, V_CONSO_meter_value as Index_PD
FROM v_conso_day
) D1
ON D1.V_CONSO_meter_devlog_id = D0.V_CONSO_meter_devlog_id
AND D1.V_CONSO_meter_logdate =
(SELECT max(D2.V_CONSO_meter_logdate)
FROM v_conso_day D2
WHERE D2.V_CONSO_meter_devlog_id = D0.V_CONSO_meter_devlog_id
AND D2.V_CONSO_meter_logdate < D0.V_CONSO_meter_logdate
)
LEFT JOIN v_conso_day D10
ON D10.V_CONSO_meter_devlog_id = devicestatus.dev_id
AND date(D10.V_CONSO_meter_logdate) = DATE(DATE_ADD(NOW(), INTERVAL -1 DAY)) OR date(D10.V_CONSO_meter_logdate) IS NULL
LEFT JOIN
(SELECT V_CONSO_meter_devlog_id
, V_CONSO_meter_logdate
, V_CONSO_meter_value as Index_PD
FROM v_conso_day
) D11
ON D11.V_CONSO_meter_devlog_id = D10.V_CONSO_meter_devlog_id
AND D11.V_CONSO_meter_logdate =
(SELECT max(D12.V_CONSO_meter_logdate)
FROM v_conso_day D12
WHERE D12.V_CONSO_meter_devlog_id = D10.V_CONSO_meter_devlog_id
AND D12.V_CONSO_meter_logdate < D10.V_CONSO_meter_logdate
)
LEFT JOIN
v_conso_month M0
ON M0.V_CONSO_meter_devlog_id = devicestatus.dev_id
AND M0.V_CONSO_meter_logdate = date_format(NOW(), '%Y-%m') OR M0.V_CONSO_meter_logdate IS NULL
LEFT JOIN
(SELECT
V_CONSO_meter_devlog_id,
V_CONSO_meter_logdate,
V_CONSO_meter_value as Index_PM
FROM v_conso_month
) M1
ON M1.V_CONSO_meter_devlog_id = M0.V_CONSO_meter_devlog_id
AND M1.V_CONSO_meter_logdate =
(SELECT max(M2.V_CONSO_meter_logdate)
FROM v_conso_month M2
WHERE M2.V_CONSO_meter_devlog_id = M0.V_CONSO_meter_devlog_id
AND M2.V_CONSO_meter_logdate < M0.V_CONSO_meter_logdate
)
LEFT JOIN
v_conso_year Y0
ON Y0.V_CONSO_meter_devlog_id = devicestatus.dev_id
AND Y0.V_CONSO_meter_logdate = year(NOW()) OR Y0.V_CONSO_meter_logdate IS NULL
LEFT JOIN
(SELECT
V_CONSO_meter_devlog_id,
V_CONSO_meter_logdate,
V_CONSO_meter_value as Index_PY
FROM v_conso_year
) Y1
ON Y1.V_CONSO_meter_devlog_id = Y0.V_CONSO_meter_devlog_id
AND Y1.V_CONSO_meter_logdate =
(SELECT max(Y2.V_CONSO_meter_logdate)
FROM v_conso_year Y2
WHERE Y2.V_CONSO_meter_devlog_id = Y0.V_CONSO_meter_devlog_id
AND Y2.V_CONSO_meter_logdate < Y0.V_CONSO_meter_logdate
)
WHERE
devicestatus.dev_used = 1
ORDER BY devicestatus.dev_name, groups.grp_group_name |
Partager