/*latest 9/25/2021*/



/*THIS WORKS - - - -SELECT * FROM meter
WHERE (date_timestamp, serial_number) IN(SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number
                          FROM meter mtr 
                          INNER JOIN Machine_Archive cd ON cd.SerialNumber = mtr.serial_number
                          WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 23:59:58'
                          AND cd.org_id_ma = 59 AND cd.school_year = 21
                          GROUP BY mtr.serial_number) 
ORDER BY serial_number;*/


#select * from meter where date_timestamp = '2020-08-02 04:23:52' order by serial_number;

/*SELECT MAX(mtr.date_timestamp) dt,
                    mtr.serial_number,
                    mtr.black_meter
                    #(CASE WHEN mtr.black_meter IS NULL THEN 0 ELSE mtr.black_meter END) meter_read
                    #cd.building_id_ma
                    
                    FROM meter mtr
                    #INNER JOIN Machine_Archive cd ON cd.SerialNumber = mtr.serial_number
                    WHERE mtr.date_timestamp > '2020-07-01 00:00:00' AND mtr.date_timestamp < '2020-08-02 04:23:58'
                    AND serial_number IN ('AA7P011000450', 'ACET011000635')
                    #AND cd.school_year = 21
                    #AND org_id_ma = 59
                    GROUP BY mtr.serial_number, mtr.black_meter;
                    #ORDER BY cd.building_id_ma;*/
                    
/*SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number
                          FROM meter mtr 
                          INNER JOIN Machine_Archive cd ON cd.SerialNumber = mtr.serial_number
                          WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 04:23:58'
                          AND cd.org_id_ma = 59 AND cd.school_year = 21
                          GROUP BY mtr.serial_number; */                 

#SELECT * FROM Machine_Archive WHERE `SerialNumber` = 'ACET011000403' AND school_year = 21;

SELECT * FROM Machine_Archive ma
INNER JOIN
(SELECT * FROM meter
WHERE (date_timestamp, serial_number) IN(SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number
                          FROM meter mtr 
                          INNER JOIN Machine_Archive cd ON cd.SerialNumber = mtr.serial_number
                          WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 23:59:58'
                          AND cd.org_id_ma = 59 AND cd.school_year = 21
                          GROUP BY mtr.serial_number)) ser_dt ON ser_dt.serial_number = ma.`SerialNumber`
ORDER BY ser_dt.serial_number;



SELECT bldgs.building_name,
		bldgs.id,
        (CASE WHEN ser_dt.meter_read IS NULL THEN 0 - ma.`Begin` ELSE ser_dt.meter_read - ma.`Begin` END) vol_pre_upgrade,
        ma.tot_yr_vols - (CASE WHEN ser_dt.meter_read Is NULL THEN 0 - ma.`Begin` ELSE ser_dt.meter_read - ma.`Begin` END) vol_post_upgrade,        
        ma.tot_yr_vols tot_vols,
        (CASE WHEN ser_dt.meter_read IS NULL THEN 0 ELSE ser_dt.meter_read * `CostCopy` END) cost_pre_upgrade,
        
        (ma.tot_yr_vols - CASE WHEN ser_dt.meter_read Is NULL THEN 0 ELSE ser_dt.meter_read END) * `CostCopy` cost_post_upgrade,
        ma.tot_yr_vols * `CostCopy` tot_costs
        
        FROM buildings bldgs
        
        RIGHT JOIN (SELECT `SerialNumber`, building_id_ma, `CostCopy`, `Begin`, SUM(`Begin`) ma_begin,
                    SUM(`End` - `Begin`) tot_yr_vols, org_id_ma
                    FROM Machine_Archive
        			WHERE school_year = 21 AND org_id_ma = 59
        			GROUP BY building_id_ma) ma ON ma.building_id_ma = bldgs.id 
        
       INNER JOIN (SELECT * FROM meter
			WHERE (date_timestamp, serial_number) IN(SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number
            FROM meter mtr 
            INNER JOIN Machine_Archive cd ON cd.SerialNumber = mtr.serial_number
            WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 23:59:58'
            AND cd.org_id_ma = 59 AND cd.school_year = 21
            GROUP BY mtr.serial_number)) ser_dt ON ser_dt.serial_number = ma.`SerialNumber`
        
        GROUP BY bldgs.id
        ORDER BY bldgs.building_name;











