legacy
select * from Y2019_floorplan_machines where serial_number IN('VND3F20743', 'VND3J34727');

select * from Y2019_machine_status where serial_number IN('VND3F20743', 'VND3J34727');

SELECT * FROM Y2019_machine_status WHERE serial_number IN('VND3F20743', 'VND3J34727');;#machine_id = 28880;


star2020


#-------------------DeviceSummaries--------------#
select * from `y2019_floorplan_machines` as `fpm`

/******************Begin - This is the differing code*********************/
left join `y2019_machine_statuses` as `ms` on `ms`.`serial_number` = `fpm`.`proposed_serial_number`

inner join (select max(created_date) as dt, serial_number
            from `y2019_machine_statuses` where `created_date` > '2018-07-01 00:00:00' and `created_date` < '2019-06-30 00:00:00'
            group by `serial_number`) as `serNumDate` on `ms`.`serial_number` = `serNumDate`.`serial_number` and `ms`.`created_date` = `serNumDate`.`dt`
/******************End - This is the differing code*********************/



            
inner join `y2019_floorplans` as `fp` on `fp`.`fp_id` = `fpm`.`floorplans_id`
inner join `model_details` as `mach` on `mach`.`model_id` = `fpm`.`proposed_model_id`
left join `y2019_buildings` as `bldg` on `bldg`.`bldg_id` = `fp`.`buildings_id`
inner join `organizations` as `orgs` on `orgs`.`org_id` = `bldg`.`organizations_id`
where `fpm`.`floorplans_id` in (select `fp`.`fp_id` from `y2019_floorplans` as `fp`
                                inner join `y2019_buildings` as `bldgs` on `bldgs`.`bldg_id` = `fp`.`buildings_id`
                                where `fp`.`buildings_id` in (select `bldg_id` from `y2019_buildings`
                                                              where `organizations_id` = 1781))
and `fpm`.`proposed_model_id` != 790 and `fpm`.`fyer_groups_id` is not null
order by `fpm`.`proposed_serial_number`;

/************Begin This works!!*************/
select * from `y2019_floorplan_machines` as `fpm`

LEFT JOIN(SELECT ms.serial_number, ms.toner, ms.service_needed FROM `y2019_machine_statuses` as `ms`

			inner join (select max(created_date) as dt, serial_number
            		from `y2019_machine_statuses`
                    where `created_date` > '2018-07-01 00:00:00' and `created_date` < '2019-06-30 00:00:00'
            		group by `serial_number`) as `serNumDate`
          			on `ms`.`serial_number` = `serNumDate`.`serial_number` and `ms`.`created_date` = `serNumDate`.`dt`) tot_mach
ON `tot_mach`.`serial_number` = `fpm`.`proposed_serial_number`

            
inner join `y2019_floorplans` as `fp` on `fp`.`fp_id` = `fpm`.`floorplans_id`
inner join `model_details` as `mach` on `mach`.`model_id` = `fpm`.`proposed_model_id`
left join `y2019_buildings` as `bldg` on `bldg`.`bldg_id` = `fp`.`buildings_id`
inner join `organizations` as `orgs` on `orgs`.`org_id` = `bldg`.`organizations_id`
where `fpm`.`floorplans_id` in (select `fp`.`fp_id` from `y2019_floorplans` as `fp`
                                inner join `y2019_buildings` as `bldgs` on `bldgs`.`bldg_id` = `fp`.`buildings_id`
                                where `fp`.`buildings_id` in (select `bldg_id` from `y2019_buildings`
                                                              where `organizations_id` = 1781))
and `fpm`.`proposed_model_id` != 790 and `fpm`.`fyer_groups_id` is not null
order by `fpm`.`proposed_serial_number`;
            
/************End This works!!*************/




#-------------------gauges---------------#
select * from `y2019_floorplan_machines` as `fpm`
where `fpm`.`floorplans_id` in (select `fp`.`fp_id` from `floorplans` as `fp`
                                inner join `buildings` as `bldgs` on `bldgs`.`bldg_id` = `fp`.`buildings_id`
                                where `fp`.`buildings_id` in (select `bldg_id` from `buildings`
                                                              where `organizations_id` = 1781))
and `fpm`.`proposed_model_id` != 790 and `fpm`.`fyer_groups_id` is not null
order by `fpm`.`proposed_serial_number`;





select * from y2019_floorplan_machines where proposed_serial_number IN('VND3F20743', 'VND3J34727');

select * from machine_archive where serial_number IN('VND3F20743', 'VND3J34727');

select * from y2019_machine_statuses where serial_number IN('VND3F20743', 'VND3J34727');

/*select * from model_details where model = 'Laser Jet Pro P1606dn';
select * from y2019_floorplan_machines where proposed_model_id = 1010;*/