Hi guys, I am beginner in SQLServer codings and would appreciate help on a little tasks i have to perfrom for my boss: Basically I need to calculate an overstock on article number level. For this I need to compare the current stock level in our warehouses with the consumption of stock in the past 5 years. If we have more on stock than we have consumed in the past 5 years then the surplus should be shown as overstock qty in an extra column:
Past 5 years consumption: First of all I had to union 2 tables in order to have a complete stock consumption
select
[BAAS_PowerBI].[dbo].[BAAS_Daily_Stock].[Warehouse code]
,[BAAS_PowerBI].[dbo].[BAAS_Daily_Stock].[Item code]
,[BAAS_PowerBI].[dbo].[BAAS_Daily_Stock].[Free text 2]
,[BAAS_PowerBI].[dbo].[BAAS_Daily_Stock].[Current Stock]
,[BAAS_PowerBI].[dbo].[BAAS_Daily_Stock].[unit cost]
,[BAAS_PowerBI].[dbo].[BAAS_Daily_Stock].[unit cost currency]
,SH2.BAAS_qty_sold
from [BAAS_PowerBI].[dbo].[BAAS_Daily_Stock]
Left join
(select
sum(SH.Bill_qty) as BAAS_qty_sold
, SH.MM_Material
from
-- Union starts here
(SELECT
Bill_BillingDate
, MM_Material
, Bill_qty
FROM dbo.BAAS_Bill_done
UNION All
SELECT
Bill_BillingDate
, MM_Material
, Bill_qty
FROM [BAAS_PowerBI].dbo.[GreatPlains Sales History 2012-102017]) SH
where
sh.Bill_BillingDate > dateadd(year, -5, getdate())
group by
SH.MM_Material) SH2
on [BAAS_PowerBI].[dbo].[BAAS_Daily_Stock].[Item code]= SH2.[mm_material]
the code above does what it should. It joins the table baas daily stock with the unioned consumption tables (summarizing the stock consumption).
the next action in the query should be to calculate the overstock qty per material : ([current stock] -sh2.Baas_qty_sold.
This should be a new column in the output table. And here I struggle completely to get this work. Could you help me on this?
Thank you in advance
Br
c.