locked
Date Wise Opening and Closing Stock in SQL RRS feed

  • Question

  • User-1190924364 posted

    This is my query ...

    declare @Opening Table(Product varchar(10), Qty int, Date date)
    
    insert into @Opening
    select 'A', 0 ,'01-Oct-2020' 
    
    declare @Arrival Table(Product varchar(10), Qty int, Date date)
    insert into @Arrival
    Select 'A',	78000,	'13-Oct-2020' union all
    Select 'A',	78000,	'13-Oct-2020' union all
    Select 'A',	76000,	'17-Oct-2020' union all
    Select 'A',	99000,	'17-Oct-2020' union all
    Select 'A',	82000,	'17-Oct-2020'
    
    
    declare @Issue Table(Product varchar(10), Qty int, Date date)
    insert into @Issue
    select 'A', 150 ,'14-Oct-2020' union all
    select 'A',	16000	,'14-Oct-2020' union all
    select 'A',	13000	,'16-Oct-2020' union all
    select 'A',	12000	,'16-Oct-2020' union all
    select 'A',	36000	,'16-Oct-2020' union all
    select 'A',	17000	,'17-Oct-2020' union all
    select 'A',	26000	,'17-Oct-2020' union all
    select 'A',	42000	,'17-Oct-2020' union all
    select 'A',	27000	,'19-Oct-2020' union all
    select 'A',	13000	,'19-Oct-2020' union all
    select 'A',	2000	,'19-Oct-2020' union all
    select 'A',	2000	,'19-Oct-2020' union all
    select 'A',	3000	,'19-Oct-2020'
    
    
    declare @fromdate date, @todate date
    set @fromdate = '12-Oct-2020'
    set @todate = '20-Oct-2020'
    
    ;with cte as(
    	select @fromdate as dt
    	union all
    	select DATEADD(d,1,dt) from cte where dt<@todate
    )
    select c.dt, t.Product, sum( isnull(o.Qty,0) ) as [OpeningQty], sum( isnull(p.Qty,0) ) as [PurchaseQty]
    		, sum( isnull(s.Qty,0) ) as [SalesQty]	, sum( isnull(p.Qty,0)-isnull(s.Qty,0) ) as [tempcol]
    		into #temp 
    from cte c 
    cross join (select distinct Product from @Opening) t
    left join @Opening o on o.Date = c.dt and o.Product = t.Product
    left join @Arrival p on p.Date = c.dt and p.Product = t.Product
    left join @Issue s on s.Date = c.dt and s.Product = t.Product
    Group By c.dt, t.Product
    
    select Distinct t1.Product as Product, t1.dt as Date, t1.[ClosingQty] - t2.[PurchaseQty]  + t2.[SalesQty]  as [OpeningQty], t2.[PurchaseQty] as ArrivalQty, t2.[SalesQty] as IssueQty, t1.[ClosingQty]
    from (
    	select t1.dt, t1.Product,sum(t2.[OpeningQty] + t2.[PurchaseQty]  - t2.[SalesQty]) as [ClosingQty] from #temp t1
    	left join #temp t2 on t1.Product = t2.Product and t1.dt >= t2.dt
    	group by t1.dt, t1.Product
    ) t1
    inner join #temp t2 on t1.Product = t2.Product and t1.dt = t2.dt 
    
    order by t1.Product, t1.dt
    
    drop table #temp

    Output

    Product	Date	OpeningQty	ArrivalQty	IssueQty	ClosingQty
    A	2020-10-12	0	0	0	0
    A	2020-10-13	0	156000	0	156000
    A	2020-10-14	156000	0	16150	139850
    A	2020-10-15	139850	0	0	139850
    A	2020-10-16	139850	0	61000	78850
    A	2020-10-17	78850	771000	255000	594850
    A	2020-10-18	594850	0	0	594850
    A	2020-10-19	594850	0	47000	547850
    A	2020-10-20	547850	0	0	547850

    The above is fine fine. But On 17-Oct-2020, i dont have that much of arrival, what is wrong with the query.

    Kindly help what is wrong in the query and what to change ....

    Tuesday, October 20, 2020 5:26 AM

