# Finding the month over month and year over month comparison metrics

Hi All, Hope you are doing well!... I am trying to compare the metrics such as purchase (number of purchases), purchase rate ,fulfillment rate to the previous month in comparison to the current month and also to he same month last year (one year before). Since purchase is a number the percentage increase or decrease(currentmonth month purchases -previous month purchases)/previous month purchases is calculated.. For the purchase rate , fulfilment rate since these are percentages it is calculated as (current month purchase rate -previous month purchase rate)...Please find the DDL for the input and output tables below..Can you please help me here..I want to do this for every single archive in the dataset.

Input
create table ##input
(archive date,
state varchar(10),
purchase int,
purchaserate float,
fulfilledrate float)
insert into ##input values
('2/1/2022','AK','1500','115','80'),
('2/1/2022','AL','20000','117','90'),
('1/1/2022','AK','1700','121','65'),
('1/1/2022','AL','25000','93','89'),
('2/1/2021','AK','2100','70','50'),
('2/1/2021','AL','32000','123','60')
Output
create table ##output
(archive date,
state varchar(10),
purchaseincreasedecrease-MOM float,
purchaseincreasedecrease-YOM float,
purchaserate -MOM float,
purchaserate -YOM float,
fulfilledrate-MOM float,
fulfilledrate-YOM float)
insert into ##output values
('2/1/2022','AK','-11.7','-28.6','-6','45','15','30'),
('2/1/2022','AL','-20','-37.5','24','-6','1','30')

``````SELECT C.archive, C.[state]
,(1- C.purchase * 1.0/M.purchase) * 100 AS purchaseincreasedecrease_MOM
,(1- C.purchase * 1.0/Y.purchase) * 100 AS purchaseincreasedecrease_YOM
,C.purchaserate - M.purchaserate AS purchaserate_MOM
,C.purchaserate - Y.purchaserate AS purchaserate_YOM
,C.fulfilledrate - M.fulfilledrate AS fulfilledrate_MOM
,C.fulfilledrate - Y.fulfilledrate AS fulfilledrate_MOM
from ##input C
JOIN ##input M
ON C.[state] = M.[state]
AND C.archive = DATEADD(month, 1, M.archive)
JOIN ##input Y
ON C.[state] = Y.[state]
AND C.archive = DATEADD(year, 1, Y.archive);``````