Loading

How to divide two values from the different row in SQL

How to divide two values from the different row in SQL

Ekrem Tapan

Ekrem Tapan

10y865SQL

hi everybody,

i have a this formula

Quote change = (current month / previous month) * 100

my data stored in sql table like that ;;

id --- date - data

1 2015/01/01 10

2 2015/02/01 15

3 2015/03/01 20

4 2015/04/01 15

.

.

.

.

10 2015/10/01 85

how can i implement this function on sql

for example current month is 2015/02/01

Quote change = (current month data / previous month data ) * 100

Quote change =( 15/10)*100

then if now is 2015/01/01. because no any data before 2015/01/01 this i need to show 0 or -

anybody can i help me ?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pramod Thakur10y ago
Accepted answer
--Create Table

CREATE TABLE [dbo].[myTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[myDate] [date] NOT NULL,
[data] [int] NOT NULL
) ON [PRIMARY]

-- Insert some records

insert into myTable values('2015/01/01',10),('2015/02/01',15),('2015/03/01',20),('2015/04/01',15)

--Create a user define function

Create Function dbo.MyFunction(@id int)
returns decimal
as
begin
declare @currentMonth int
declare @currentYear int
declare @returnValue float
declare @currentMonthData decimal
declare @previousMonthData decimal
set @currentMonth = (select MONTH(myDate) from myTable where id=@id)
set @currentYear = (select Year(myDate) from myTable where id=@id)
if @currentMonth=1
begin
set @returnValue=0
end
else
begin
set @currentMonthData = (select data from myTable where id=@id)
set @previousMonthData = (select data from myTable where month(myDate)=@currentMonth-1 and Year(myDate)=@currentYear)
set @returnValue = (@currentMonthData/@previousMonthData)*100
end
return cast(@returnValue as decimal(10,2))
end

-- Call that function

select *,dbo.MyFunction(id) as 'Quote change' from myTable

I hope it helpful. If in this reply you find your solution then please make it reply accepted.
0
Pramod Thakur10y ago
Hi,

Best way is, write a sql function for it and in that function write a logic if month is equal to 1 the return 0 else your own logic which you implement.

thnx,
Pramod
0