locked
Get Sales % Using Purchase Table RRS feed

  • Question

  • User-807418713 posted

    Hello

    This is My Purchase Table Data

    Date Product Stock_Qty
    1-Jan-2019 Item1 5
    4-Jan-3019 Item2 6
    7-Jan-2019 Item2 10
    8-Jan-2019 Item1 20
    8-Jan-2019 Item1 10
    22-Jan-2019 Item2 5
    3-Jan-2019 Item1 15
    2-Feb-2019 Item2 10

    This is My Sale Table Data

    Date Product Sale_Qty
    1-Jan-2019 Item1 2
    13-Jan-3019 Item2 4
    16-Jan-2019 Item1 8
    16-Jan-2019 Item1 4
    20-Jan-2019 Item2 6
    1-Feb-2019 Item1 3
    4-Feb-2019 Item2 6
    11-Feb-2019 Item1 8

    I Want Output Like This Each Mont Wise

    Month And Year Item1 Stock Qty Item1 Sale Qty Item 1 Sale % Item2 Stock Qty Item2 Sale Qty Item 2 Sale %
    Jan-19 35 14 40% 21 16-Jan-1900 76.10%
    Feb-19 21 11 52% 15 6-Jan-1900 40%

    Thank You

    Tuesday, June 11, 2019 2:22 AM

