Include all articles in stock balance

BI tools Google Bigquery (SQL)
,
1

Hey all! Excited to share with you the SQL query from our developer that will help you list stock articles even if they have no items.

P.S. Do not forget to change {uid} to your workspace ID :wink:

with fact_stockbalance as (
  select
  ItemId,
  CASE 
    WHEN ENZ_SYNC_TS = (
        SELECT MAX(ENZ_SYNC_TS)
        FROM fortnox_api_{uid}.stockbalance
    ) THEN AvailableStock
    ELSE null
  END AS AvailableStock,
  CASE 
    WHEN ENZ_SYNC_TS = (
        SELECT MAX(ENZ_SYNC_TS)
        FROM fortnox_api_{uid}.stockbalance
    ) THEN InStock
    ELSE null
  END AS InStock,
  StockPointCode,
  OrgId,
  OrgId || '-' || ItemId as ArticleId,
  OrgId || '-' || StockPointCode as StockPointId,
from
  fortnox_api_{uid}.stockbalance
),
articles_stockbalance as (select
  a.*
  except(ArticleId, QuantityInStock, ReservedQuantity, StockValue),
  s.StockPointCode as StockPointCode,
  sp.Name as StockPointName,
  ifnull(s.Instock,0) as Instock,
  ifnull(s.AvailableStock,0) as AvailableStock
from
  fact_stockbalance s
  full outer join fortnox_bi_{uid}.dim_articles a on s.ArticleId = a.ArticleId
  left join fortnox_bi_{uid}.dim_stockpoints sp on s.StockPointId = sp.StockPointId
where
a.StockGoods is true
) 
select * from articles_stockbalance where ArticleNumber = {optional: your ArticleNumber in quotes}
2 Likes