locked
Query To Get Daily Available Stock RRS feed

  • Question

  • User2033107836 posted

    Hello 

    I Have Three Table Opening, Arrival, Issue

    Opening Table
    
    Field1	Opening_Date	Qty
    
    Apple	01-Dec-2017	10
    Mango	01-Dec-2017	0
    
    
    Arrival Table
    
    Field1	Arrival_Date	Qty
    Apple	01-Dec-2017	2
    Apple	04-Dec-2017	5
    Apple	06-Dec-2017	10
    Mango	04-Dec-2017	2
    
    Issue Table
    
    Field1	Arrival_Date	Qty
    Apple	03-Dec-2017	1
    Apple	07-Dec-2017	6
    Mango	08-Dec-2017	1
    Mango	09-Dec-2017	1
    
    If i pass date 01-Dec-2017 To 10-Dec-2017 and Apple I Need Result Set Like This
    
    Date		Descriptions	Stock_Add	Stock_Subtract		Balance
    01-Dec-2017	Opening		2					12
    03-Dec-2017	Issued		0		1			11
    04-Dec-2017	Arrival		5					16
    06-Dec-2017	Arrival		10					26
    07-Dec-2017	Issued				6			20
    

    Please give me code

    Wednesday, December 20, 2017 7:02 PM

Answers

  • User991499041 posted

    Hi Asp.ambur,

    When using sql server 2008, we can use row_number and self join via row_number.

    declare @Opening table(Field1 nvarchar(20),Opening_Date nvarchar(20),Qty int)
    declare @Arrival table(Field1 nvarchar(20),Arrival_Date nvarchar(20),Qty int)
    declare @Issue table(Field1 nvarchar(20),Arrival_Date nvarchar(20),Qty int)
    
    insert into @Opening values
    ('Apple','01-Dec-2017',10),
    ('Mango','01-Dec-2017',0)
    
    insert into @Arrival values
    ('Apple','01-Dec-2017',2),
    ('Apple','04-Dec-2017',5),
    ('Apple','06-Dec-2017',10),
    ('Mango','04-Dec-2017',2)
    
    insert into @Issue values
    ('Apple','03-Dec-2017',1),
    ('Apple','07-Dec-2017',6),
    ('Mango','08-Dec-2017',1),
    ('Mango','09-Dec-2017',1)
    
    ;with t1 as
    (
     select * from @Opening where Field1='Apple' and Opening_Date between '01-Dec-2017' and '10-Dec-2017'
    ),
    t2 as
    ( 
     select * from @Arrival where Field1='Apple' and Arrival_Date between '01-Dec-2017' and '10-Dec-2017'
     ),
    t3 as
    (
     select * from @Issue where Field1='Apple' and Arrival_Date between '01-Dec-2017' and '10-Dec-2017'
     ),
     t4 as
     (
     select 
     case when t1.Opening_Date is null and t2.Arrival_Date is not null then t2.Arrival_Date 
     when t1.Opening_Date is null and t2.Arrival_Date is null then t3.Arrival_Date
     else t1.Opening_Date end as Date,
     case when t1.Field1 is not null then 'Opening' 
     when t1.Field1 is null and t2.Field1 is not null then 'Arrival'
     when t1.Field1 is null and t2.Field1 is null then 'Issued'
     end as Descriptions,
     ISNULL(t2.Qty,0) as Stock_Add,
     ISNULL(t3.Qty,0) as Stock_Subtract,
     ISNULL(t1.Qty,0)+ISNULL(t2.Qty,0)-ISNULL(t3.Qty,0) as Balance
     from t1 
     full join t2 on t1.Opening_Date= t2.Arrival_Date
     full join t3 on t1.Opening_Date=t3.Arrival_Date
     ),
    t5 as
    (
     select Date,Descriptions,Stock_Add,Stock_Subtract,balance,row_number() over(order by date) as rownum from t4
    )
    
    select a.Date,a.Descriptions,a.Stock_Add,a.Stock_Subtract,sum(b.Balance) as Balance 
    from t5 as a join t5 as b on a.rownum>=b.rownum
    group by a.Date,a.Descriptions,a.Stock_Add,a.Stock_Subtract
    
    
    

    Screenshot:

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 27, 2017 6:50 AM

