How to divide two values from the different row in SQL

Question

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 ?

Pramod Thakur · Answer

--Create Table CREATE TABLE [dbo].[myTable]( [id] [int] IDENTITY(11) 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(102)) 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.

Pramod Thakur · Answer

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