By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) Apr 11 2022 16:24:07 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
CREATE TABLE test (
dt datetime
);
INSERT INTO test
VALUES
('2022-10-01 11:00:00'),
('2022-10-02 11:00:00'),
('2022-10-03 11:00:00'),
('2022-10-04 11:00:00'),
('2022-10-05 11:00:00'),
('2022-10-06 11:00:00'),
('2022-10-07 11:00:00'),
('2022-10-08 11:00:00'),
('2022-10-09 11:00:00'),
('2022-10-11 11:00:00'),
('2022-10-11 11:00:01'),
('2022-10-12 11:00:00'),
('2022-10-13 11:01:00')
;
13 rows affected
SELECT
dt,
DATEADD(MILLISECOND, pctcont_90_dt % 1000, DATEADD(SECOND, pctcont_90_dt / 1000, '19700101')) AS pctcont_90_dt,
pctdisc_90_dt
FROM (
SELECT
dt,
CAST(
percentile_cont(.90)
WITHIN GROUP (ORDER BY (datediff_big(MS, '1970-01-01', dt)))
OVER()
AS BIGINT
)
AS pctcont_90_dt,
percentile_disc(.90)
WITHIN GROUP (ORDER BY dt) over()
AS pctdisc_90_dt
FROM test
) t
;
| dt | pctcont_90_dt | pctdisc_90_dt |
|---|---|---|
| 2022-10-01 11:00:00.000 | 2022-10-12 06:12:00.200 | 2022-10-12 11:00:00.000 |
| 2022-10-02 11:00:00.000 | 2022-10-12 06:12:00.200 | 2022-10-12 11:00:00.000 |
| 2022-10-03 11:00:00.000 | 2022-10-12 06:12:00.200 | 2022-10-12 11:00:00.000 |
| 2022-10-04 11:00:00.000 | 2022-10-12 06:12:00.200 | 2022-10-12 11:00:00.000 |
| 2022-10-05 11:00:00.000 | 2022-10-12 06:12:00.200 | 2022-10-12 11:00:00.000 |
| 2022-10-06 11:00:00.000 | 2022-10-12 06:12:00.200 | 2022-10-12 11:00:00.000 |
| 2022-10-07 11:00:00.000 | 2022-10-12 06:12:00.200 | 2022-10-12 11:00:00.000 |
| 2022-10-08 11:00:00.000 | 2022-10-12 06:12:00.200 | 2022-10-12 11:00:00.000 |
| 2022-10-09 11:00:00.000 | 2022-10-12 06:12:00.200 | 2022-10-12 11:00:00.000 |
| 2022-10-11 11:00:00.000 | 2022-10-12 06:12:00.200 | 2022-10-12 11:00:00.000 |
| 2022-10-11 11:00:01.000 | 2022-10-12 06:12:00.200 | 2022-10-12 11:00:00.000 |
| 2022-10-12 11:00:00.000 | 2022-10-12 06:12:00.200 | 2022-10-12 11:00:00.000 |
| 2022-10-13 11:01:00.000 | 2022-10-12 06:12:00.200 | 2022-10-12 11:00:00.000 |