-
answered
2022-12-26T03:05:41.89+00:00 PercyTang-MSFT 1,186 Reputation points Microsoft EmployeeHi @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.
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.