# Get Sales % Using Purchase Table

• ### 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

• User-719153870 posted

Hi Gopi.MCA,

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

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 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.

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 Thursday, October 7, 2021 12:00 AM
Wednesday, June 19, 2019 11:25 AM