Departments - Gauges WIP

select depts.organizations_id, orgs.org_name from y2019_departments depts
INNER JOIN organizations orgs ON orgs.org_id = depts.organizations_id
GROUP BY depts.organizations_id;


select distinct departments_id FROM y2019_floorplan_machines;

select * FROM y2019_floorplan_machines where departments_id = 4;



select fpm.budgeted_black as budget_blk, fpm.budgeted_color as budget_col, fpm.commencement_black_meter,
fpm.commencement_color_meter, fpm.commencement_date as dev_commencement_date, fpm.cpc_black, fpm.cpc_color,
fpm.budgeted_black, fpm.budgeted_color, mtr.black_meter as black_meter, mtr.color_meter as color_meter,
mtr.black_meter - fpm.commencement_black_meter as cons_blk, mtr.color_meter - fpm.commencement_color_meter as cons_col,
mtr.black_meter * fpm.cpc_black as cons_blk_cost, mtr.color_meter * fpm.cpc_color as cons_col_cost, mtr.created_date,
fpm.proposed_serial_number, org.commencement_date as org_commencement_date

from `y2019_meters` as `mtr`
inner join `y2019_floorplan_machines` as `fpm` on `fpm`.`proposed_serial_number` = `mtr`.`serial_number`
inner join `y2019_floorplans` as `fp` on `fp`.`fp_id` = `fpm`.`floorplans_id`
inner join `y2019_departments` as `depts` on `depts`.`dept_id` = `fpm`.`departments_id`
inner join `organizations` as `org` on `depts`.`organizations_id` = `org`.`org_id`
inner join (select max(created_date) as dt, serial_number
			from `y2019_meters` where `created_date` >= '2018-07-01 00:00:00' and `created_date` <= '2019-06-30 00:00:00'
			group by `serial_number`) as `serNumDateMetersMax`
			on `mtr`.`serial_number` = `serNumDateMetersMax`.`serial_number`
			and `mtr`.`created_date` = `serNumDateMetersMax`.`dt`
			where `fpm`.`proposed_serial_number` in (select `fm`.`proposed_serial_number`
													from `y2019_floorplan_machines` as `fm`
													inner join `y2019_floorplans` as `fp`
													on `fp`.`fp_id` = `fm`.`floorplans_id`
													
													inner join `y2019_buildings` as `bldgs`
													on `bldgs`.`bldg_id` = `fp`.`buildings_id`
													where `bldgs`.`organizations_id` = 4);
													
													
													
													
													
/*select distinct departments_id FROM y2019_floorplan_machines;

select * FROM y2019_floorplan_machines where departments_id = 4;
select * FROM y2019_departments where dept_id = 4;

Select * FROM departments WHERE organizations_id = 4;*/



select * FROM y2019_floorplan_machines fpm where `fpm`.`proposed_serial_number` in (select `fm`.`proposed_serial_number`
													from `y2019_floorplan_machines` as `fm`
													inner join `y2019_floorplans` as `fp`
													on `fp`.`fp_id` = `fm`.`floorplans_id`
													
													inner join `y2019_departments` as `depts`
													on `depts`.`dept_id` = `fm`.`departments_id`
													where `depts`.`organizations_id` = 4)
                                                    order By fpm.departments_id;
                                                    
                                                    



/*select depts.dept_name, depts.dept_id, fpm.budgeted_black as budget_blk, fpm.budgeted_color as budget_col, fpm.commencement_black_meter,
fpm.commencement_color_meter, fpm.commencement_date as dev_commencement_date, fpm.cpc_black, fpm.cpc_color,
fpm.budgeted_black, fpm.budgeted_color, mtr.black_meter as black_meter, mtr.color_meter as color_meter,
mtr.black_meter - fpm.commencement_black_meter as cons_blk, mtr.color_meter - fpm.commencement_color_meter as cons_col,
mtr.black_meter * fpm.cpc_black as cons_blk_cost, mtr.color_meter * fpm.cpc_color as cons_col_cost, mtr.created_date,
fpm.proposed_serial_number, org.commencement_date as org_commencement_date

from `y2019_meters` as `mtr`
inner join `y2019_floorplan_machines` as `fpm` on `fpm`.`proposed_serial_number` = `mtr`.`serial_number`
inner join `y2019_floorplans` as `fp` on `fp`.`fp_id` = `fpm`.`floorplans_id`
inner join `y2019_departments` as `depts` on `depts`.`dept_id` = `fpm`.`departments_id`
inner join `organizations` as `org` on `depts`.`organizations_id` = `org`.`org_id`
inner join (select max(created_date) as dt, serial_number
			from `y2019_meters` where `created_date` >= '2018-07-01 00:00:00' and `created_date` <= '2019-06-30 00:00:00'
            and black_meter is not null
			group by `serial_number`) as `serNumDateMetersMax`
			on `mtr`.`serial_number` = `serNumDateMetersMax`.`serial_number`
			and `mtr`.`created_date` = `serNumDateMetersMax`.`dt`
			where `fpm`.`proposed_serial_number` in (select `fm`.`proposed_serial_number`
													from `y2019_floorplan_machines` as `fm`
													inner join `y2019_floorplans` as `fp`
													on `fp`.`fp_id` = `fm`.`floorplans_id`
													
													inner join `y2019_departments` as `depts`
													on `depts`.`dept_id` = `fm`.`departments_id`
													where `depts`.`organizations_id` = 4)
                                                    order By depts.dept_name;*/