To get orgs that have been upgraded during the school year
SELECT id, org_name, dateNewUpgrade

FROM organization

WHERE dateNewUpgrade is not null
AND status = "Active Client"
AND org_type_id <> 7
AND dateNewUpgrade BETWEEN '2020-06-30' AND '2021-06-30'

or org_name = 'North Country Council'
or org_name = 'NHSAU 03 - Berlin Public Schools'

ORDER BY org_name;

/****************************************************************************/

SELECT 'pre' pre_or_post, meter.date_timestamp, meter.black_meter, meter.color_meter  FROM meter

WHERE (date_timestamp, serial_number) IN(SELECT MAX(mtr.date_timestamp), mtr.serial_number
FROM meter mtr
WHERE serial_number IN (SELECT SerialNumber FROM current_devices WHERE org_id_ma = 1838)
AND date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-07-01 23:59:58'
GROUP BY serial_number)

UNION ALL
SELECT 'post' pre_or_post, meter.date_timestamp, meter.black_meter, meter.color_meter  FROM meter

WHERE (date_timestamp, serial_number) IN(SELECT MAX(mtr.date_timestamp), mtr.serial_number
FROM meter mtr
WHERE serial_number IN (SELECT SerialNumber FROM current_devices WHERE org_id_ma = 1838)
AND date_timestamp BETWEEN '2020-07-01 23:59:59' AND '2021-06-30 23:59:59'
GROUP BY serial_number)


/**********************************************************************************/
SELECT meter.date_timestamp, meter.black_meter, meter.color_meter, ma.End, ma.Begin  FROM meter
INNER JOIN (SELECT * FROM Machine_Archive ma_ar
WHERE school_year = 21 AND org_id_ma = 59) ma ON ma.SerialNumber = meter.serial_number

WHERE (date_timestamp, serial_number) IN(SELECT MAX(mtr.date_timestamp), mtr.serial_number
FROM meter mtr
WHERE serial_number IN (SELECT SerialNumber FROM current_devices WHERE org_id_ma = 59)
AND date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 23:59:58'
GROUP BY serial_number)

/************Using meter for pre and Machine_Archive for post******************/
SELECT meter.date_timestamp, meter.black_meter, ma.End, ma.Begin, meter.color_meter, ma.MstrBegin, ma.MstrEnd,
(ma.End - ma.Begin) post_vols, ((ma.End - ma.Begin) + meter.black_meter) totVols
FROM meter
RIGHT JOIN (SELECT * FROM Machine_Archive ma_ar
			WHERE school_year = 21 AND org_id_ma = 59) ma ON ma.SerialNumber = meter.serial_number

WHERE (date_timestamp, serial_number) IN(SELECT MAX(mtr.date_timestamp), mtr.serial_number
FROM meter mtr
WHERE serial_number IN (SELECT SerialNumber FROM current_devices WHERE org_id_ma = 59)
AND date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 23:59:58'
GROUP BY serial_number);

/*************************************group by serial_number vs group by building************/
/*SELECT id, org_name, dateNewUpgrade
FROM organization
WHERE dateNewUpgrade is not null
AND status = "Active Client"
AND org_type_id <> 7
AND dateNewUpgrade BETWEEN '2020-06-30' AND '2021-06-30'
or org_name = 'North Country Council'
or org_name = 'NHSAU 03 - Berlin Public Schools'
ORDER BY org_name; #1893, 1781, 13, 93

SELECT * FROM Machine_Archive WHERE org_id_ma = 1710 AND school_year = 21 ORDER BY SerialNumber;*/

#1
/*SELECT meter.date_timestamp, meter.black_meter, meter.color_meter  FROM meter

WHERE (date_timestamp, serial_number) IN(SELECT MAX(mtr.date_timestamp), mtr.serial_number
FROM meter mtr
WHERE serial_number IN (SELECT SerialNumber FROM current_devices WHERE org_id_ma = 59)
AND date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 23:59:58'
GROUP BY serial_number)*/;

#2 time 1:57.46
/*SELECT meter.date_timestamp, meter.black_meter, meter.color_meter, ma.End, ma.Begin  FROM meter
INNER JOIN Machine_Archive ma ON ma.SerialNumber = meter.serial_number

WHERE (date_timestamp, serial_number) IN(SELECT MAX(mtr.date_timestamp), mtr.serial_number
FROM meter mtr
WHERE serial_number IN (SELECT SerialNumber FROM current_devices WHERE org_id_ma = 59)
AND date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 23:59:58'
GROUP BY serial_number)*/

#3 time 25.58

SELECT bldgs.building_name, meter.date_timestamp, meter.black_meter, ma.End, ma.Begin, meter.color_meter, ma.MstrBegin, ma.MstrEnd,
(ma.End - ma.Begin - meter.black_meter) post_vols, ((ma.End - ma.Begin) + meter.black_meter) totVols
FROM meter
RIGHT JOIN (SELECT * FROM Machine_Archive ma_ar
			WHERE school_year = 21 AND org_id_ma = 59) ma ON ma.SerialNumber = meter.serial_number
INNER JOIN buildings bldgs ON bldgs.id = ma.building_id_ma

WHERE (date_timestamp, serial_number) IN(SELECT MAX(mtr.date_timestamp), mtr.serial_number
FROM meter mtr
WHERE serial_number IN (SELECT SerialNumber FROM current_devices WHERE org_id_ma = 59)
AND date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 23:59:58'
GROUP BY serial_number);


/***group by buildings*/

SELECT bldgs.building_name, meter.black_meter pre_upgrade_vol, ma.End, ma.Begin, meter.color_meter, ma.MstrBegin, ma.MstrEnd,
SUM(ma.End - ma.Begin - meter.black_meter) post_vols, SUM((ma.End - ma.Begin) + meter.black_meter) totVols
FROM meter
RIGHT JOIN (SELECT * FROM Machine_Archive ma_ar
			WHERE school_year = 21 AND org_id_ma = 59) ma ON ma.SerialNumber = meter.serial_number
INNER JOIN buildings bldgs ON bldgs.id = ma.building_id_ma

WHERE (date_timestamp, serial_number) IN(SELECT MAX(mtr.date_timestamp), mtr.serial_number
FROM meter mtr
WHERE serial_number IN (SELECT SerialNumber FROM current_devices WHERE org_id_ma = 59)
AND date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 23:59:58'
GROUP BY serial_number)
GrOUP BY bldgs.building_name;