CODEWITHSUNDEEP
sqlsql-serverfunction
ekrem tapan
ekrem tapan

Reputation: 147

How to divide two values from the different row

I have used this formula.

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

Then my data stored on SQL SERVER table look like below :

id        DATE         DATA

1          2015/01/01   10
2          2015/02/01   20
3          2015/03/01   30
4          2015/04/01   40
5          2015/05/01   50
6          2015/06/01   60
7          2015/07/01   70
8          2015/08/01   80
9          2015/09/01   90

How can i implement this formula on SQL Function ?

For Example 

current month is 2015/02/1
Quote change = (Current Month Data / Previous Month Data ) * 100
Quote change =( 15/10)*100

Then if current date is 2015/01/01. Because no data before 2015/01/01, I need to show 0 or #

Upvotes: 0

Views: 1957

Answers (2)

sam
sam

Reputation: 1304

You can use inner join like mentioned below - 

select a.*,isnull(cast(a.data/b.data as decimal(4,2))*100,0)
from TableA as a
inner join TableA as b
on b.date = dateadd(mm,-1,a.date)

Let me know if this helps

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82504

Sql server 2012 have a window function called LAG that is very useful in situations like this.
Lag returns the value of a specific column in the previous row (specified by the order by part of the over clause).

Try this:

;With cte as 
(
SELECT Id, Date, Data, LAG(Data) OVER(ORDER BY Date) As LastMonthData
FROM YourTable
)

SELECT Id, 
       Date, 
       Data, 
       CASE WHEN ISNULL(LastMonthData, 0) = 0 THEN 0 ELSE (Data/LastMonthData) * 100 END As Quote 
FROM cte

I've used a CTE just so I wouldn't have to repeat the LAG twice. The CASE expression is to prevent an exception in case the LastMonthData is 0 or null.

Upvotes: 3

Related Questions