Average movement of promise dates by order number

Transact-SQL
1

Hi All,

Hope you are doing well !..I am trying to understand the average movement in the promise dates from the current promise date (currentprmdate) with respect to the promise dates 7 days ago (prmdate7) , 14 days ago(prmdate14) and then 28 days (prmdate28) ago in terms of days at a plant, segment ,model level...Also I want to find the distinct number of ordernumber at this level of aggregation..

The calculation is average difference in days (currentprmdate -prmdate7)

average difference in days (currentprmdate -prmdate14)

average difference in days (currentprmdate-prmdate28)

over segment ,plant and model

Please find the DDL below:

Input table

create table input
(ordernumber varchar(40),
segment varchar(20),
plant varchar(10),
model varchar(15),
currentprmdate date,
prmdate7 date,
prmdate14 date,
prmdate28 date)

insert into input values
('V89121','vinots','Chikoo','HJ781','5/6/2021','5/5/2021','5/1/2021','5/7/2021'),
('LM12781','vinots ','Chikoo','HJ781','5/17/2021','5/11/2021','5/15/2021','5/10/2021'),
('JK9812','vinots','Chikoo','HJ781','5/3/2021','4/28/2021','4/25/2021','4/20/2021'),
('LP18921','Vimar','Jolie','MK241','4/3/2021','3/27/2021','3/21/2021','3/20/2021'),
('BN1231','Vimar','Jolie','MK241','6/10/2021','6/5/2021','6/3/2021','6/1/2021'),
('LO1231','Vimar','Jolie','MK241','7/15/2022','7/11/2022','7/13/2022','7/7/2022')

Output table

create table output
(segment varchar(20),
plant varchar(20),
model varchar(40),
avg7day float,
avg14day float,
avg28day float,
distinctorders int)

insert into output values

('vinots','Chikoo','HJ781','4','5','6.3','3'),
('Vimar','Jolie','MK241','5.3','7.3','10.3','3')

2 
select segment, plant, model
	,Cast(avg(abs(Cast(Datediff(day, currentprmdate, prmdate7) as decimal(10,2)))) as decimal(10,1)) as Avg7Day
	,Cast(avg(abs(Cast(Datediff(day, currentprmdate, prmdate14) as decimal(10,2)))) as decimal(10,1)) as Avg14Day
	,Cast(avg(abs(Cast(Datediff(day, currentprmdate, prmdate28) as decimal(10,2)))) as decimal(10,1)) as Avg28Day
	,count(Distinct ordernumber) as distinctorders
 from #input
group by segment, plant, model
3

Thank you so much @mike01 !..