Untitled

not very smart attempt in creating this query note: please affix the prefixes to the table names accordingly
 avatar
user_0223141589
sql
a year ago
1.3 kB
5
Indexable
select
fvm.name as part_model,
spl.id,
spl.product_id,
spl.name as component_lot,
spl_top.name as top_level_lot,
mbv.START_DATE,
mbv.START_REFERENCE,
mbv.START_MODEL,
case 
	when vs.maintenance_type_id = '1' then 'upgrade'
	else ''
end as start_maintenance_type,
mbv.END_DATE,
mbv.END_REFERENCE,
mbv.END_MODEL,
case 
	when vs2.maintenance_type_id = '1' then 'upgrade'
	else ''
end as end_maintenance_type,
spl2.name as original_serial,
spl2.id as original_serial_id
from as_maintenance_bom_version mbv
left join stock_production_lot spl on spl.id = mbv.LOT_ID
left join stock_production_lot spl_top on spl_top.id = mbv.TOP_PARENT_LOT_ID
left join product_product pp on pp.id = spl.PRODUCT_ID
left join product_template pt on pt.id = pp.PRODUCT_TMPL_ID
left join fleet_vehicle_model fvm on fvm.id = pt.PRODUCT_TEMPLATE_ROTABLE_MODEL_ID
left join stock_production_lot spl2 on spl.original_lot_id = spl2.id
left join fleet_repair fr on fr.name = mbv.start_reference 
left join vehicle_services vs on vs.repair_id = fr.id
left join fleet_repair fr2 on fr2.name = mbv.end_reference  
left join vehicle_services vs2 on vs2.repair_id = fr2.id
--left join fleet_maintenance_type fmt on fmt.id = vs.maintenance_type_id 
where spl_top.name = 'SBDY-000-000978' and spl.product_id != 707
order by mbv.id desc
Editor is loading...