/*SELECT bldgs.building_name,
		bldgs.id,
        (CASE WHEN ser_dt.meter_read IS NULL THEN 0 - ma.`Begin` ELSE ser_dt.meter_read - ma.`Begin` END) vol_pre_upgrade,
        ma.tot_yr_vols - (CASE WHEN ser_dt.meter_read Is NULL THEN 0 - ma.`Begin` ELSE ser_dt.meter_read - ma.`Begin` END) vol_post_upgrade,        
        ma.tot_yr_vols tot_vols,
        (CASE WHEN ser_dt.meter_read IS NULL THEN 0 ELSE ser_dt.meter_read * `CostCopy` END) cost_pre_upgrade,
        
        (ma.tot_yr_vols - CASE WHEN ser_dt.meter_read Is NULL THEN 0 ELSE ser_dt.meter_read END) * `CostCopy` cost_post_upgrade,
        ma.tot_yr_vols * `CostCopy` tot_costs
        
        FROM buildings bldgs
        
        RIGHT JOIN (SELECT `SerialNumber`, building_id_ma, `CostCopy`, `Begin`, SUM(`Begin`) ma_begin, SUM(`End` - `Begin`) tot_yr_vols, org_id_ma
                    FROM Machine_Archive
        			WHERE school_year = 21 AND org_id_ma = 59
        			GROUP BY building_id_ma) ma ON ma.building_id_ma = bldgs.id 
        
        LEFT JOIN (SELECT MAX(mtr.date_timestamp) dt,
                    mtr.serial_number,
                    (CASE WHEN mtr.black_meter IS NULL THEN 0 ELSE mtr.black_meter END) meter_read,
                    cd.building_id_ma
                    FROM meter mtr
                    INNER JOIN Machine_Archive cd ON cd.SerialNumber = mtr.serial_number# AND org_id_ma = 59
                    WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 23:59:59'
                    AND cd.school_year = 21
                    AND org_id_ma = 59
                    GROUP BY mtr.serial_number) ser_dt ON ser_dt.building_id_ma = ma.building_id_ma
        
        GROUP BY bldgs.id
        ORDER BY bldgs.building_name;*/
/********best***********/
SELECT bldgs.building_name,    
	SUM(CASE WHEN ser_dt.meter_read IS NULL THEN 0 - ma.`Begin` ELSE ser_dt.meter_read - ma.`Begin` END) vol_pre_upgrade
    0 vol_post_upgrade,
    SUM(`End` - `Begin`) tot_vols,
	0 cost_pre_upgrade,
	0 cost_post_upgrade,
	SUM((`End` - `Begin`) * `CostCopy`) tot_costs
    
    
FROM Machine_Archive ma

LEFT JOIN (SELECT SUM(black_meter) meter_read, serial_number
                   	FROM meter
					WHERE (date_timestamp, serial_number)
                   	IN(SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number
                      FROM meter mtr 
                      INNER JOIN Machine_Archive cd ON cd.SerialNumber = mtr.serial_number
                      WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 23:59:58'
                      AND cd.org_id_ma = 59 AND cd.school_year = 21
                      GROUP BY mtr.serial_number)
GROUP BY serial_number ) ser_dt ON ser_dt.serial_number = ma.`SerialNumber`
 
INNER JOIN buildings bldgs ON bldgs.id = ma.building_id_ma AND bldgs.org_id = ma.org_id_ma
                    
                    
WHERE ma.school_year = 21 AND ma.org_id_ma = 59
GROUP BY bldgs.building_name
order by bldgs.building_name;   

/*SELECT building_id_ma, `SerialNumber` FROM Machine_Archive WHERE school_year = 21 AND org_id_ma = 59
ORDER BY building_id_ma, `SerialNumber`;*/

/*select * from meter where serial_number IN(select SerialNumber from Machine_Archive WHERE org_id_ma = 59 AND school_year = 21)
AND date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2021-08-03 23:59:59'
ORDER BY date_timestamp, serial_number;*/

#select * from meter where serial_number IN ('AA7P011000450', 'ACET011000635') order by serial_number, date_timestamp desc;


