spcstardoc
select SUM(mtr.black_meter - fpm.commencement_black_meter) c_blk,
SUM(mtr.color_meter - fpm.commencement_color_meter) c_col from Y2022_floorplan_machines fpm
inner join Y2022_floorplans fp on fp.id = fpm.floorplan_id
inner join Y2022_buildings bldgs on bldgs.id = fp.building_id
left join Y2022_meter as mtr on `fpm`.`serial_number` = mtr.`serial_number`
where bldgs.org_id = 4;


#buildings volume query

select
@device_commencement_date := (CASE WHEN fpm.commencement_date IS NULL THEN 1625112000 WHEN UNIX_TIMESTAMP(fpm.commencement_date) < 1625112000 THEN 1625112000 ELSE UNIX_TIMESTAMP(fpm.commencement_date) END) device_commencement_date,

@c_blk := (CASE WHEN fpm.commencement_black_meter IS NULL THEN mtr.black_meter ELSE (mtr.black_meter - fpm.commencement_black_meter) END) c_blk,
@c_col := (CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter ELSE (mtr.color_meter - fpm.commencement_color_meter) END) c_col,
@c_blk_cost := (CASE WHEN fpm.commencement_black_meter IS NULL THEN mtr.black_meter * cpc_black ELSE (mtr.black_meter - fpm.commencement_black_meter) * cpc_black END) c_blk_cost,
@c_col_cost := (CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter * cpc_color ELSE (mtr.color_meter - fpm.commencement_color_meter) * cpc_color END) c_col_cost,
@last_diff_orig := (CASE WHEN mtr.created_date IS NULL THEN 1 ELSE ((UNIX_TIMESTAMP(mtr.created_date) - @device_commencement_date) / 60/60/24) END) last_diff_orig, @last_diff := (CASE WHEN @last_diff_orig <= 0 THEN 1 ELSE @last_diff_orig END) last_diff,
@cm_diff_orig := ((1656647999- @device_commencement_date) /60/60/24) cm_diff_orig,
@cm_diff := (CASE WHEN @cm_diff_orig < 0 THEN 0 ELSE @cm_diff_orig END) cm_diff, (CASE WHEN fpm.commencement_black_meter IS NULL THEN mtr.black_meter ELSE (mtr.black_meter - fpm.commencement_black_meter) END) consumed_black,

(CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter ELSE (mtr.color_meter - fpm.commencement_color_meter) END) consumed_color,
(CASE WHEN 1593576000 IS NULL THEN fpm.budgeted_black WHEN 1593576000 < 1625112000 THEN fpm.budgeted_black ELSE (fpm.budgeted_black / 364) * 364 END) budgeted_black,
(CASE WHEN 1593576000 IS NULL THEN fpm.budgeted_color WHEN 1593576000 < 1625112000 THEN fpm.budgeted_color ELSE (fpm.budgeted_color / 364) * 364 END) budgeted_color, 

