Alternative for lead function in sql server

Question

Hello All,

I have a table where I am having month and sales.

I have a requirement where I need to calculate difference of my previous month sales and current month sales like below.

I am ssms 2014 and I dont have lead function there.

Could any one please help how to do that without lead functions..

Answer 1

The LEAD function was introduced in SQL 2012, so you should have access to it.

Can you share the output of "SELECT @@version", and also a query where you try to use the LEAD function and the error message you get when you try to run it?

Answer 2

Hi @Learner

You can use a table join as an alternative.

Try this query.

create table test(Month datetime,Sales int);
insert into test values
('5/1/2023',900),('4/1/2023',600),('3/1/2023',400),('2/1/2023',500);
select * from test;

;with CTE as(
  select A.Month as AMonth,A.Sales as ASales,B.Month as BMonth,B.Sales as BSales
  from test as A left outer join test as B on dateadd(month,-1,A.Month) = B.Month)
select AMonth as Month,ASales as Sales,
       case when BSales is null then 0 else ASales - BSales end as Diff from CTE;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I am ssms 2014

SSMS is just a tool, the version doesn't matter in any way.

If you really have for every month a figure, then you last month sales with a simple sub-select:

SELECT T.*,
      (SELECT Sales FROM yourTable AS SUB WHERE SUB.Month = DATADD(month, -1, T.Month) AS LastMonth,
      T.Sales - (SELECT Sales FROM yourTable AS SUB WHERE SUB.Month = DATADD(month, -1, T.Month) AS Diff
FROM yourTable AS T