承诺日期的平均移动按订单编号

发布于 2025-02-09 01:29:54 字数 3006 浏览 2 评论 0原文

我试图了解承诺日期的平均运动日期（currentprmdate）关于7天前的承诺日期（PRMDATE7），14天前（PRMDATE14）（PRMDATE14），然后是28天（PRMDATE28）（prmdate28）以前在工厂，段，模型水平上的天数...我也想在此汇总水平上找到订购数的不同数量。

计算是天数的平均差异（CurrentPrmdate -prmdate7）

天数的平均差异

平均差异（CurrentPrmdate-prmdate28）

植物和模型

order

order> order	细分		表	输入	>	order	，
几天的	>	与	>		>	order> order 2021	5/7/2021
LM12781	VINOTS	CHIKOO	HJ781	5/3/3/2021	4/28/2021	4/28/2021 4/28/2021 4/25/2021	5/17/2021 5/11/2021 5/15/2021 5/10/10/2021
JK9812	VINOTS CHIKOO	CHIKOO	HJ781	4/25/2021	4/25/2021 4/25/2021	4/25/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/5/2021 6/3/2021	6/2021 6/2021	6/1 6/1 6/1 /2021
LO1231	VIMAR	JOLIE	MK241	7/15/2022	7/11/2022	7/13/2022	7/7/2022

输出表

段	植物	平均	7天平均平均	14天平均14天	平均28天28天28	天订单
Vinots	Chikoo	HJ781	4	5	6.3	3
Vimar	Jolie	MK241	5.3 7.3	7.3	10.3	3

请在下面找到DDL：

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')

原文

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

Input table

ordernumber	segment	plant	model	currentprmdate	prmdate7	prmdate14	prmdate28
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

segment	plant	model	Average 7 day	Average 14 day	Average 28 day	distinct number of orders
vinots	Chikoo	HJ781	4	5	6.3	3
Vimar	Jolie	MK241	5.3	7.3	10.3	3

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')

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

陌上芳菲 2025-02-16 01:29:54

 ;WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY ordernumber ORDER BY orderdate) AS Rn
 FROM #input i WHERE i.code1 IN ('SLD','SPP','SOB') OR i.code2 IN ('SLD','SPP','SOB'))
    
 SELECT * FROM cte WHERE Rn = 1

 ;WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY ordernumber ORDER BY orderdate) AS Rn
 FROM #input i WHERE i.code1 IN ('SLD','SPP','SOB') OR i.code2 IN ('SLD','SPP','SOB'))
    
 SELECT * FROM cte WHERE Rn = 1

回复收藏 0 原文

~没有更多了~