sql
user_0223141589
mysql
2 years ago
4.8 kB
7
Indexable
with all_servos as ( select inv.serial_number , inv.DEFAULT_CODE as part_number , lot_stat.name as status -- Identify location ID based on installation situation , case -- If the servo's grandparent is installed, pull the location of the great-grandparent when gp_loc.STOCK_LOCATION_ID = 15 then ggp_loc.STOCK_LOCATION_ID -- If the servo's parent is installed, pull the location of the grandparent when parent_loc.STOCK_LOCATION_ID = 15 then gp_loc.STOCK_LOCATION_ID -- If servo is installed, look at the location of the parent when inv.STOCK_LOCATION_ID = 15 then parent_loc.STOCK_LOCATION_ID -- Otherwise, look at the location of the part else inv.STOCK_LOCATION_ID end as location_id -- Identify location based on installation situation , case -- If the servo's grandparent is installed, pull the location of the great-grandparent when gp_loc.STOCK_LOCATION_ID = 15 then ggp_loc.COMPLETE_NAME -- If the servo's parent is installed, pull the location of the grandparent when parent_loc.STOCK_LOCATION_ID = 15 then gp_loc.COMPLETE_NAME -- If servo is installed, look at the location of the parent when inv.STOCK_LOCATION_ID = 15 then parent_loc.COMPLETE_NAME -- Otherwise, look at the location of the part else inv.LOCATION_COMPLETE_NAME end as location , parent_lot.name as parent_sn , gparent_lot.name as gparent_sn , ggparent_lot.name as ggparent_sn , case when parent_loc.STOCK_LOCATION_ID = 15 then true else false end as installed_parent , case when gp_loc.STOCK_LOCATION_ID = 15 then true else false end as installed_gp from BIZ.DBT_ODOO.INVENTORY as inv --left join BIZ.DBT_ODOO.LOCATIONS as loc on inv.STOCK_LOCATION_ID = loc.stock_location_id left join BIZ.DBT_STG.STG_ODOO_PROD__STOCK_PRODUCTION_LOT as lot on inv.SERIAL_NUMBER = lot.NAME left join BIZ.DBT_STG.STG_ODOO_PROD__STOCK_PRODUCTION_LOT_STATUS as lot_stat on lot.stock_production_lot_status_id = lot_stat.id -- We need lot.top_parent_lot_id <> lot.id because if this level is the top level of a part, top_parent_lot_id will be populated with the id of this level and we only want to look at the next level if it's different than the current one left join BIZ.DBT_STG.STG_ODOO_PROD__STOCK_PRODUCTION_LOT as parent_lot on lot.top_parent_lot_id = parent_lot.id and lot.top_parent_lot_id <> lot.id -- Get production lot info for part's parent left join BIZ.DBT_ODOO.LOCATIONS as parent_loc on parent_lot.location_id = parent_loc.stock_location_id -- We need parent_lot.top_parent_lot_id <> parent_lot.id because if this level is the top level of a part, top_parent_lot_id will be populated with the id of this level and we only want to look at the next level if it's different than the current one left join BIZ.DBT_STG.STG_ODOO_PROD__STOCK_PRODUCTION_LOT as gparent_lot on parent_lot.top_parent_lot_id = gparent_lot.id and parent_lot.top_parent_lot_id <> parent_lot.id -- Get production lot info for part's grandparent left join BIZ.DBT_ODOO.LOCATIONS as gp_loc on gparent_lot.location_id = gp_loc.stock_location_id -- We need gparent_lot.top_parent_lot_id <> gparent_lot.id because if this level is the top level of a part, top_parent_lot_id will be populated with the id of this level and we only want to look at the next level if it's different than the current one left join BIZ.DBT_STG.STG_ODOO_PROD__STOCK_PRODUCTION_LOT as ggparent_lot on gparent_lot.top_parent_lot_id = ggparent_lot.id and gparent_lot.top_parent_lot_id <> gparent_lot.id left join BIZ.DBT_ODOO.LOCATIONS as ggp_loc on ggparent_lot.location_id = ggp_loc.stock_location_id where inv.QUANTITY > 0 and inv.DEFAULT_CODE in ('00826-000', '00826-001') and inv.location_complete_name <> 'My Company: Inventory adjustment' and inv.location_complete_name <> 'Virtual Locations/My Company: Inventory adjustment' and inv.location_complete_name <> 'Virtual Locations/Zipline: Consumption' and inv.location_complete_name <> 'HATCH/Hatchery Warehouse/R&D Pickup Hatchery' and inv.location_complete_name <> 'Partner Locations/Customers' and inv.location_complete_name <> 'Partner Locations/Vendors' -- We don't care about scrapped servos and lot_stat.name <> 'Scrapped' and inv.location_complete_name <> 'My Company: Scrap' --and inv.serial_number = '2046372' --HAVING location <> 'My Company: Scrap' order by 1 ) select nests.country , count(*) from all_servos as servos left join BIZ.DBT_ODOO.LOCATIONS as loc on servos.location_id = loc.STOCK_LOCATION_ID left join BIZ.DBT_ODOO.STOCK_WAREHOUSES as wh on loc.WAREHOUSE_ID = wh.WAREHOUSE_ID left join BIZ.DBT.DIM_NESTS as nests on wh.X_NEST_ID = nests.NEST_ID group by country order by country
Editor is loading...