I need to retrieve fields for a selected Item using Sql Query
dates between daterange, openingstock, purchaseQty,salesQty, closingDate
Tables I use are
1)ItemMaster(ItemId,ItemName,OpnStock,StockDate)
2)ItemPurchase(PurchaseId,ItemId,PurchaseDate,PurRate,PurQty)
3)ItemSales(SalesId,ItemId,SalesDate,SalesRate,SalesQty)
I need output as follows
Date OpeningStock Purchase Sales ClosingStock
=================================================================================
01-10-2016 10 0 0 10
01-11-2016 10 5 0 15
01-12-2016 15 0 2 13
01-13-2016 13 0 0 13
01-14-2016 13 0 4 9
01-15-2016 9 0 0 9
Here Date range is
From 01-10-2016 to 01-15-2016.
I have used following sql query which is displaying openingStock only.
declare @date_from datetime, @date_to datetime
set @date_from = '2016-01-10'
set @date_to = '2016-01-15'
;with dates as(
select @date_from as dt
union all
select DATEADD(d,1,dt) from dates where dt<@date_to
)
select d.dt, isnull(OpeningStock,0) as OpeningStock
from dates d
left join (
select StockDate, SUM(OpnStock) as OpeningStock
from ItemMaster im
where StockDate between @date_from and @date_to and im.ItemId=1
group by StockDate
) s on d.dt = s.StockDate
order by d.dt
But I need to retrieve Purchase,Sales and Closing quantities also
Thanks