Bom Dia,
realizei uma query acerca da reposiçao do estoque e a quantidade que deve ser reposta.Nela contem parametros para ter reposiçao, talvez ou nao.....quando tem que ter reposicao ou talvez ela deveria indicar o numero que deve ser reposto, entretanto ela retorna
0 ou NULL.Segue a query:
select
sh.category,
it.subcategory1,
it.subcategory2,
it.product_type,
it.name,
sh.color,
sh.size,
sh.brand,
sh.store,
sh.cost,
case
when abc.curve = 'A' then '1'
when abc.curve = 'B' then '2'
when bs.best_seller = '1' then '1'
when st.days_in_stock <= '60' then '1'
when st.days_in_stock <= '90' then '2'
when exists (select sum(quantity) from data_warehouse.dbo.sales_item where date>=getdate()-60 and sku_store=sh.sku_store and canceled = '0' and quantity>= '3')then '1'
when exists (select sum(quantity) from data_warehouse.dbo.sales_item where date>=getdate()-90 and sku_store=sh.sku_store and canceled = '0' and quantity >= '4')then '2'
when exists (select sum(quantity) from data_warehouse.dbo.sales_item where date>=getdate()-120 and sku_store=sh.sku_store and canceled = '0'and quantity >= '5')then '0'
when st.stock_turnover >= '2' then '1'
when st.stock_turnover between '1' and '2' then '2'
when sa.stock_age >= '2' then '1'
when sa.stock_age between '1' and '2' then '2'
when sh.brand = sh.brand then '1'
when sh.category = sh.category then '1'
when sh.color = sh.color then '1'
when sh.size = sh.size then '1'
else '0' end reposiçao,
ceiling(
case
when st.days_in_stock>30 and ads.avg_daily_sales_60<0.075 then '0'
when bs.best_seller=1 or abc.curve='A'or abc.curve='B' or st.stock_turnover>0.4
then (gsc.goal_stock_cover+lt.lead_time)*ads.avg_daily_sales_60*(1-plc.product_lifecycle)*
case
when st.days_in_stock<3 and ads.avg_daily_sales_60>0.2 then 0.15
when st.days_in_stock<=7 and ads.avg_daily_sales_60>0.5 then 0.67
else 1 end
-case when s.quantity>'0' then s.quantity else '0' end-(select sum(quantity) from data_warehouse.dbo.pedidos_abertos where date<getdate()+50 and sku_store=sh.sku_store)
-case when s.quantity>'0' then s.quantity else '0' end-(select sum(quantity) from data_warehouse.dbo.pedidos_abertos where date<getdate()+50 and sku_store=sh.sku_store)+(gsc.goal_stock_cover+lt.lead_time)*ads.avg_daily_sales_60*(1-plc.product_lifecycle)/2
end*
case
when st.days_in_stock<3 and ads.avg_daily_sales_60>0.2 then 0.15
when st.days_in_stock<=7 and ads.avg_daily_sales_60>0.5 then 0.67
else 1 end)
current_need_qty
from data_warehouse.dbo.stock_history sh
left join data_warehouse.dbo.item_info it
on it.category = sh.category
and it.cost = sh.cost
left join data_warehouse.dbo.abc_curve abc
on abc.sku_store = sh.sku_store
left join data_warehouse.dbo.best_seller bs
on bs.sku_store = sh.sku_store
left join data_warehouse.dbo.stock_turnover st
on st.sku_store = sh.sku_store
left join data_warehouse.dbo.avg_stock_age sa
on sa.sku_store = sh.sku_store
left join data_warehouse.dbo.avg_daily_sales_60 ads
on ads.sku_store = sh.sku_store
left join data_warehouse.dbo.stock s
on s.sku_store = sh.sku_store
left join data_warehouse.dbo.goal_stock_cover gsc
on gsc.category = sh.category and gsc.subcategory1 = it.subcategory1
left join data_warehouse.dbo.lead_time lt
on lt.brand = sh.brand
left join data_warehouse.dbo.product_lifecycle plc
on plc.category = sh.category and plc.subcategory1 = it.subcategory1
where sh.category = 'feminino jovem'
and it.subcategory1 = '18-feminino jovem'
and sh.store = 'GL'
and it.subcategory2 = '021 calca'
and it.product_type = 'cos medio'
Acredito que o problema esteja no case when do eeling porem nao consigo enxergar aonde.Alguem tem alguma sugestao?
Desde ja muito obrigada