Answers

  • User-939850651 posted

    Hi umerfaiz007,

    Based on the query statements you provided, after testing and analysis, I think there is a problem with the logic.

    Whenever the data in the @Issue table matches the @Arrival data, the sum will be calculated once. In the @Issue table, there are three records of data with the date ‘17-Oct-2020’, so the sum is calculated three times, and this problem arises.

    I made some modifications to the query to fix this problem, please refer to the following statement:

    declare @Opening Table(Product varchar(10), Qty int, Date date)
    
    insert into @Opening
    select 'A', 0 ,'01-Oct-2020' 
    
    declare @Arrival Table(Product varchar(10), Qty int, Date date)
    insert into @Arrival
    Select 'A',	78000,	'13-Oct-2020' union all
    Select 'A',	78000,	'13-Oct-2020' union all
    Select 'A',	76000,	'17-Oct-2020' union all
    Select 'A',	99000,	'17-Oct-2020' union all
    Select 'A',	82000,	'17-Oct-2020'
    
    
    declare @Issue Table(Product varchar(10), Qty int, Date date)
    insert into @Issue
    select 'A', 150 ,'14-Oct-2020' union all
    select 'A',	16000	,'14-Oct-2020' union all
    select 'A',	13000	,'16-Oct-2020' union all
    select 'A',	12000	,'16-Oct-2020' union all
    select 'A',	36000	,'16-Oct-2020' union all
    select 'A',	17000	,'17-Oct-2020' union all
    select 'A',	26000	,'17-Oct-2020' union all
    select 'A',	42000	,'17-Oct-2020' union all
    select 'A',	27000	,'19-Oct-2020' union all
    select 'A',	13000	,'19-Oct-2020' union all
    select 'A',	2000	,'19-Oct-2020' union all
    select 'A',	2000	,'19-Oct-2020' union all
    select 'A',	3000	,'19-Oct-2020'
    
    
    declare @fromdate date, @todate date
    set @fromdate = '12-Oct-2020'
    set @todate = '20-Oct-2020'
    
    ;with cte as(
    	select @fromdate as dt
    	union all
    	select DATEADD(d,1,dt) from cte where dt<@todate
    )
    select c.dt, t.Product, sum( isnull(o.Qty,0) ) as [OpeningQty], sum( isnull(p.Qty,0) ) as [PurchaseQty]
    		, sum( isnull(s.Qty,0) ) as [SalesQty]	, sum( isnull(p.Qty,0)-isnull(s.Qty,0) ) as [tempcol]
    		into #temp 
    from cte c 
    cross join (select distinct Product from @Opening) t
    left join @Opening o on o.Date = c.dt 
    left join (select Product,sum(Qty) Qty,Date from @Arrival group by Date,Product) p on p.Date = c.dt 
    left join (select Product,sum(Qty) Qty,Date from @Issue group by Date,Product) s on s.Date = c.dt 
    Group By c.dt, t.Product
    
    select Distinct t1.Product as Product, t1.dt as Date, t1.[ClosingQty] - t2.[PurchaseQty]  + t2.[SalesQty]  as [OpeningQty], t2.[PurchaseQty] as ArrivalQty, t2.[SalesQty] as IssueQty, t1.[ClosingQty]
    from (
    	select t1.dt, t1.Product,sum(t2.[OpeningQty] + t2.[PurchaseQty]  - t2.[SalesQty]) as [ClosingQty] from #temp t1
    	left join #temp t2 on t1.Product = t2.Product and t1.dt >= t2.dt
    	group by t1.dt, t1.Product
    ) t1
    inner join #temp t2 on t1.Product = t2.Product and t1.dt = t2.dt 
    
    order by t1.Product, t1.dt
    
    drop table #temp

    Result:

    If I misunderstood something, please let me know.

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 21, 2020 8:38 AM