sql server - Sum along with subtraction of respective columns in sql -

i have show current stock location along location name.have 4 tables tables setup contains branch details,stockinward contains stock location along product id,product outward contains product sales of particular product,productmaster contains deatils of product.here sample record , have tried below function

create function [dbo].[cstock]  (    @sid int  )  returns varchar(max)   begin declare @stckprdt int,@stckprdt1 int,@stockloc varchar(max);    select @stckprdt= sum(qty)from stockinward productid= @sid group  stocklocation   select @stckprdt1=sum(qty)from productoutward productid= @sid group location     return @stckprdt-@stckprdt1 end  go 

productmaster

productid productname  1          washingmachine 2           fridge 3            tv 4             laptop 

setup

id  branchname  1      chennai  2      coimbatore  3      mumbai  4     cochin 

stockinward

id  productid    stocklocation    qty  1      1             4             100 2      4             4              10 3      4              1             10 4      3              2             20 5      `1             1             10 

productoutward

id  productid    stocklocation    qty  1        4          4              2 2         1         4              2 

the output should be

orderedproducts       currentstock  laptop(2)               cochin(8)                          chennai(10)  washingmachine(2)            cochin(98)                            chennai(10) 

on output,currentstock should show sum of product particular location stockinward -sum of product particular location stockoutward

can try this

select productname + '('+convert(nvarchar(10),productoutward.qty)+')' orderedproducts, branchname + '('+convert(nvarchar(10),stockinward.qty-productoutward.qty)+')' currentstock productmaster  inner join productoutward on productmaster.productid = productoutward.productid inner join stockinward on productmaster.productid = stockinward.productid , stockinward.stocklocation = productoutward.stocklocation inner join setup on stockinward.stocklocation = setup.id union select productname + '('+convert(nvarchar(10),0)+')' orderedproducts, branchname + '('+convert(nvarchar(10),stockinward.qty)+')' currentstock productmaster  inner join productoutward on productmaster.productid = productoutward.productid inner join stockinward on productmaster.productid = stockinward.productid , stockinward.stocklocation <> productoutward.stocklocation inner join setup on stockinward.stocklocation = setup.id 

edit : multiple stockinwards & productoutwards

select productname + '('+convert(nvarchar(10),sum(productoutward.qty)/count(distinct stockinward.id))+')' orderedproducts , branchname + '('+convert(nvarchar(10),sum(stockinward.qty)/count(distinct productoutward.id)-sum(productoutward.qty)/count(distinct stockinward.id))+')' currentstock  productmaster  inner join productoutward on productmaster.productid = productoutward.productid inner join stockinward on productmaster.productid = stockinward.productid , stockinward.stocklocation = productoutward.stocklocation inner join setup on stockinward.stocklocation = setup.id  productname = 'laptop' group productname,setup.branchname--,stockinward.id   union  select productname + '('+convert(nvarchar(10),0)+')' orderedproducts, branchname + '('+convert(nvarchar(10),sum(stockinward.qty))+')' currentstock productmaster  inner join productoutward on productmaster.productid = productoutward.productid inner join stockinward on productmaster.productid = stockinward.productid , stockinward.stocklocation <> productoutward.stocklocation inner join setup on stockinward.stocklocation = setup.id productname = 'laptop' group productname,productoutward.qty,branchname 

check out :

select productname + '('+convert(nvarchar(10),sum(productoutward.qty)/count(distinct stockinward.id))+')' orderedproducts , branchname + '('+convert(nvarchar(10),sum(stockinward.qty)/count(distinct productoutward.id)-sum(productoutward.qty)/count(distinct stockinward.id))+')' currentstock  productmaster  inner join productoutward on productmaster.productid = productoutward.productid inner join stockinward on productmaster.productid = stockinward.productid , stockinward.stocklocation = productoutward.stocklocation inner join setup on stockinward.stocklocation = setup.id  productname = 'laptop' group productname,setup.branchname--,stockinward.id  union   select productname + '('+convert(nvarchar(10),sum(productoutward.qty))+')' orderedproducts , branchname + '(0)' currentstock  productmaster  inner join productoutward on productmaster.productid = productoutward.productid inner join setup on productoutward.stocklocation = setup.id  productname = 'laptop' , productmaster.productid not in (select stockinward.productid stockinward stockinward.stocklocation = productoutward.stocklocation) group productname,setup.branchname--,stockinward.id  union   select productname + '(0)' orderedproducts , branchname + '('+convert(nvarchar(10),sum(stockinward.qty))+')' currentstock  productmaster  inner join stockinward on productmaster.productid = stockinward.productid inner join setup on stockinward.stocklocation = setup.id  productname = 'laptop' , productmaster.productid not in (select productoutward.productid productoutward productoutward.stocklocation = stockinward.stocklocation) group productname,setup.branchname--,stockinward.id