All replies

  • User347430248 posted

    Hi asp.ambur,

    based on your requirement, you need to use Pivot table.

    with the help of that you can display the data in rows and column format.

    you can also use aggregate function in that to do the calculation.

    below is an example of Pivot query.

    USE AdventureWorks2014;  
    GO  
    SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5  
    FROM   
    (SELECT PurchaseOrderID, EmployeeID, VendorID  
    FROM Purchasing.PurchaseOrderHeader) p  
    PIVOT  
    (  
    COUNT (PurchaseOrderID)  
    FOR EmployeeID IN  
    ( [250], [251], [256], [257], [260] )  
    ) AS pvt  
    ORDER BY pvt.VendorID;  

    you can try to refer it and create your own query to achieve your requirement.

    Reference:

    FROM - Using PIVOT and UNPIVOT

    SQL Server: PIVOT Clause

    Regards

    Deepak

    Friday, December 22, 2017 9:46 AM
  • User2033107836 posted

    Hello

    can you please give me complete code based on my sample data

    I'm not well known to pivot in sql

    Hope you do the needful

    Thanking You

    Waiting for complete sql code

    Thanks

    Friday, December 22, 2017 9:58 AM
  • User991499041 posted

    Hi Asp.ambur,

    You can try

    declare @Opening table(Field1 nvarchar(20),Opening_Date nvarchar(20),Qty int)
    declare @Arrival table(Field1 nvarchar(20),Arrival_Date nvarchar(20),Qty int)
    declare @Issue table(Field1 nvarchar(20),Arrival_Date nvarchar(20),Qty int)
    
    insert into @Opening values
    ('Apple','01-Dec-2017',10),
    ('Mango','01-Dec-2017',0)
    
    insert into @Arrival values
    ('Apple','01-Dec-2017',2),
    ('Apple','04-Dec-2017',5),
    ('Apple','06-Dec-2017',10),
    ('Mango','04-Dec-2017',2)
    
    insert into @Issue values
    ('Apple','03-Dec-2017',1),
    ('Apple','07-Dec-2017',6),
    ('Mango','08-Dec-2017',1),
    ('Mango','09-Dec-2017',1)
    
    ;with t1 as
    (
     select * from @Opening where Field1='Apple' and Opening_Date between '01-Dec-2017' and '10-Dec-2017'
    ),
    t2 as
    ( 
     select * from @Arrival where Field1='Apple' and Arrival_Date between '01-Dec-2017' and '10-Dec-2017'
     ),
    t3 as
    (
     select * from @Issue where Field1='Apple' and Arrival_Date between '01-Dec-2017' and '10-Dec-2017'
     ),
     t4 as
     (
     select 
     case when t1.Opening_Date is null and t2.Arrival_Date is not null then t2.Arrival_Date 
     when t1.Opening_Date is null and t2.Arrival_Date is null then t3.Arrival_Date
     else t1.Opening_Date end as Date,
     case when t1.Field1 is not null then 'Opening' 
     when t1.Field1 is null and t2.Field1 is not null then 'Arrival'
     when t1.Field1 is null and t2.Field1 is null then 'Issued'
     end as Descriptions,
     ISNULL(t2.Qty,0) as Stock_Add,
     ISNULL(t3.Qty,0) as Stock_Subtract,
     ISNULL(t1.Qty,0)+ISNULL(t2.Qty,0)-ISNULL(t3.Qty,0) as Balance
     from t1 
     full join t2 on t1.Opening_Date= t2.Arrival_Date
     full join t3 on t1.Opening_Date=t3.Arrival_Date
     )
    
    select Date,Descriptions,Stock_Add,Stock_Subtract,sum(Balance) over(order by date) as Balance from t4
    

    Screenshot

    Regards,

    zxj

    Monday, December 25, 2017 4:34 AM
  • User2033107836 posted

    Helo zxj

    error

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'over'.

    Tuesday, December 26, 2017 10:22 AM
  • User2033107836 posted

    I'm using sql server 2008 

    Tuesday, December 26, 2017 10:22 AM
  • User991499041 posted

    Hi Asp.ambur,

    When using sql server 2008, we can use row_number and self join via row_number.

    declare @Opening table(Field1 nvarchar(20),Opening_Date nvarchar(20),Qty int)
    declare @Arrival table(Field1 nvarchar(20),Arrival_Date nvarchar(20),Qty int)
    declare @Issue table(Field1 nvarchar(20),Arrival_Date nvarchar(20),Qty int)
    
    insert into @Opening values
    ('Apple','01-Dec-2017',10),
    ('Mango','01-Dec-2017',0)
    
    insert into @Arrival values
    ('Apple','01-Dec-2017',2),
    ('Apple','04-Dec-2017',5),
    ('Apple','06-Dec-2017',10),
    ('Mango','04-Dec-2017',2)
    
    insert into @Issue values
    ('Apple','03-Dec-2017',1),
    ('Apple','07-Dec-2017',6),
    ('Mango','08-Dec-2017',1),
    ('Mango','09-Dec-2017',1)
    
    ;with t1 as
    (
     select * from @Opening where Field1='Apple' and Opening_Date between '01-Dec-2017' and '10-Dec-2017'
    ),
    t2 as
    ( 
     select * from @Arrival where Field1='Apple' and Arrival_Date between '01-Dec-2017' and '10-Dec-2017'
     ),
    t3 as
    (
     select * from @Issue where Field1='Apple' and Arrival_Date between '01-Dec-2017' and '10-Dec-2017'
     ),
     t4 as
     (
     select 
     case when t1.Opening_Date is null and t2.Arrival_Date is not null then t2.Arrival_Date 
     when t1.Opening_Date is null and t2.Arrival_Date is null then t3.Arrival_Date
     else t1.Opening_Date end as Date,
     case when t1.Field1 is not null then 'Opening' 
     when t1.Field1 is null and t2.Field1 is not null then 'Arrival'
     when t1.Field1 is null and t2.Field1 is null then 'Issued'
     end as Descriptions,
     ISNULL(t2.Qty,0) as Stock_Add,
     ISNULL(t3.Qty,0) as Stock_Subtract,
     ISNULL(t1.Qty,0)+ISNULL(t2.Qty,0)-ISNULL(t3.Qty,0) as Balance
     from t1 
     full join t2 on t1.Opening_Date= t2.Arrival_Date
     full join t3 on t1.Opening_Date=t3.Arrival_Date
     ),
    t5 as
    (
     select Date,Descriptions,Stock_Add,Stock_Subtract,balance,row_number() over(order by date) as rownum from t4
    )
    
    select a.Date,a.Descriptions,a.Stock_Add,a.Stock_Subtract,sum(b.Balance) as Balance 
    from t5 as a join t5 as b on a.rownum>=b.rownum
    group by a.Date,a.Descriptions,a.Stock_Add,a.Stock_Subtract
    
    
    

    Screenshot:

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 27, 2017 6:50 AM
  • User2033107836 posted

    Hello Zxj

    If I Pass Date 03-Dec-2017 and 10-Dec-2017 data is showing like this below 

    Date Descriptions Stock_Add Stock_Subtract Balance
    03-Dec-17 Issued 0 1 -1
    04-Dec-17 Arrival 5 0 4
    06-Dec-17 Arrival 10 0 14
    07-Dec-17 Issued 0 6 8

    but i want data to show like this 

    Date Descriptions Stock_Add Stock_Subtract Balance
    03-Dec-17 Opening 0 0 12
    03-Dec-17 Issued 0 1 11
    04-Dec-17 Arrival 5 0 16
    06-Dec-17 Arrival 10 0 26
    07-Dec-17 Issued 0 6 20

    How To do so.. In simple sql query

    Friday, December 29, 2017 12:36 PM
  • User347430248 posted

    Hi asp.ambur,<sub></sub><sup></sup>

    I try to check your last reply.

    in which you had posted that currently you get this kind of result but now you want to a different result with a simple query.

    I check the both table but I did not find any difference in table structure.

    I can see that data are different in table.

    so if I misunderstand anything then kindly correct me and provide detailed information.

    we will again try to provide you suggestions to solve the issue.

    Regards

    Deepak

    Tuesday, January 2, 2018 6:09 AM