/******************BEGIN Pivot table query - Returns correct building values, but what about the cpc cost figures?*********************/
SELECT allRows.building_name,
		SUM(CASE WHEN pre_year = 'pre' THEN tot ELSE NULL END) vol_pre_upgrade,
        SUM(CASE WHEN pre_year = 'year' THEN tot ELSE NULL END) - SUM(CASE WHEN pre_year = 'pre' THEN tot ELSE NULL END) vol_post_upgrade,
        SUM(CASE WHEN pre_year = 'pre' THEN cost ELSE NULL END) cost_pre_upgrade,
        SUM(CASE WHEN pre_year = 'year' THEN cost ELSE NULL END) tot_costs,
        SUM(CASE WHEN pre_year = 'year' THEN cost ELSE NULL END) - SUM(CASE WHEN pre_year = 'pre' THEN cost ELSE NULL END) cost_post_upgrade,
		SUM(CASE WHEN pre_year = 'year' THEN tot ELSE NULL END) yearly_total
FROM (

SELECT 'pre' pre_year, bldg.id bldg_id, bldg.building_name, black_meter tot,  (black_meter * ma.`CostCopy`) cost, serial_number
                   	FROM meter
    				INNER JOIN Machine_Archive ma ON ma.SerialNumber = meter.serial_number AND school_year = 21 AND org_id_ma = 75
    				INNER JOIN buildings bldg ON bldg.id = ma.building_id_ma AND bldg.org_id = ma.org_id_ma
					WHERE (date_timestamp, serial_number)
                   	IN(SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number
                      FROM meter mtr 
                      INNER JOIN Machine_Archive cd ON cd.SerialNumber = mtr.serial_number
                      WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-07-01 23:59:58'
                      AND cd.org_id_ma = 75 AND cd.school_year = 21
                      GROUP BY serial_number)
    				
UNION ALL

SELECT 'year' pre_year, ma.building_id_ma, bldg.building_name,	
    SUM(ma.`End` - ma.`Begin`) tot,
    SUM((ma.`End` - ma.`Begin`) * `CostCopy`) cost,
    ma.SerialNumber
    
    
FROM `Machine_Archive` ma
INNER JOIN buildings bldg ON bldg.id = ma.building_id_ma AND bldg.org_id = ma.org_id_ma
WHERE ma.school_year = 21 AND ma.org_id_ma = 75
GROUP BY ma.SerialNumber, ma.building_id_ma, bldg.building_name) allRows
GROUP BY allRows.building_name;


/*FINAL QUERY WHICH GETS VALUES FROM Machine_Archive and Machine_Archive_Upgrades*/

SELECT allRows.building_name,
		SUM(CASE WHEN pre_year = 'pre' THEN tot ELSE NULL END) vol_pre_upgrade,         
        SUM(CASE WHEN pre_year = 'post' THEN tot ELSE NULL END) vol_post_upgrade,        
        SUM(CASE WHEN pre_year = 'pre' THEN tot ELSE NULL END) + SUM(CASE WHEN pre_year = 'post' THEN tot ELSE NULL END) yearly_total,
        
        SUM(CASE WHEN pre_year = 'pre' THEN cost ELSE NULL END) cost_pre_upgrade,
        SUM(CASE WHEN pre_year = 'post' THEN cost ELSE NULL END) - SUM(CASE WHEN pre_year = 'pre' THEN cost ELSE NULL END) cost_post_upgrade,
        SUM(CASE WHEN pre_year = 'post' THEN cost ELSE NULL END) yearly_costs
		
FROM (


                      
SELECT 'pre' pre_year, mau.building_id_ma, bldg.building_name,	
    SUM(mau.`End` - mau.`Begin`) tot,
    SUM((mau.`End` - mau.`Begin`) * `CostCopy`) cost,
    mau.SerialNumber
    
    
FROM `Machine_Archive_Upgrades` mau
INNER JOIN buildings bldg ON bldg.id = mau.building_id_ma AND bldg.org_id = mau.org_id_ma
WHERE mau.school_year = 21 AND mau.org_id_ma = 1
GROUP BY mau.SerialNumber, mau.building_id_ma, bldg.building_name

    				
UNION ALL

SELECT 'post' pre_year, ma.building_id_ma, bldg.building_name,	
    SUM(ma.`End` - ma.`Begin`) tot,
    SUM((ma.`End` - ma.`Begin`) * `CostCopy`) cost,
    ma.SerialNumber
    
    
FROM `Machine_Archive` ma
INNER JOIN buildings bldg ON bldg.id = ma.building_id_ma AND bldg.org_id = ma.org_id_ma
WHERE ma.school_year = 21 AND ma.org_id_ma = 1
GROUP BY ma.SerialNumber, ma.building_id_ma, bldg.building_name) allRows
GROUP BY allRows.building_name;   

















