Overstock calculation

Transact-SQL
#1

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.

#2

Not sure I fully understand what you're asking for, but maybe this?

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
,[BAAS_PowerBI].[dbo].[BAAS_Daily_Stock].[Current Stock] - SH2.BAAS_qty_sold AS OverStock

		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]

#3

If you include sample data in the form of create tables and insert statements you will likely get some more useful responses.