Hi All,
I am working on a report, which would give the Average of the bill( If I Selected Yearly it should show Yearly average (total 12 months bill/12 ) and If I selected Jan to June then it should show the average of 6 months).
the below one is working when I selected the YEAR, but how to achieve the same for selected months.
select * from (SELECT BILL_PERIOD,YR,MON,SITE, BUILDING,ROOM,METER_E,CONSUM_E,INITIAL_COST_E,PRESNT_E,PREVIS_E, KAHRAMA_BILL_E,METER_W,CONSUMP_W,INITIAL_COST_W,PRESENT_W,PREVIS_W,KAHRAMA_BILL_W,
COST_KAHR_E_W,ROUND(AVG(COST_KAHR_E_W) OVER (PARTITION BY SITE,BUILDING,YR)) AS AVG_OF_BLDG_BY_YER FROM
(SELECT TO_CHAR(A.BILL_PERIOD, 'YYYY - MON') AS BILL_PERIOD, TO_CHAR(A.BILL_PERIOD, 'YYYY') AS YR, TO_CHAR(A.BILL_PERIOD, 'mm') AS MON,
TO_CHAR(A.BILL_PERIOD, 'MM/DD/YYYY') mon_filter,
A.SITE, A.BUILDING,A.ROOM,
MAX(CASE WHEN A.SYSTEM_METER_SITE_NO = 'ELECTRICITY' THEN A.METER_SERIAL_NO ELSE NULL END) AS METER_E,
MAX(CASE WHEN A.SYSTEM_METER_SITE_NO = 'ELECTRICITY' THEN A.INITIAL_CONSUMPTION ELSE NULL END) AS CONSUM_E,
MAX(CASE WHEN A.SYSTEM_METER_SITE_NO = 'ELECTRICITY' THEN A.INITIAL_COST ELSE NULL END) AS INITIAL_COST_E,
MAX(CASE WHEN A.SYSTEM_METER_SITE_NO = 'ELECTRICITY' THEN A.METER_READING ELSE NULL END) AS PRESNT_E,
MAX(CASE WHEN A.SYSTEM_METER_SITE_NO = 'ELECTRICITY' THEN A.METER_READING-INITIAL_CONSUMPTION ELSE NULL END) AS PREVIS_E,
MAX(CASE WHEN A.SYSTEM_METER_SITE_NO = 'ELECTRICITY' THEN A.INVOICE_COST ELSE NULL END) AS KAHRAMA_BILL_E,
MAX(CASE WHEN A.SYSTEM_METER_SITE_NO = 'WATER' THEN A.METER_SERIAL_NO ELSE NULL END) AS METER_W,
MAX(CASE WHEN A.SYSTEM_METER_SITE_NO = 'WATER' THEN A.INITIAL_CONSUMPTION ELSE NULL END) AS CONSUMP_W,
MAX(CASE WHEN A.SYSTEM_METER_SITE_NO = 'WATER' THEN A.INITIAL_COST ELSE NULL END) AS INITIAL_COST_W,
MAX(CASE WHEN A.SYSTEM_METER_SITE_NO = 'WATER' THEN A.METER_READING ELSE NULL END) AS PRESENT_W,
MAX(CASE WHEN A.SYSTEM_METER_SITE_NO = 'WATER' THEN A.METER_READING-INITIAL_CONSUMPTION ELSE NULL END) AS PREVIS_W,
MAX(CASE WHEN A.SYSTEM_METER_SITE_NO = 'WATER' THEN A.INVOICE_COST ELSE NULL END) AS KAHRAMA_BILL_W,
(SUM(A.INVOICE_COST +(SELECT PARAM_NUMBER FROM FAMIS_PARAM WHERE PARAM_TYPE = 'METER RENT')) ) AS COST_KAHR_E_W
FROM FM_CONSUMPTION A
WHERE A.SYSTEM_METER_SITE_NO <> 'TELECOM'
GROUP BY TO_CHAR(A.BILL_PERIOD, 'YYYY - MON'), TO_CHAR(A.BILL_PERIOD, 'YYYY'),TO_CHAR(A.BILL_PERIOD, 'mm'),TO_CHAR(A.BILL_PERIOD, 'MM/DD/YYYY') , A.SITE, A.BUILDING,A.ROOM
ORDER BY TO_CHAR(A.BILL_PERIOD, 'YYYY - MON'),TO_CHAR(A.BILL_PERIOD, 'mm') , A.SITE, A.BUILDING, A.ROOM ))where (BILL_PERIOD in ('2016 - JUL','2016 - AUG','2016 - SEP','2016 - OCT','2016 - NOV') AND site in ('RG1') AND BUILDING in ('107'))
Thanks