SELECT bldg_id,
		SUM(CASE WHEN pre_year = 'pre' THEN tot ELSE NULL END) pre_upgrade,
		SUM(CASE WHEN pre_year = 'year' THEN tot ELSE NULL END) yearly_total
FROM (

SELECT 'pre' pre_year, bldg.id bldg_id, black_meter tot, serial_number
                   	FROM meter
    				INNER JOIN Machine_Archive ma ON ma.SerialNumber = meter.serial_number AND school_year = 21 AND org_id_ma = 75
    				INNER JOIN buildings bldg ON bldg.id = ma.building_id_ma AND bldg.org_id = ma.org_id_ma
					WHERE (date_timestamp, serial_number)
                   	IN(SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number
                      FROM meter mtr 
                      INNER JOIN Machine_Archive cd ON cd.SerialNumber = mtr.serial_number
                      WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-07-01 23:59:58'
                      AND cd.org_id_ma = 75 AND cd.school_year = 21
                      GROUP BY serial_number)
    				
UNION ALL

SELECT 'year' pre_year, ma.building_id_ma bldg_id,	
    SUM(ma.`End` - ma.`Begin`) tot, 
    ma.SerialNumber
    
    
FROM `Machine_Archive` ma 
WHERE ma.school_year = 21 AND ma.org_id_ma = 75
GROUP BY ma.building_id_ma) allRows
GROUP BY bldg_id;
/******************END Pivot table query*********************/




RSU 20 org_id 75 
416					blk	col
('AA7N011000048',	2020-07-01 08:10:09		274	159
'AA7N011000063',	2020-07-01 08:10:09		135	46
'AA7N011000273',	2020-07-01 08:10:09		118	18
'AAFJ011002984',	2020-07-16 08:11:36
'AAFJ011003011',	2021-03-02 08:10:11
'AAFJ011003015',	2020-07-16 08:11:36
'AAFJ011003019',	2020-07-16 08:11:36
'AAFJ011003045',	2020-07-20 08:10:36
'AAFJ011003047',	2021-03-02 08:10:11
'AAFJ011003052')	2020-07-01 00:00:00		0	0

418
('AA6R011007090',
'AA7N011000050',
'AA7N011000278',
'AA7R011004231',
'AAFJ011002994',
'AAFJ011003016',
'AAFJ011003027',
'AAFJ011003037',
'CNBCH760B1')

419

('AA7N011000015',
'AA7N011000084', 	2020-08-19 08:10:05		0
'AAFJ011003020',	2020-07-16 08:11:36		18
'CNBCH760BM')	2020-07-20 08:10:36		20835

1361
('AA7N011000263',	2020-07-01 08:10:09		375	488
'AAFJ011003009',	2020-07-20 08:10:36		425	0
'AAJP011000354',	2020-07-01 08:10:09		95	11
'CNBCH760BX')	2020-07-23 08:14:06		44252	0


SELECT 'pre' pre_post, black_meter tot, serial_number
                   	FROM meter
					WHERE (date_timestamp, serial_number)
                   	IN(SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number
                      FROM meter mtr 
                      INNER JOIN Machine_Archive cd ON cd.SerialNumber = mtr.serial_number
                      WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-07-01 23:59:58'
                      AND cd.org_id_ma = 75 AND cd.school_year = 21
                       GROUP BY mtr.serial_number)
UNION ALL

SELECT #bldgs.building_name, 
	'post' pre_post,
	#SUM(CASE WHEN ser_dt.meter_read IS NULL THEN 0 - ma.`Begin` ELSE ser_dt.meter_read - ma.`Begin` END) tot,
    #SUM(ma.`End` - ma.`Begin`) - SUM(CASE WHEN ser_dt.meter_read IS NULL THEN 0 - ma.`Begin` ELSE ser_dt.meter_read - ma.`Begin` END) vol_post_upgrade,
    SUM(ma.`End` - ma.`Begin`) tot 
    
    
