SELECT dateNewUpgrade FROM organization where id = 75; #2020-07-01
SELECT SUM(`End` - `Begin`) totVols, building_id_ma FROM Machine_Archive Where school_year = 21 AND org_id_ma = 75 GROUP BY building_id_ma;

SELECT * FROM buildings WHERE id IN(416, 418, 419, 1361);

SELECT `End`, `Begin`, SerialNumber, building_id_ma, org_id_ma, Modified FROM Machine_Archive Where school_year = 21 AND building_id_ma = 419;


Breaking down the indiv queries
SELECT bldgs.building_name,
		ma.`Begin` begin_mtr,
        #meter.black_meter,                
        SUM(`End` - `Begin`) tot_yr_vols,
       	ma.`CostCopy` cpc
     	#meter.serial_number,
     	#meter.date_timestamp
    FROM Machine_Archive ma
    INNER JOIN buildings bldgs ON bldgs.id = ma.building_id_ma AND bldgs.org_id = ma.org_id_ma
    WHERE school_year = 21 AND org_id_ma = 75
    GROUP BY building_name;


SELECT  ser_dt.building_id_ma,
		meter.black_meter,
     	meter.serial_number,
     	meter.date_timestamp
    FROM meter
    LEFT JOIN (SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number, cd.building_id_ma, cd.org_id_ma
               FROM meter mtr
        		INNER JOIN (SELECT SerialNumber, building_id_ma, org_id_ma FROM current_devices WHERE org_id_ma = 75 AND building_id_ma IS NOT NULL) cd
                ON cd.SerialNumber = mtr.serial_number
                WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-07-01 23:59:59'               	
                GROUP BY mtr.serial_number) ser_dt ON ser_dt.serial_number = meter.serial_number AND meter.date_timestamp = ser_dt.dt
                WHERE ser_dt.org_id_ma = 75
                GROUP BY ser_dt.building_id_ma;

select * from meter where date_timestamp = '2020-07-01 08:10:09' and serial_number IN('AAFJ011003020', 'AA7N011000015', 'AA7N011000084', 'CNBCH760BM');





('AA7N011000048',
'AA7N011000063',
'AA7N011000273',
'AAFJ011002984',
'AAFJ011003011',
'AAFJ011003015',
'AAFJ011003019',
'AAFJ011003045',
'AAFJ011003047',
'AAFJ011003052')


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

419
('AA7N011000015',
'AA7N011000084',
'AAFJ011003020',
'CNBCH760BM')

1361
('AA7N011000263',
'AAFJ011003009',
'AAJP011000354',
'CNBCH760BX')




SELECT * FROM meter where date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-07-01 23:59:59' AND serial_number IN ('AA7N011000263',
'AAFJ011003009',
'AAJP011000354',
'CNBCH760BX')

ORDER BY serial_number, date_timestamp DESC;

SELECT * FROM meter where serial_number IN ('AA7N011000263',
'AAFJ011003009',
'AAJP011000354',
'CNBCH760BX')


ORDER BY serial_number, date_timestamp DESC;

SELECT * FROM Machine_Archive where SerialNumber IN ('AA7N011000263',
'AAFJ011003009',
'AAJP011000354',
'CNBCH760BX')

AND school_year = 21
ORDER BY SerialNumber;










SELECT allRows.building_name,
SUM(allRows.black_meter - allRows.begin_mtr) vol_pre_upgrade,
SUM(allRows.tot_yr_vols) - SUM(allRows.black_meter - allRows.begin_mtr) vol_post_upgrade,
SUM(allRows.tot_yr_vols) tot_vols,
SUM(allRows.black_meter) * allRows.cpc cost_pre_upgrade,
(SUM(allRows.tot_yr_vols) - SUM(allRows.black_meter)) * allRows.cpc cost_post_upgrade,
SUM(allRows.tot_yr_vols) * allRows.cpc tot_costs

