How to calculate derived variables in absolute and percentage terms in ssms18?

Question

Can anybody help please, i need conduct calculation the new variables in ssms
dbo.pricecomp

I have such data in sql
competitor_id comp_article_id MDM_Key subgroup_id month_id comp_disc_price iek_disc_price
7 FAF7FFB9-7E0D-DE5C-627E-49E70619224A 43476 05.04.2001 202210 597,27 454,17
7 7CBED177-416E-3AC2-0D0A-BA618780D76C43476 05.04.2001 202206 863,2 779,93
7 9BD3E269-5595-46C2-4734-9814ECC94EE2 43476 05.04.2001 202208 733,74 700,09
7 71414007-3E0C-3973-F06C-E724485888D2 43476 05.04.2001 202211 924,65 713,02
7 71414007-3E0C-3973-F06C-E724485888D2 43830 05.04.2001 202212 924,65 793,13
7 3449B644-5204-8182-45D2-66BEFB482EB4 43830 05.04.2001 202207 744,11 672,34
7 8E791A9A-C801-FEE9-B191-7B5DB4FCEBF7 43830 05.04.2001 202206 355,52 270,26
7 8E791A9A-C801-FEE9-B191-7B5DB4FCEBF7 43830 05.04.2001 202210 319,97 256,75
7 C5F9BE32-CE90-C4C7-9381-B45AEB520EDB 43830 05.04.2001 202206 605,89 460,73
7 C5F9BE32-CE90-C4C7-9381-B45AEB520EDB 43830 05.04.2001 202209 605,89 469,94
1 C5F9BE32-CE90-C4C7-9381-B45AEB520EDB 43938 05.04.2001 202212 605,89 519,71
1 6504BA81-4FB9-B8C6-8968-40BE01E0A481 43938 05.04.2001 202209 886,34 862,59
1 6504BA81-4FB9-B8C6-8968-40BE01E0A481 43938 05.04.2001 202212 886,34 760,26
1 3E9C5934-3A09-C7E3-F3C0-FB5C2EFE2777 43938 05.04.2001 202209 1095,74 1066,38
1 3E9C5934-3A09-C7E3-F3C0-FB5C2EFE2777 43938 05.04.2001 202211 1095,74 844,95
1 59767F0F-5951-79BD-5C36-3EBF3719EA7A 5411 05.04.2001 202205 907,3 819,78
1 59767F0F-5951-79BD-5C36-3EBF3719EA7A 5411 05.04.2001 202208 907,3 819,78
1 EA58A371-A22D-942F-4AD7-81FC425A14E75411 05.04.2001 202208 430,59 363,4
1 01B129DB-CF35-9731-26C5-F3AFC31B5F18 5411 05.04.2001 202208 1041,95 994,15
1 858FDDEE-54D3-F03B-910E-8F85528F0B01 5411 05.04.2001 202210 1278,98 1220,31

I need calculate the difference between the new and old month by each group competitor_id + MDM_Key separately for comp_disc_price iek_disc_price variables.
Take for example competitor_id=7 and MDM_Key=43476.
Here the data is not ranked(in sql), I did the ranking in Excel, but in this short exampe only 4 months from 202206 to 202211 are avilable.
From the month later, we subtract the month earlier, i.e. 202208-202206, further 202210-202208, then from 202211-202210.
For example like this in screen.
And we get the calculated values in the columns comp abs and iek abs, respectively.

After it, the percentage for these columns comp_disc_price iek_disc_price is calculated, thus obtaining new variables perc_comp and perc_iek respectively

percentage is calculated according to the principle. Divide the resulting difference between a couple of months by the previous one, for example '-129.46/863.2=-0.14998

similarly, I need to make such calculations for all groups of competitor_id + MDM_Key. In this example, there are only 4 groups
competitor_id=7+MDM_Key=43476
competitor_id=7+MDM_Key=43830
competitor_id=1+MDM_Key=43938
competitor_id=1+MDM_Key=5411
In fact, there are hundreds of such combinations.

sometimes (but not always)there is a double of the month, for example, here 2 times 202206, as in this group, in such cases I calculate average the values
competitor_id=7+MDM_Key=43830

Now I'm doing it using excel, but there is too much data, and excel does not fit more than 1 million data, so I need help via SQL (but I'm still learning)
How to create such a query in sql for which calculations?
Thanks for any valuable help.

Answer 1

Hi @warlock

I used a table join to do it, you can give it a try.

With CTE as(  
  select competitor_id,MDM_key,month_id,  
         avg(comp_disc_price) as cdp,avg(iek_disc_price) as idp,  
		 row_number()over(partition by competitor_id,MDM_key order by month_id) as num  
  from dbo.pricecomp  
  group by competitor_id,MDM_key,month_id)  
select A.competitor_id,A.MDM_key,A.month_id,A.cdp as comp_disc_price,A.idp as iek_disc_price,  
       case when B.cdp is null then null  
       else A.cdp - B.cdp end as comp_abs,  
	   case when B.idp is null then null  
       else A.idp - B.idp end as iek_abs,  
	   case when B.cdp is null then null  
       else (A.cdp - B.cdp)/B.cdp end as perc_comp,  
	   case when B.idp is null then null  
       else (A.idp - B.idp)/B.idp end as perc_iek  
from CTE as A left outer join CTE as B on A.competitor_id = B.competitor_id and   
    A.MDM_key = B.MDM_key and A.num - 1 = B.num;  

