使用日期差异的第90个百分位

Question

我一直在使用以下

DATEDIFF(dd,OrderReceivedDate, DeliveryDate) DaysToDelivery

计算OrderReceivedDate和DeliveryDate之间的天数

我想计算出每个部门的第90个百分点。

我有以下

PERCENTILE_DISC(0.9) 
WITHIN GROUP (ORDER BY DATEDIFF(dd,OrderReceivedDate, DeliveryDate))   
OVER (PARTITION BY Department) as 90thpercentile

我无法让这个工作

当尝试使用PERCENTILE_DISC时，是否不允许日期差异？还是必须使用存储的数字？

我不断收到错误-OrderReceivedDate或DeliveryDate不在子句的聚合或分组中

请帮助

Answer 1

datediff支持PERCENTIAL_ISC，我已经在2016 sql server中测试了您的查询，它运行良好

SELECT PERCENTILE_DISC(0.9) 
WITHIN GROUP (ORDER BY DATEDIFF(dd,OrderReceivedDate, DeliveryDate))
OVER (PARTITION BY Department) as "90thpercentile"
from t

我认为列别名有问题，请使用双引号或[]表示列名称中的数字开头

Answer 2

示例代码。。你的预期结果集是什么？？？

--drop table #Temp_Orders
create table #Temp_Orders
(
OrderNo int null
,Department varchar(30) null
,OrderReceivedDate date null
,DeliveryDate date null
)
insert into #Temp_Orders values
(1,'Acc','12-1-2018','12-2-2018')
,(1,'Acc','12-5-2018','12-8-2018')
,(1,'Warehouse','12-1-2018','12-12-2018')
,(1,'Warehouse','12-10-2018','12-12-2018')
,(1,'Acc','11-1-2018','11-29-2018')
,(1,'Acc','10-1-2018','10-9-2018')

select *
,PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY DATEDIFF(dd,OrderReceivedDate, DeliveryDate))
OVER (PARTITION BY Department) as [90thpercentile]
from #Temp_Orders