locked
Get Opening Closing Query Based On Two SQL Table RRS feed

  • Question

  • User-807418713 posted

    Hello

    As i need to know how to get opening, arrival, issues, closing quantity from two tables

    This is my table1 data

    Item Arrival Date Item Name Arrived Qty
    1-Nov-19 AA 2
    2-Nov-19 AA 8
    2-Nov-19 BB 6
    3-Nov-19 AA 1
    3-Nov-19 AA 1
    3-Nov-19 AA 2
    3-Nov-19 CC 4
    4-Nov-19 BB 1
    4-Nov-19 CC 2
    4-Nov-19 BB 1

    This is my table2 data

    Item Issue Date Item Name Issued Qty
    1-Nov-19 AA 1
    2-Nov-19 AA 3
    2-Nov-19 BB 1
    2-Nov-19 CC 2
    2-Nov-19 AA 1
    3-Nov-19 CC 1
    3-Nov-19 AA 5
    4-Nov-19 AA 3
    4-Nov-19 CC 1

    If I pass 2-Nov-19 To 4-Nov-2019 in sql query then Item Name As AA I want resultset like this below

    Date Item Name Opening Quantiy Arived Quantity Issued Quantity Closing Quantity
    2-Nov-19 AA Opening Stock 1 8 3 6
    3-Nov-19 AA 6 4 5 5
    4-Nov-19 AA 5 0 3 2

    How would be sql query

    Thanking You

    Thursday, October 24, 2019 5:27 PM