FROM `Machine_Archive` ma 
WHERE ma.school_year = 21 AND ma.org_id_ma = 75








/*('AA7P011000132',
'AAFK011001523',
'AAFK011001608',
'AAFK011001634',
'AAFK011001766',
'AAFK011001800',
'AAJT011001444',
'AAJT011001666',
'ACET011000637')*/
/*AND date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2021-08-03 23:59:59'
ORDER BY serial_number, date_timestamp;*/

/* no records returned
select * from Machine_Archive_Upgrades WHERE org_id_ma = 59
AND `SerialNumber` IN('AA7P011000132',
'A8KN012000053',
'A8KN012000148',
'A8KN012000352',
'AA7P011000450',
'AAFK011001623',
'AAFK011001755',
'AAFK011001766',                      
'AAFK011001787',
'AAFK011001801',
'AAJT011001400',
'AAJT011001494',
'AAJT011001603',
'AAJT011001638',
'AAJT011001703',
'AAJT011001757',
'AAJT011001772',
'AAJT011001806',
'ACET011000632',
'ACET011000634',
'ACET011000635')
ORDER BY SerialNumber;*/


/*SELECT building_id_ma, `SerialNumber`, `Begin`, `End` FROM Machine_Archive
WHERE school_year = 21
AND `SerialNumber` IN('AA7P011000132',
'A8KN012000053',
'A8KN012000148',
'A8KN012000352',
'AA7P011000450',
'AAFK011001623',
'AAFK011001755',
'AAFK011001766',                      
'AAFK011001787',
'AAFK011001801',
'AAJT011001400',
'AAJT011001494',
'AAJT011001603',
'AAJT011001638',
'AAJT011001703',
'AAJT011001757',
'AAJT011001772',
'AAJT011001806',
'ACET011000632',
'ACET011000634',
'ACET011000635')
ORDER BY SerialNumber;*/













/*select * from current_devices where SerialNumber IN('VNB3N04864', 'VNB4F00026', 'VNB3M37235', 'CFGF44639');

SELECT * FROM buildings WHERE org_id = 6 OR id = 1284;

select org_id_ma, building_id_ma, school_year, SerialNumber from Machine_Archive where SerialNumber IN('VNB3N04864', 'VNB4F00026', 'VNB3M37235', 'CFGF44639') ORDER BY SerialNumber;*/

select * from current_devices where SerialNumber IN('G706M960538', 'G176R130311', 'G176R130573', 'C509P100060') ORDER BY SerialNumber;

select * from Machine_Archive where SerialNumber IN('G706M960538', 'G176R130311', 'G176R130573', 'C509P100060') AND school_year = 21 ORDER BY SerialNumber;

select * from meter where serial_number IN('G706M960538', 'G176R130311', 'G176R130573', 'C509P100060') ORDER BY serial_number;

select DISTINCT serial_number from meter where serial_number IN('G706M960538', 'G176R130311', 'G176R130573', 'C509P100060') ORDER BY serial_number, date_timestamp DESC;

select org_id_ma, `End` - `Begin` from Machine_Archive where building_id_ma = 1221 AND school_year = 21;

SELECT `SerialNumber`, building_id_ma, `CostCopy`, `End`, `Begin`, SuM(`End` - `Begin`) tot_yr_vols, org_id_ma
FROM Machine_Archive
WHERE school_year = 21 AND org_id_ma = 65
GROUP BY building_id_ma
ORDER BY building_id_ma, `SerialNumber`;


SELECT `SerialNumber`, building_id_ma, `CostCopy`, `End`, `Begin`, `End` - `Begin` tot_yr_vols, org_id_ma
                    FROM Machine_Archive
        			WHERE school_year = 21 AND org_id_ma = 65
        			GROUP BY building_id_ma;
					
					
SELECT id, dateNewUpgrade FROM `organization`
WHERE id = 55 AND dateNewUpgrade BETWEEN '2020-07-01 00:00:00' AND '2021-06-30 23:59:59';

SELECT * FROM meter
WHERE serial_number IN(SELECT SerialNumber FROM current_devices WHERE org_id_ma = 55)
AND date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 23:59:59'
ORDER BY serial_number, date_timestamp;

