27 lines
699 B
MySQL
27 lines
699 B
MySQL
|
select
|
||
|
|
b.organ_code
|
||
|
|
, b.organ_name
|
||
|
|
, b.comm_id
|
||
|
|
, b.comm_name
|
||
|
|
, a.MeterType
|
||
|
|
, date_format(ListDate,'%Y%m') list_ym
|
||
|
|
, sum(case when Amount != 0 and Dosage = 0 then Amount / Price else Dosage end) Dosage
|
||
|
|
, sum(Amount) Amount
|
||
|
|
from dwd_engine_meter_reading_record_d a
|
||
|
|
left join (
|
||
|
|
select distinct
|
||
|
|
organ_code
|
||
|
|
, organ_name
|
||
|
|
, comm_id
|
||
|
|
, comm_name
|
||
|
|
from dim_organ_mapping
|
||
|
|
) b
|
||
|
|
on a.CommID = b.comm_id
|
||
|
|
where b.comm_id is not null
|
||
|
|
group by
|
||
|
|
b.organ_code
|
||
|
|
, b.organ_name
|
||
|
|
, b.comm_id
|
||
|
|
, b.comm_name
|
||
|
|
, a.MeterType
|
||
|
|
, date_format(ListDate,'%Y%m')
|