Ways to find Percentage increase in sales Month over Month

Question

I want to find the percentage increase in Month over month sales amount using SQL Server. I want to find % MoM increase in sales by using self join and also using partition with rows unbounded preceding. I do not want to use lag(). Can anyone let me know about the the ways to generate this solution.

Here is my table.

create table growth_new(slno bigint,mon varchar(30),sales_amount bigint)

insert into growth_new values(1, 'Jan', 5000)
insert into growth_new values(2, 'Feb', 12000)
insert into growth_new values(3, 'Mar', 32000)
insert into growth_new values(4, 'Apr', 20000)

Slno Mon sales_amount
1    Jan    5000
2    Feb    12000
3    Mar    32000
4    Apr    20000

What have you tried? Where did you get stuck? And what the problem with using `lag`? — Dale K, May 24 '20 at 22:03
`LAG` (or `LEAD`) is exactly what you want here. It'll be much more performant that a self join. — Thom A, May 24 '20 at 22:04
Are you really storing dates as abbreviated month names? This makes things much more complicated here. — GMB, May 24 '20 at 22:09

score 1 · Accepted Answer · answered May 24 '20 at 22:14

You can use lag(). If slno orders the rows, then:

select gn.*,
       (gn.sales_amount * 1.0 / lag(gn.sales_amount) over (order by slno)) - 1 as increase
from growth_new gn;

A self-join doesn't really make sense for this problem. But if you really needed to with this data structure:

with gn as (
      select gn.*, convert(date, month + ' 2000') as mm
      from growth_new
     )
select gn.*,
       (gn.sales_amount * 1.0 / gnprev.sales_amount) - 1
from gn left join
     gn gnprev
     on gnprev.mm = dateadd(month, -1, gn.mm);

You should, however, really fix the data so the month is in a reasonable format.

score 1 · Answer 2 · answered May 24 '20 at 22:17

If you don't want to use the LEAD or LAG, you can use the following:

I assumed that you can compare using the ids, otherwise, you can have a table to store the months Ids

selecT g.*, growth = 100*cast(iif(p.sales_amount is null,0,(g.sales_amount-p.sales_amount)*1.0/p.sales_amount) as money)
from growth_new g
left join growth_new p on p.slno=g.slno-1

the output is:

slno    mon     sales_amount    growth
1        Jan    5000             0.00
2        Feb    12000            140.00
3        Mar    32000            166.67
4        Apr    20000            -37.50

Hope this helps you

VTi · Answer 3 · 2020-05-24T22:44:12.360

1

You could use the lag function really unless you want to try other alternatives. Also as mentioned above your month format is not ideal and not scalable at all.

WITH growth_new(slno ,mon ,sales_amount)
AS (SELECT 1, 'Jan', 5000  UNION 
    SELECT 2, 'Feb', 12000 UNION
    SELECT 3, 'Mar', 32000 UNION
    SELECT 4, 'Apr', 20000 
   )

 SELECT cur.*, prev.mon as prev_month, 
 ISNULL(prev.sales_amount,0) AS prev_month_sales_amount,
 [%MoM Change] = ((cur.sales_amount - 
 ISNULL(prev.sales_amount,0))/CAST(prev.sales_amount as float))*100
 FROM growth_new cur
 LEFT JOIN growth_new prev ON prev.slno = cur.slno - 1


 slno   mon sales_amount    prev_month  prev_month_sales_amount %MoM Change
  1     Jan   5000            NULL               0                 NULL
  2     Feb   12000           Jan             5000                 140
  3     Mar   32000           Feb             12000          166.666666666667
  4     Apr   20000           Mar             32000               -37.5

edited May 24 '20 at 22:44

answered May 24 '20 at 22:24

VTi

1,309
6
14

@DaleK - I have posted formatted code/query and in addition I have added image to show that I have run the code and the results it s giving. I am relatively new here, any reason we shouldn't use images ? – VTi May 24 '20 at 22:28
Just googled - One of the SO post says - Images are useful in a post, but make sure the post is still clear without them. If you post images of code or error messages, copy and paste or type the actual code or message into the post directly. – VTi May 24 '20 at 22:34
I will look to see how we can add data as formatted text. – VTi May 24 '20 at 22:35
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/214547/discussion-between-varuntiwari-and-dale-k). – VTi May 24 '20 at 22:44

Ways to find Percentage increase in sales Month over Month

3 Answers3