SELECT * FROM meter
WHERE serial_number IN('A6DR012102474',
'ACET011001700',
'A8KN011002838',
'A5AY011017642')

AND date_timestamp > '2020-06-01 00:00:00'
#AND date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 23:59:59'
ORDER BY serial_number, date_timestamp;



250
('A8KN012000944',
'A2X1017012022',
'ACET011000490'
'A6DR012102441',
'A79J013001614')

253
('A6DR012102474',
'ACET011001700',
'A8KN011002838',
'A5AY011017642')

264
('A5AY011017977',
'A63N011012604',
'A6DR012102473',
'A6VF011015426',
'A8KN011002452',
'A8KN012000922',
'AAFK011001865',
'ACET011000513',
'ACET011000519')

250 in Machine_Archive_Upgrades
('A2X1017012022',
'A61D011001323',
'A63N011012625',
'A6DR012102441',
'HNA10475',
'HTK12643',
'MEMA000819',
'MGKA100326')


264 in Machine_Archive_Upgrades
('A6DR012102473',
'A6VF011015426',
'A8KN011002452',
'HTK12465',
'HTP10258',
'MEMA001245',
'MXGA002492')

SELECT cd.`SerialNumber` cd_serNum, ma.`SerialNumber` ma_serNum, cd.building_id_ma cd_bldg, cd.org_id_ma cd_org,
ma.building_id_ma ma_bldg, ma.org_id_ma ma_org_id, cd.Begin cd_begin, ma.Begin ma_begin FROM current_devices cd
INNER JOIN Machine_Archive ma ON ma.SerialNumber = cd.SerialNumber
WHERE cd.SerialNumber = 'A8KN011002452'
AND ma.school_year = 21; #org_id_ma = 55 ORDER BY SerialNumber;*/

SELECT bldgs.building_name,
		bldgs.id,
        (CASE WHEN ser_dt.meter_read IS NULL THEN 0 ELSE ser_dt.meter_read - ma.`Begin` END) vol_pre_upgrade,
        ma.tot_yr_vols - (CASE WHEN ser_dt.meter_read Is NULL THEN 0 ELSE ser_dt.meter_read  - ma.`Begin` END) vol_post_upgrade, 
        SUM(ma.tot_yr_vols) - SUM(ser_dt.meter_read  - ma.`Begin`) vol_post_upgrade2,
        ma.`Begin`,
        SUM(ser_dt.meter_read  - ma.`Begin`) meter_read2,
        
        ma.tot_yr_vols tot_vols,
        (CASE WhEN SUM(ser_dt.meter_read) IS NULL THEN 0 ELSE SUM(ser_dt.meter_read * `CostCopy`) END) cost_pre_upgrade,
        (SUM(ma.tot_yr_vols) - CASE WHEN SUM(ser_dt.meter_read) Is NULL THEN 0 ELSE SUM(ser_dt.meter_read) END) * `CostCopy` cost_post_upgrade,
        SUM(ma.tot_yr_vols) * `CostCopy` tot_costs
        
        FROM buildings bldgs
        
        RIGHT JOIN (SELECT `SerialNumber`, building_id_ma, `CostCopy`, `Begin`, SUM(`End` - `Begin`) tot_yr_vols, org_id_ma
                    FROM Machine_Archive
        			WHERE school_year = 21 AND org_id_ma = 55
        			GROUP BY building_id_ma) ma ON ma.building_id_ma = bldgs.id 
        
        LEFT JOIN (SELECT MAX(mtr.date_timestamp) dt,
                    mtr.serial_number,
                    (CASE WHEN SUM(mtr.black_meter) IS NULL THEN 0 ELSE SUM(mtr.black_meter) END) meter_read,
                    cd.building_id_ma
                    FROM meter mtr
                    INNER JOIN  Machine_Archive cd ON cd.SerialNumber = mtr.serial_number AND cd.org_id_ma = 55
                    WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 23:59:59'
                   AND cd.school_year = 21
                    GROUP BY cd.building_id_ma) ser_dt ON ser_dt.building_id_ma = ma.building_id_ma
        
        GROUP BY ma.building_id_ma
        ORDER BY bldgs.building_name;