Also, I need to say that you have to thank ErlandSommarskog for helping you type out the table creation statement.

Best regards,
Percy Tang

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. 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 2

A few things first. You don't calculate anything in SSMS. SSMS is only a tool that allows you to talk to SQL Server which does the job. You could use some other tool for the same task, for instance Azure Data Studio.

Next, for this type of question, we recommend that you post CREATE TABLE statements for your table and INSERT statements with the sample data. This makes it easier for us who answers question. This time I have done this for you, but the next time I may not feel like it.

Here is a solution, including CREATE TABLE + INSERT:

   CREATE TABLE #warlock (  
      competitor_id    int,  
      comp_article_id  uniqueidentifier,  
      MDM_key          int,  
      subgroup_id      varchar(10),  
      month_id         int,  
      comp_disc_price  decimal(10,2),  
      iek_disc_price   decimal(10,2)  
   )  
     
   INSERT  #warlock(competitor_id, comp_article_id, MDM_key, subgroup_id, month_id, comp_disc_price, iek_disc_price)  
   VALUES (7, 'FAF7FFB9-7E0D-DE5C-627E-49E70619224A', 43476, '05.04.2001', 202210, 597.27 , 454.17 ),  
          (7, '7CBED177-416E-3AC2-0D0A-BA618780D76C', 43476, '05.04.2001', 202206, 863.2  , 779.93 ),  
          (7, '9BD3E269-5595-46C2-4734-9814ECC94EE2', 43476, '05.04.2001', 202208, 733.74 , 700.09 ),  
          (7, '71414007-3E0C-3973-F06C-E724485888D2', 43476, '05.04.2001', 202211, 924.65 , 713.02 ),  
          (7, '71414007-3E0C-3973-F06C-E724485888D2', 43830, '05.04.2001', 202212, 924.65 , 793.13 ),  
          (7, '3449B644-5204-8182-45D2-66BEFB482EB4', 43830, '05.04.2001', 202207, 744.11 , 672.34 ),  
          (7, '8E791A9A-C801-FEE9-B191-7B5DB4FCEBF7', 43830, '05.04.2001', 202206, 355.52 , 270.26 ),  
          (7, '8E791A9A-C801-FEE9-B191-7B5DB4FCEBF7', 43830, '05.04.2001', 202210, 319.97 , 256.75 ),  
          (7, 'C5F9BE32-CE90-C4C7-9381-B45AEB520EDB', 43830, '05.04.2001', 202206, 605.89 , 460.73 ),  
          (7, 'C5F9BE32-CE90-C4C7-9381-B45AEB520EDB', 43830, '05.04.2001', 202209, 605.89 , 469.94 ),  
          (1, 'C5F9BE32-CE90-C4C7-9381-B45AEB520EDB', 43938, '05.04.2001', 202212, 605.89 , 519.71 ),  
          (1, '6504BA81-4FB9-B8C6-8968-40BE01E0A481', 43938, '05.04.2001', 202209, 886.34 , 862.59 ),  
          (1, '6504BA81-4FB9-B8C6-8968-40BE01E0A481', 43938, '05.04.2001', 202212, 886.34 , 760.26 ),  
          (1, '3E9C5934-3A09-C7E3-F3C0-FB5C2EFE2777', 43938, '05.04.2001', 202209, 1095.74, 1066.38),  
          (1, '3E9C5934-3A09-C7E3-F3C0-FB5C2EFE2777', 43938, '05.04.2001', 202211, 1095.74, 844.95 ),  
          (1, '59767F0F-5951-79BD-5C36-3EBF3719EA7A', 5411 , '05.04.2001', 202205, 907.3  , 819.78 ),  
          (1, '59767F0F-5951-79BD-5C36-3EBF3719EA7A', 5411 , '05.04.2001', 202208, 907.3  , 819.78 ),  
          (1, 'EA58A371-A22D-942F-4AD7-81FC425A14E7', 5411 , '05.04.2001', 202208, 430.59 , 363.4  ),  
          (1, '01B129DB-CF35-9731-26C5-F3AFC31B5F18', 5411 , '05.04.2001', 202208, 1041.95, 994.15 ),  
          (1, '858FDDEE-54D3-F03B-910E-8F85528F0B01', 5411 , '05.04.2001', 202210, 1278.98, 1220.31)  
     
   SELECT * FROM #warlock ORDER BY competitor_id, MDM_key, month_id  
   go  
   ; WITH avgs AS (  
      SELECT  competitor_id, MDM_key, month_id,  
              AVG(comp_disc_price) AS comp_disc_price, AVG(iek_disc_price) AS iek_disc_price  
      FROM    #warlock  
      GROUP   BY competitor_id, MDM_key, month_id  
   )  
   SELECT competitor_id, MDM_key, month_id, comp_disc_price, iek_disc_price,  
          comp_disc_price - LAG(comp_disc_price) OVER(PARTITION BY competitor_id, MDM_key ORDER BY month_id) AS diff_comp_disc_price,  
          iek_disc_price - LAG(comp_disc_price) OVER(PARTITION BY competitor_id, MDM_key ORDER BY month_id) AS diff_iek_disc_price  
   FROM   avgs  
   ORDER BY competitor_id, MDM_key, month_id  
   go  
   DROP TABLE #warlock  

The WITH thing is a common table expression (CTE) computes the monthly averages. With CTEs we can build more complex queries from simpler ones. In the final query, I use LAG to get the value from the previous row.