Answers

  • User-719153870 posted

    Hi Gopi.MCA,

    I'm sorry to have taken so long to reply back and forth.

    I had abandoned this post long time ago(Sorry again), but today I suddenly had a new idea about this case.

    Now I have found a solution.

    Notice: Your sales rate calculation is logically incorrect, but I will still give the answer based on your logic.

    According to your request: this month's stock = last month's stock + this month's purchase - this month's sales.

                                                Sales Rate for this month = Sales for this month / Inventory for this month.

    Please refer to the below SQL code:

    ---Create a new table[AAA] to store all data
    if exists (select * from sysobjects where id = object_id(N'[AAA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
    drop table [AAA]
    CREATE TABLE [AAA]( 
    [Month And Year] varchar(50),
    ) 
    ---Insert all [Month and Year] into AAA first
    insert into AAA([Month And Year]) select distinct left(CONVERT(varchar(50),Date),charindex('-',CONVERT(varchar(50),Date))+2) from Purchases
    ---Create a new table[Products] to store all different kinds of goods
    if exists (select * from sysobjects where id = object_id(N'[Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
    drop table [Products]
    CREATE TABLE [Products]( 
    [ProductName] varchar(50),
    ) 
    ---Insert data into [Products]
    insert into Products(ProductName) select a.Product from(select distinct Product from Purchases)a
    ---Create a new table[MAY] to store store all different kinds of Date
    if exists (select * from sysobjects where id = object_id(N'[MAY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
    drop table [MAY]
    CREATE TABLE [MAY]( 
    [MonthYear] varchar(50),
    ) 
    ---Insert data into [MAY]
    insert into MAY([MonthYear]) select distinct left(CONVERT(varchar(50),Date),charindex('-',CONVERT(varchar(50),Date))+2) from Purchases
    ---Use the While loop to modify the [AAA](add more columns Dynamically )
    declare @i int
    declare @Stock varchar(50)
    declare @Sale varchar(50)
    declare @Percent varchar(50)
    declare @S varchar(5000)
    declare @T varchar(5000)
    declare @TP varchar(5000)
    declare @ProductName varchar(50)
    set @i=1
    while @i<=(select COUNT(distinct Product) from Purchases)
    begin
    set @ProductName=(select top 1 ProductName from Products where ProductName not in (select top (@i-1) ProductName from Products))
    set @Stock=@ProductName+' Stock Qty'
    set @Sale=@ProductName+' Sale Qty'
    set @Percent=@ProductName+' Sale %'
    set @S='alter table AAA add ['+ltrim(@Stock)+'] int,['+LTRIM(@Sale)+'] int,['+LTRIM(@Percent)+'] varchar(50)'
    exec(@s)
    set @i=@i+1
    end
    ---Use the While loop again to insert data into[AAA]
    declare @ThisMonth varchar(50)
    declare @x int
    declare @j int
    declare @Stock1 varchar(50)
    declare @Sale1 varchar(50)
    declare @Percent1 varchar(50)
    declare @F varchar(2000)
    declare @FP varchar(2000)
    declare @ProductName1 varchar(50)
    declare @LastMonth varchar(50)
    set @j=1
    while @j<=(select COUNT(distinct left(CONVERT(varchar(50),Date),charindex('-',CONVERT(varchar(50),Date))+2) )from Purchases)
    begin
    set @x=1
    while @x<=(select COUNT(distinct Product) from Purchases)
    begin
    set @ThisMonth=(select top 1 MonthYear from MAY where MonthYear not in(select top (@j-1) MonthYear from MAY))
    set @LastMonth=LEFT(@ThisMonth,4)+'-'+right('0'+CONVERT(varchar(50),(convert(int,(select right(@ThisMonth,2)))-1)),2)
    set @ProductName1=(select top 1 ProductName from Products where ProductName not in (select top (@x-1) ProductName from Products))
    set @Stock1=@ProductName1+' Stock Qty'
    set @Sale1=@ProductName1+' Sale Qty'
    set @Percent1=@ProductName1+' Sale %'
    set @F=' update AAA set ['+@Stock1+']=(isnull((select SUM(Stock_Qty) from Purchases where Product='''+@ProductName1+''' and convert(varchar(50),Date) like '''+@ThisMonth+'%'+''' ),0)+isnull((select ['+@Stock1+'] from AAA where [Month And Year]='''+@LastMonth+'''),0)-isnull((select SUM(Sale_Qty) from Sales where Product='''+@ProductName1+''' and convert(varchar(50),Date) like '''+@ThisMonth+'%'+'''),0))
    ,['+@Sale1+']=(select SUM(Sale_Qty) from Sales where Product='''+@ProductName1+''' and convert(varchar(50),Date) like '''+@ThisMonth+'%'+''') where [Month And Year]='''+@ThisMonth+''' '
    set @FP='update AAA set ['+@Percent1+']=(convert(varchar(50),convert(decimal(18,2),(select ['+@Sale1+'] from AAA where [Month And Year]='''+@ThisMonth+''')*100/CONVERT(float,(select ['+@Stock1+'] from AAA where [Month And Year]='''+@ThisMonth+'''))))+''%'') where [Month And Year] ='''+@ThisMonth+''''
    exec(@F)
    exec(@FP)
    set @x=@x+1
    end
    set @j=@j+1
    end
    select * from AAA
    ---Delete all tables created
    drop table AAA
    drop table MAY
    drop table Products

    Data about goods and Date can now be added and searched dynamically.

    Here's result of my demo:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 19, 2019 11:25 AM

All replies

  • User-719153870 posted

    Hi  Gopi.MCA,

    According to your description, I suggest you can select and insert all these data into a new table with new columns names.
    I assumed your date column’s type is varchar.
     
    More details, you could refer to below sql query:

    if exists (select * from sysobjects where id = object_id(N'[AAA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
    drop table [AAA]
    CREATE TABLE [AAA]( 
    [Month And Year] varchar(50),
    [Item1 Stock Qty] int ,
    [Item1 Sale Qty] int,
    [Item1 Sale%] varchar(50),
    [Item2 Stock Qty] int ,
    [Item2 Sale Qty] int,
    [Item2 Sale%] varchar(50),
    ) 
     
     insert into AAA values('Jan-19',
    (select SUM(Stock_Qty)from Purchase where Product ='Item1' and RIGHT(Date,8)='Jan-2019'),
    (select SUM(Sale_Qty)from Sale where Product ='Item1' and RIGHT(Date,8)='Jan-2019'),
    (select convert(varchar(50),CONVERT(decimal(18,2),(select SUM(Sale_Qty)from Sale where Product ='Item1' and RIGHT(Date,8)='Jan-2019')*100/convert(float,(select SUM(Stock_Qty)from Purchase where Product ='Item1' and RIGHT(Date,8)='Jan-2019'))))+'%'),
    (select SUM(Stock_Qty)from Purchase where Product ='Item2' and RIGHT(Date,8)='Jan-2019'),
    (select SUM(Sale_Qty)from Sale where Product ='Item2' and RIGHT(Date,8)='Jan-2019'),
    (select convert(varchar(50),CONVERT(decimal(18,2),(select SUM(Sale_Qty)from Sale where Product ='Item2' and RIGHT(Date,8)='Jan-2019')*100/convert(float,(select SUM(Stock_Qty)from Purchase where Product ='Item2' and RIGHT(Date,8)='Jan-2019'))))+'%')),
    ('Feb-19',
    (select SUM(Stock_Qty)from Purchase where Product ='Item1' and RIGHT(Date,8)='Feb-2019'),
    (select SUM(Sale_Qty)from Sale where Product ='Item1' and RIGHT(Date,8)='Feb-2019'),
    (select convert(varchar(50),CONVERT(decimal(18,2),(select SUM(Sale_Qty)from Sale where Product ='Item1' and RIGHT(Date,8)='Feb-2019')*100/convert(float,(select SUM(Stock_Qty)from Purchase where Product ='Item1' and RIGHT(Date,8)='Feb-2019'))))+'%'),
    (select SUM(Stock_Qty)from Purchase where Product ='Item2' and RIGHT(Date,8)='Feb-2019'),
    (select SUM(Sale_Qty)from Sale where Product ='Item2' and RIGHT(Date,8)='Feb-2019'),
    (select convert(varchar(50),CONVERT(decimal(18,2),(select SUM(Sale_Qty)from Sale where Product ='Item2' and RIGHT(Date,8)='Feb-2019')*100/convert(float,(select SUM(Stock_Qty)from Purchase where Product ='Item2' and RIGHT(Date,8)='Feb-2019'))))+'%'))
    
      select * from AAA
      
      drop table [AAA]
    

    Here's the result of my demo:

    Best Regard,
    Yang Shen

    Tuesday, June 11, 2019 8:41 AM
  • User-807418713 posted

    Hi

    Item1 Stock Qty Should be 21 because last month is Jan-2019 35-14 = 21

    Item1 No Purchase in Feb-2019

    Tuesday, June 11, 2019 9:27 AM
  • User-807418713 posted

    Hello Yang Shen

    User Will Not Give Item1 And Item2 Becuase Table will have many items

    is that any short sql query to access dynamic

    Thank You

    Tuesday, June 11, 2019 11:59 AM
  • User-719153870 posted

    Hi Gopi.MCA,

    I'm sorry to have taken so long to reply back and forth.

    I had abandoned this post long time ago(Sorry again), but today I suddenly had a new idea about this case.

    Now I have found a solution.

    Notice: Your sales rate calculation is logically incorrect, but I will still give the answer based on your logic.

    According to your request: this month's stock = last month's stock + this month's purchase - this month's sales.

                                                Sales Rate for this month = Sales for this month / Inventory for this month.

    Please refer to the below SQL code:

    ---Create a new table[AAA] to store all data
    if exists (select * from sysobjects where id = object_id(N'[AAA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
    drop table [AAA]
    CREATE TABLE [AAA]( 
    [Month And Year] varchar(50),
    ) 
    ---Insert all [Month and Year] into AAA first
    insert into AAA([Month And Year]) select distinct left(CONVERT(varchar(50),Date),charindex('-',CONVERT(varchar(50),Date))+2) from Purchases
    ---Create a new table[Products] to store all different kinds of goods
    if exists (select * from sysobjects where id = object_id(N'[Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
    drop table [Products]
    CREATE TABLE [Products]( 
    [ProductName] varchar(50),
    ) 
    ---Insert data into [Products]
    insert into Products(ProductName) select a.Product from(select distinct Product from Purchases)a
    ---Create a new table[MAY] to store store all different kinds of Date
    if exists (select * from sysobjects where id = object_id(N'[MAY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
    drop table [MAY]
    CREATE TABLE [MAY]( 
    [MonthYear] varchar(50),
    ) 
    ---Insert data into [MAY]
    insert into MAY([MonthYear]) select distinct left(CONVERT(varchar(50),Date),charindex('-',CONVERT(varchar(50),Date))+2) from Purchases
    ---Use the While loop to modify the [AAA](add more columns Dynamically )
    declare @i int
    declare @Stock varchar(50)
    declare @Sale varchar(50)
    declare @Percent varchar(50)
    declare @S varchar(5000)
    declare @T varchar(5000)
    declare @TP varchar(5000)
    declare @ProductName varchar(50)
    set @i=1
    while @i<=(select COUNT(distinct Product) from Purchases)
    begin
    set @ProductName=(select top 1 ProductName from Products where ProductName not in (select top (@i-1) ProductName from Products))
    set @Stock=@ProductName+' Stock Qty'
    set @Sale=@ProductName+' Sale Qty'
    set @Percent=@ProductName+' Sale %'
    set @S='alter table AAA add ['+ltrim(@Stock)+'] int,['+LTRIM(@Sale)+'] int,['+LTRIM(@Percent)+'] varchar(50)'
    exec(@s)
    set @i=@i+1
    end
    ---Use the While loop again to insert data into[AAA]
    declare @ThisMonth varchar(50)
    declare @x int
    declare @j int
    declare @Stock1 varchar(50)
    declare @Sale1 varchar(50)
    declare @Percent1 varchar(50)
    declare @F varchar(2000)
    declare @FP varchar(2000)
    declare @ProductName1 varchar(50)
    declare @LastMonth varchar(50)
    set @j=1
    while @j<=(select COUNT(distinct left(CONVERT(varchar(50),Date),charindex('-',CONVERT(varchar(50),Date))+2) )from Purchases)
    begin
    set @x=1
    while @x<=(select COUNT(distinct Product) from Purchases)
    begin
    set @ThisMonth=(select top 1 MonthYear from MAY where MonthYear not in(select top (@j-1) MonthYear from MAY))
    set @LastMonth=LEFT(@ThisMonth,4)+'-'+right('0'+CONVERT(varchar(50),(convert(int,(select right(@ThisMonth,2)))-1)),2)
    set @ProductName1=(select top 1 ProductName from Products where ProductName not in (select top (@x-1) ProductName from Products))
    set @Stock1=@ProductName1+' Stock Qty'
    set @Sale1=@ProductName1+' Sale Qty'
    set @Percent1=@ProductName1+' Sale %'
    set @F=' update AAA set ['+@Stock1+']=(isnull((select SUM(Stock_Qty) from Purchases where Product='''+@ProductName1+''' and convert(varchar(50),Date) like '''+@ThisMonth+'%'+''' ),0)+isnull((select ['+@Stock1+'] from AAA where [Month And Year]='''+@LastMonth+'''),0)-isnull((select SUM(Sale_Qty) from Sales where Product='''+@ProductName1+''' and convert(varchar(50),Date) like '''+@ThisMonth+'%'+'''),0))
    ,['+@Sale1+']=(select SUM(Sale_Qty) from Sales where Product='''+@ProductName1+''' and convert(varchar(50),Date) like '''+@ThisMonth+'%'+''') where [Month And Year]='''+@ThisMonth+''' '
    set @FP='update AAA set ['+@Percent1+']=(convert(varchar(50),convert(decimal(18,2),(select ['+@Sale1+'] from AAA where [Month And Year]='''+@ThisMonth+''')*100/CONVERT(float,(select ['+@Stock1+'] from AAA where [Month And Year]='''+@ThisMonth+'''))))+''%'') where [Month And Year] ='''+@ThisMonth+''''
    exec(@F)
    exec(@FP)
    set @x=@x+1
    end
    set @j=@j+1
    end
    select * from AAA
    ---Delete all tables created
    drop table AAA
    drop table MAY
    drop table Products

    Data about goods and Date can now be added and searched dynamically.

    Here's result of my demo:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 19, 2019 11:25 AM