FROM(SELECT bldgs.building_name,
		ma.begin_mtr,
        meter.black_meter,                
        ma.tot_yr_vols tot_yr_vols,
        ma.cpc
    FROM meter
        
	INNER JOIN (SELECT `SerialNumber`, org_id_ma, building_id_ma, `CostCopy` cpc, `Begin` begin_mtr, SUM(`End` - `Begin`) tot_yr_vols
            FROM Machine_Archive
        	WHERE school_year = 21 AND org_id_ma = 75
        	GROUP BY building_id_ma) ma ON ma.SerialNumber = meter.serial_number 
        
LEFT JOIN (SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number FROM meter mtr
        			INNER JOIN (SELECT SerialNumber, FROM current_devices WHERE org_id_ma = 75) cd
                    ON cd.SerialNumber = mtr.serial_number
                    WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-07-01 23:59:59'
                    GROUP BY mtr.serial_number) ser_dt ON ser_dt.serial_number = meter.serial_number AND meter.date_timestamp = ser_dt.dt                
         
INNER JOIN buildings bldgs ON bldgs.id = ma.building_id_ma AND bldgs.org_id = ma.org_id_ma
WHERE meter.date_timestamp = ser_dt.dt)
GROUP BY bldgs.building_name
ORDER BY bldgs.building_name;




SELECT `SerialNumber`, ma.org_id_ma, ma.building_id_ma, ma.`CostCopy`,
	ma.`End`, ma.`Begin`, SUM(ma.`End` - ma.`Begin`) tot_yr_vols,
	mtr.black_meter
FROM Machine_Archive 
INNER JOIN meter mtr ON mtr.serial_number = ma.SerialNumber

WHERE (mtr.date_timestamp, mtr.serial_number) IN(SELECT MAX(date_timestamp) dt, serial_number
FROM meter WHERE serial_number IN(Select SerialNumber FROM Machine_Archive
WHERE org_id_ma = 75 AND school_year = 21)
AND date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-07-01 23:59:59'
GROUP BY serial_number)
GROUP BY building_id_ma;





