select isNull(Purchase.ProductCode,0)as'Product Code',
isNull(ps.Quantity,0) as 'Opening Stock',
isNull(Purchase.Quantity,0) as 'Purchase Quantity',
isNull(Saleitem.Quantity,0) 'Sale Quantity'
,isNull(ps.Quantity + Purchase.Quantity - Saleitem.Quantity,0) as 'Closing Stock',
convert(date,Purchase.date,101)as'Date'
from
(select ProductCode,Quantity,CONVERT(date,DateTime,101)date from stocktable
where Status='Purchase Entry'
and ProductCode = 4
group by ProductCode,Quantity,CONVERT(date,DateTime,101))Purchase
Full join
(
select ProductCode,convert(date,DateTime,101)as date,SUM(quantity)as Quantity
from Stocktable
where Status='Sale Entry'
group by ProductCode ,convert(date,DateTime,101)
)Saleitem
on Saleitem.ProductCode='4'
full join
(
select p.Quantity,p.productcode,p.vendorcode from purechaseStockreport p
group by p.Quantity ,p.productcode,p.vendorcode
)ps
on ps.productcode = '4'
or ps.productcode = '4'
DISTIselect isNull(Purchase.ProductCode,0)as'Product Code',
isNull(ps.Quantity,0) as 'Opening Stock',
isNull(Purchase.Quantity,0) as 'Purchase Quantity',
isNull(Saleitem.Quantity,0) 'Sale Quantity'
,isNull(ps.Quantity + Purchase.Quantity - Saleitem.Quantity,0) as 'Closing Stock',
convert(date,Purchase.date,101)as'Date'
from
(select ProductCode,Quantity,CONVERT(date,DateTime,101)date from stocktable
where Status='Purchase Entry'
and ProductCode = 4
group by ProductCode,Quantity,CONVERT(date,DateTime,101))Purchase
Full join
(
select ProductCode,convert(date,DateTime,101)as date,SUM(quantity)as Quantity
from Stocktable
where Status='Sale Entry'
group by ProductCode ,convert(date,DateTime,101)
)Saleitem
on Saleitem.ProductCode='4'
full join
(
select p.Quantity,p.productcode,p.vendorcode from purechaseStockreport p
group by p.Quantity ,p.productcode,p.vendorcode
)ps
on ps.productcode = '4'
or ps.productcode = '4'NIC