Answers

  • User-719153870 posted

    Hi Gopi.MCA,

    Before whatever the arrived- issued = Opening Quanity Of 2-Nov-2019

    Now Opening Quantity + In between From To Date Whatever Arrived - Issued = Closing_Qty

    According to this logic, most of the data in your expected resultset would be incorrect.

    Please refer to below demo which is built based on the logic not the resultset table from your first post:

    create table #table1
    (
    [Item Arrival Date] date,
    [Item Name] varchar(50),
    [Arrived Qty] int
    )
    
    insert into #table1 values('2019-10-1','AA',2)
    insert into #table1 values('2019-10-2','AA',8)
    insert into #table1 values('2019-10-2','BB',6)
    insert into #table1 values('2019-10-3','AA',1)
    insert into #table1 values('2019-10-3','AA',1)
    insert into #table1 values('2019-10-3','AA',2)
    insert into #table1 values('2019-10-3','CC',4)
    insert into #table1 values('2019-10-4','BB',1)
    insert into #table1 values('2019-10-4','CC',2)
    insert into #table1 values('2019-10-4','BB',1)
    
    create table #table2
    (
    [Item Issue Date] date,
    [Item Name] varchar(50),
    [Issued Qty] int
    )
    
    insert into #table2 values('2019-10-1','AA',1)
    insert into #table2 values('2019-10-2','AA',3)
    insert into #table2 values('2019-10-2','BB',1)
    insert into #table2 values('2019-10-2','CC',2)
    insert into #table2 values('2019-10-2','AA',1)
    insert into #table2 values('2019-10-3','CC',1)
    insert into #table2 values('2019-10-3','AA',5)
    insert into #table2 values('2019-10-4','AA',3)
    insert into #table2 values('2019-10-4','CC',1)
    
    --select * from #table1
    --select * from #table2
    
    declare @start date
    declare @end date
    declare @item varchar(50)
    declare @i date
    declare @s varchar(max)
    set @start='2019-10-02'
    set @end='2019-10-04'
    set @item='AA'
    set @i=@start
    set @s=''
    while @i<=@end
    begin
    set @s+='
    select '''+convert(varchar(50),@i)+''' as [Date],'''+@item+''' as [Item Name],a.[Opening Quantiy], a.[Arived Quantity],a.[Issued Quantity],
    (a.[Arived Quantity]+a.[Opening Quantiy]-a.[Issued Quantity]) as [Closing Quantity] from
    (
    select (select isnull(SUM([Arrived Qty]),0) from #table1 where [Item Arrival Date]<'''+convert(varchar(50),@i)+''' and [Item Name]='''+@item+''')-
           (select isnull(SUM([Issued Qty]),0) from #table2 where [Item Issue Date]<'''+convert(varchar(50),@i)+''' and [Item Name]='''+@item+''') as [Opening Quantiy],
           (select isnull(SUM([Arrived Qty]),0) from #table1 where [Item Arrival Date]='''+convert(varchar(50),@i)+''' and [Item Name]='''+@item+''') as [Arived Quantity],
           (select isnull(SUM([Issued Qty]),0) from #table2 where [Item Issue Date]='''+convert(varchar(50),@i)+''' and [Item Name]='''+@item+''') as [Issued Quantity]
    )a UNION ALL'
    set @i=DATEADD(DAY,1,@i)
    end
    set @s=SUBSTRING(@s,0,LEN(@s)-9)
    exec(@s)

    This demo is dynamical, if you want to change 'AA' to 'BB' or change the data range, you would only need to change three parameters: @start@end and @item.

    Below is the result:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 1, 2019 2:39 AM

All replies

  • User452040443 posted

    Hi,

    What version of SQL Server are you using?

    Thursday, October 24, 2019 8:29 PM
  • User-719153870 posted

    Hi Gopi.MCA,

    Date Item Name Opening Quantiy Arived Quantity Issued Quantity Closing Quantity
    2-Nov-19 AA Opening Stock 1 8 3 6
    3-Nov-19 AA 6 4 5 5
    4-Nov-19 AA 5 0 3 2

    Where you get the [Opening Quantity] and [Closing Quantity]? It's hard to tell from above two tables you provided.

    As for the [Arived Quantity] and [Issued Quantity], you can refer to below demo.

    The whole point is to select the data from both two tables and then use the left join to select the final result you want.

    create table #table1
    (
    [Item Arrival Date] date,
    [Item Name] varchar(50),
    [Arrived Qty] int
    )
    
    insert into #table1 values('2019-10-1','AA',2)
    insert into #table1 values('2019-10-2','AA',8)
    insert into #table1 values('2019-10-2','BB',6)
    insert into #table1 values('2019-10-3','AA',1)
    insert into #table1 values('2019-10-3','AA',1)
    insert into #table1 values('2019-10-3','AA',2)
    insert into #table1 values('2019-10-3','CC',4)
    insert into #table1 values('2019-10-4','BB',1)
    insert into #table1 values('2019-10-4','CC',2)
    insert into #table1 values('2019-10-4','BB',1)
    
    create table #table2
    (
    [Item Issue Date] date,
    [Item Name] varchar(50),
    [Issued Qty] int
    )
    
    insert into #table2 values('2019-10-1','AA',1)
    insert into #table2 values('2019-10-2','AA',3)
    insert into #table2 values('2019-10-2','BB',1)
    insert into #table2 values('2019-10-2','CC',2)
    insert into #table2 values('2019-10-2','AA',1)
    insert into #table2 values('2019-10-3','CC',1)
    insert into #table2 values('2019-10-3','AA',5)
    insert into #table2 values('2019-10-4','AA',3)
    insert into #table2 values('2019-10-4','CC',1)
    
    --select * from #table1
    --select * from #table2
    
    --drop table #table1
    --drop table #table2
    
    --select [Item Arrival Date] as [Date],min([Item Name]) as [Item Name], isnull(SUM([Arrived Qty]),0) as [Arived Quantity] from #table1 where [Item Name]='AA' group by [Item Arrival Date]
    
    --select [Item Issue Date] as [Date],min([Item Name]) as [Item Name],sum([Issued Qty]) as [Issued Quantity]  from #table2 where [Item Name]='AA' group by [Item Issue Date]
    
    select a.Date,a.[Item Name],a.[Issued Quantity],isnull(b.[Arived Quantity],0) as [Arived Quantity] from (select [Item Issue Date] as [Date],min([Item Name]) as [Item Name],sum([Issued Qty]) as [Issued Quantity]  from #table2 where [Item Name]='AA' group by [Item Issue Date]) a left join
    (select [Item Arrival Date] as [Date],min([Item Name]) as [Item Name], isnull(SUM([Arrived Qty]),0) as [Arived Quantity] from #table1 where [Item Name]='AA' group by [Item Arrival Date]) b on a.Date=b.Date where a.Date between '2019-10-02' and '2019-10-14'

    Below is the result:

    Best Regard,

    Yang Shen

    Friday, October 25, 2019 2:55 AM
  • User-807418713 posted

    Hello

    Thank You For Your Reply

    But i want opening_Quantity

    Arrived_Quantity

    Issued_Quantity

    Closing_Quantity

    Can i get sql code

    based on my above output

    Thanks

    Wednesday, October 30, 2019 6:41 PM
  • User-719153870 posted

    Hi Gopi.MCA,

    Can you please explain how are you getting opening_Quantity and Closing_Quantity from above two tables?

    We can't find any rules about how you get these two fields data from that.

    Best Regard,

    Yang Shen

    Thursday, October 31, 2019 5:07 AM
  • User-807418713 posted

    Hello

    Im passing From Date As 2-Nov-19  And To 4-Nov-2019

    Before whatever the arrived- issued = Opening Quanity Of 2-Nov-2019

    Now Opening Quantity + In between From To Date Whatever Arrived - Issued = Closing_Qty

    Thanking You

    Thursday, October 31, 2019 10:01 AM
  • User-719153870 posted

    Hi Gopi.MCA,

    Before whatever the arrived- issued = Opening Quanity Of 2-Nov-2019

    Now Opening Quantity + In between From To Date Whatever Arrived - Issued = Closing_Qty

    According to this logic, most of the data in your expected resultset would be incorrect.

    Please refer to below demo which is built based on the logic not the resultset table from your first post:

    create table #table1
    (
    [Item Arrival Date] date,
    [Item Name] varchar(50),
    [Arrived Qty] int
    )
    
    insert into #table1 values('2019-10-1','AA',2)
    insert into #table1 values('2019-10-2','AA',8)
    insert into #table1 values('2019-10-2','BB',6)
    insert into #table1 values('2019-10-3','AA',1)
    insert into #table1 values('2019-10-3','AA',1)
    insert into #table1 values('2019-10-3','AA',2)
    insert into #table1 values('2019-10-3','CC',4)
    insert into #table1 values('2019-10-4','BB',1)
    insert into #table1 values('2019-10-4','CC',2)
    insert into #table1 values('2019-10-4','BB',1)
    
    create table #table2
    (
    [Item Issue Date] date,
    [Item Name] varchar(50),
    [Issued Qty] int
    )
    
    insert into #table2 values('2019-10-1','AA',1)
    insert into #table2 values('2019-10-2','AA',3)
    insert into #table2 values('2019-10-2','BB',1)
    insert into #table2 values('2019-10-2','CC',2)
    insert into #table2 values('2019-10-2','AA',1)
    insert into #table2 values('2019-10-3','CC',1)
    insert into #table2 values('2019-10-3','AA',5)
    insert into #table2 values('2019-10-4','AA',3)
    insert into #table2 values('2019-10-4','CC',1)
    
    --select * from #table1
    --select * from #table2
    
    declare @start date
    declare @end date
    declare @item varchar(50)
    declare @i date
    declare @s varchar(max)
    set @start='2019-10-02'
    set @end='2019-10-04'
    set @item='AA'
    set @i=@start
    set @s=''
    while @i<=@end
    begin
    set @s+='
    select '''+convert(varchar(50),@i)+''' as [Date],'''+@item+''' as [Item Name],a.[Opening Quantiy], a.[Arived Quantity],a.[Issued Quantity],
    (a.[Arived Quantity]+a.[Opening Quantiy]-a.[Issued Quantity]) as [Closing Quantity] from
    (
    select (select isnull(SUM([Arrived Qty]),0) from #table1 where [Item Arrival Date]<'''+convert(varchar(50),@i)+''' and [Item Name]='''+@item+''')-
           (select isnull(SUM([Issued Qty]),0) from #table2 where [Item Issue Date]<'''+convert(varchar(50),@i)+''' and [Item Name]='''+@item+''') as [Opening Quantiy],
           (select isnull(SUM([Arrived Qty]),0) from #table1 where [Item Arrival Date]='''+convert(varchar(50),@i)+''' and [Item Name]='''+@item+''') as [Arived Quantity],
           (select isnull(SUM([Issued Qty]),0) from #table2 where [Item Issue Date]='''+convert(varchar(50),@i)+''' and [Item Name]='''+@item+''') as [Issued Quantity]
    )a UNION ALL'
    set @i=DATEADD(DAY,1,@i)
    end
    set @s=SUBSTRING(@s,0,LEN(@s)-9)
    exec(@s)

    This demo is dynamical, if you want to change 'AA' to 'BB' or change the data range, you would only need to change three parameters: @start@end and @item.

    Below is the result:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 1, 2019 2:39 AM