SELECT bldgs.building_name,
        SUM(meter.black_meter - ma.`Begin`) vol_pre_upgrade,
        SUM(tot_yr_vols) - SUM(meter.black_meter - ma.`Begin`) vol_post_upgrade,        
        SUM(ma.tot_yr_vols) tot_vols,
        SUM(meter.black_meter) * CostCopy cost_pre_upgrade,
        (SUM(tot_yr_vols) - SUM(meter.black_meter)) * CostCopy cost_post_upgrade,
        SUM(tot_yr_vols) * CostCopy tot_costs
        
        FROM meter
        
        RIGHT JOIN (SELECT `SerialNumber`, building_id_ma, `CostCopy`, `End`, `Begin`, SUM(`End` - `Begin`) tot_yr_vols, org_id_ma
                    FROM Machine_Archive
        			WHERE school_year = ? AND org_id_ma = ?
        			GROUP BY building_id_ma) ma ON ma.SerialNumber = meter.serial_number 
        
        LEFT JOIN (SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number, cd.bldg_id FROM meter mtr
        			INNER JOIN (SELECT SerialNumber, building_id_ma bldg_id FROM current_devices WHERE org_id_ma = ?) cd
                    ON cd.SerialNumber = mtr.serial_number
                    WHERE mtr.date_timestamp BETWEEN ? AND ?
                    GROUP BY mtr.serial_number) ser_dt ON ser_dt.serial_number = meter.serial_number
        
        INNER JOIN buildings bldgs ON bldgs.id = ma.building_id_ma AND bldgs.org_id = ma.org_id_ma
        WHERE date_timestamp = ser_dt.dt
        GROUP BY ma.building_id_ma
        ORDER BY bldgs.building_name;";
		
		
		/***************************************************************************************************************/
		SELECT bldgs.building_name,
        SUM(ser_dt.black_meter - ma.`Begin`) vol_pre_upgrade,
        SUM(tot_yr_vols) - SUM(meter.black_meter - ma.`Begin`) vol_post_upgrade,        
        SUM(ma.tot_yr_vols) tot_vols,
        SUM(meter.black_meter) * CostCopy cost_pre_upgrade,
        (SUM(tot_yr_vols) - SUM(meter.black_meter)) * CostCopy cost_post_upgrade,
        SUM(tot_yr_vols) * CostCopy tot_costs
        
        
        FROM meter
        
        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 = 75
        			GROUP BY building_id_ma) ma ON ma.SerialNumber = meter.serial_number 
        
        LEFT JOIN (SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number, mtr.black_meter FROM meter mtr
        			
                    WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-07-01 23:59:59'
                   	AND mtr.serial_number IN (SELECT SerialNumber FROM current_devices WHERE org_id_ma = 75)
                    
                    GROUP BY mtr.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 meter.date_timestamp = ser_dt.dt
        GROUP BY ma.building_id_ma
        ORDER BY bldgs.building_name;



/*SELECT bldgs.building_name,
        SUM(meter.black_meter - ma.`Begin`) vol_pre_upgrade,
        SUM(tot_yr_vols) - SUM(meter.black_meter - ma.`Begin`) vol_post_upgrade,        
        SUM(ma.tot_yr_vols) tot_vols,
        SUM(meter.black_meter) * CostCopy cost_pre_upgrade,
        (SUM(tot_yr_vols) - SUM(meter.black_meter)) * CostCopy cost_post_upgrade,
        SUM(tot_yr_vols) * CostCopy tot_costs
        
        FROM meter
        
        RIGHT JOIN (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 = 75
        			GROUP BY building_id_ma) ma ON ma.SerialNumber = meter.serial_number 
        
        LEFT JOIN (SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number, cd.bldg_id FROM meter mtr
        			INNER JOIN (SELECT SerialNumber, building_id_ma bldg_id FROM current_devices WHERE org_id_ma = 75) cd
                    ON cd.SerialNumber = mtr.serial_number
                    WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-07-01 23:59:59'
                    GROUP BY mtr.serial_number) ser_dt ON ser_dt.serial_number = meter.serial_number
        
        INNER JOIN buildings bldgs ON bldgs.id = ma.building_id_ma AND bldgs.org_id = ma.org_id_ma
        WHERE date_timestamp = ser_dt.dt
        GROUP BY ma.building_id_ma
        ORDER BY bldgs.building_name;*/
		
		
/*********************************************most current to test************************************************/
SELECT 	bldgs.building_name,
		bldgs.id
        SUM(ser_dt.black_meter - ma.`Begin`) vol_pre_upgrade,
        #SUM(tot_yr_vols) - SUM(meter.black_meter - ma.`Begin`) vol_post_upgrade,        
        SUM(ma.tot_yr_vols) tot_vols,
        #SUM(meter.black_meter) * `CostCopy` cost_pre_upgrade,
        #(SUM(tot_yr_vols) - SUM(meter.black_meter)) * `CostCopy` cost_post_upgrade,
        SUM(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 = 75
        			GROUP BY building_id_ma) ma ON ma.building_id_ma = bldgs.id 
        
        LEFT JOIN (SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number, mtr.black_meter FROM meter mtr
        			
                    WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-07-01 23:59:59'
                   	AND mtr.serial_number IN (SELECT SerialNumber FROM current_devices WHERE org_id_ma = 75)
                    
                    GROUP BY mtr.serial_number) ser_dt ON ser_dt.serial_number = ma.`SerialNumber`        
        
        
        GROUP BY ma.building_id_ma
        ORDER BY bldgs.building_name;



/*SELECT bldgs.building_name,
        SUM(meter.black_meter - ma.`Begin`) vol_pre_upgrade,
        SUM(tot_yr_vols) - SUM(meter.black_meter - ma.`Begin`) vol_post_upgrade,        
        SUM(ma.tot_yr_vols) tot_vols,
        SUM(meter.black_meter) * CostCopy cost_pre_upgrade,
        (SUM(tot_yr_vols) - SUM(meter.black_meter)) * CostCopy cost_post_upgrade,
        SUM(tot_yr_vols) * CostCopy tot_costs
        
        FROM meter
        
        RIGHT JOIN (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 = 75
        			GROUP BY building_id_ma) ma ON ma.SerialNumber = meter.serial_number 
        
        LEFT JOIN (SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number, cd.bldg_id FROM meter mtr
        			INNER JOIN (SELECT SerialNumber, building_id_ma bldg_id FROM current_devices WHERE org_id_ma = 75) cd
                    ON cd.SerialNumber = mtr.serial_number
                    WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-07-01 23:59:59'
                    GROUP BY mtr.serial_number) ser_dt ON ser_dt.serial_number = meter.serial_number
        
        INNER JOIN buildings bldgs ON bldgs.id = ma.building_id_ma AND bldgs.org_id = ma.org_id_ma
        WHERE date_timestamp = ser_dt.dt
        GROUP BY ma.building_id_ma
        ORDER BY bldgs.building_name;*/