@projected_black := (CASE WHEN @cm_diff = 0 THEN 0 WHEN 1593576000 IS NULL THEN (@c_blk / @last_diff * 345) WHEN 1593576000 < 1625112000 THEN (@c_blk / @last_diff * 345) ELSE (@c_blk / @cm_diff * LEAST(364, 345)) END) projected_black,
@projected_color := (CASE WHEN @cm_diff = 0 THEN 0 WHEN 1593576000 IS NULL THEN (@c_col / @last_diff * 345) WHEN 1593576000 < 1625112000 THEN (@c_col / @last_diff * 345) ELSE (@c_col / @cm_diff * LEAST(364, 345)) END) projected_color,
(CASE WHEN @cm_diff = 0 THEN 0 WHEN 1593576000 IS NULL THEN @projected_black WHEN 1593576000 < 1625112000 THEN @projected_black ELSE (@c_blk / @cm_diff * LEAST(364, 345)) END) proj_blk_year_next,
(CASE WHEN @cm_diff = 0 THEN 0 WHEN 1593576000 IS NULL THEN @projected_color WHEN 1593576000 < 1625112000 THEN @projected_color ELSE (@c_col / @cm_diff * LEAST(364, 345)) END) proj_col_year_next,
(CASE WHEN fpm.commencement_black_meter IS NULL THEN mtr.black_meter * cpc_black ELSE (mtr.black_meter - fpm.commencement_black_meter) * cpc_black END) consumed_black_cost, (CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter * cpc_color ELSE (mtr.color_meter - fpm.commencement_color_meter) * cpc_color END) consumed_color_cost, (CASE WHEN 1625112000 IS NULL THEN fpm.budgeted_black * cpc_black WHEN 1625112000 < 1625112000 THEN fpm.budgeted_black * cpc_black ELSE (fpm.budgeted_black / 364) * 364 * cpc_black END) budgeted_black_cost,
(CASE WHEN 1625112000 IS NULL THEN fpm.budgeted_color * cpc_color WHEN 1625112000 < 1625112000 THEN fpm.budgeted_color * cpc_color ELSE (fpm.budgeted_color / 364) * 364 * cpc_color END) budgeted_color_cost,
@projected_black_cost := (CASE WHEN @cm_diff = 0 THEN 0 WHEN 1593576000 IS NULL THEN (@c_blk_cost / @last_diff * 345) WHEN 1593576000 < 1625112000 THEN (@c_blk_cost / @last_diff * 345) ELSE (@c_blk_cost / @cm_diff * LEAST(364, 345)) END) projected_black_cost,
@projected_color_cost := (CASE WHEN @cm_diff = 0 THEN 0 WHEN 1593576000 IS NULL THEN (@c_col_cost / @last_diff * 345) WHEN 1593576000 < 1625112000 THEN (@c_col_cost / @last_diff * 345) ELSE (@c_col_cost / @cm_diff * LEAST(364, 345)) END) projected_color_cost, (CASE WHEN @cm_diff = 0 THEN 0 WHEN 1593576000 IS NULL THEN @projected_black_cost * 1.035 WHEN 1593576000 < 1625112000 THEN @projected_black_cost * 1.035 ELSE (@c_blk_cost / @cm_diff * LEAST(364, 345) * 1.035) END) proj_blk_year_next_cost,
(CASE WHEN @cm_diff = 0 THEN 0 WHEN 1593576000 IS NULL THEN @projected_color_cost * 1.035 WHEN 1593576000 < 1625112000 THEN @projected_color_cost * 1.035 ELSE (@c_col_cost / @cm_diff * LEAST(364, 345) * 1.035) END) proj_col_year_next_cost,

bldgs.bldg_id, bldgs.bldg_name, bldgs.student_pop, mtr.created_date, fpm.closeout_date, fpm.proposed_serial_number, fpm.room_name, mach_make, model, is_color, fp.floorplan_image, fpm.ip_address, fpm.cpc_black, fpm.cpc_color

from `y2022_floorplan_machines` as `fpm`
inner join `y2022_floorplans` as `fp` on `fp`.`fp_id` = `fpm`.`floorplans_id`
inner join `y2022_buildings` as `bldgs` on `bldgs`.`bldg_id` = `fp`.`buildings_id`
left join `y2022_meters` as `mtr` on `fpm`.`proposed_serial_number` = `mtr`.`serial_number`
inner join (select max(created_date) as dt, serial_number
			from `y2022_meters`
			where `created_date` >= '2021-07-01 00:00:00'
			and `created_date` <= '2022-06-30 23:59:59'
			and `black_meter` != 0 group by `serial_number`) as `serNumDateMetersMax`
			on `mtr`.`serial_number` = `serNumDateMetersMax`.`serial_number` and `mtr`.`created_date` = `serNumDateMetersMax`.`dt`
inner join (select * from `model_details` as `mach`) as `mach_data` on `fpm`.`proposed_model_id` = `mach_data`.`model_id`

where `fpm`.`proposed_serial_number` in (select `fm`.`proposed_serial_number`
											from `y2022_floorplan_machines` as `fm`
											inner join `y2022_floorplans` as `fp` on `fp`.`fp_id` = `fm`.`floorplans_id`
											inner join `y2022_buildings` as `bldgs` on `bldgs`.`bldg_id` = `fp`.`buildings_id`
											where `bldgs`.`organizations_id` = 1781)
											order by `bldgs`.`bldg_name` asc, `fpm`.`proposed_serial_number` asc