sql

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