By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable (
MemberID int,
MemberName varchar(20),
ReportingQuarter date,
FinalExpectedDay date
);
insert into mytable values
(1, 'A', '2022-12-31', '2023-03-21'),
(1, 'A', '2022-12-31', '2023-03-22'),
(1, 'A', '2022-12-31', '2023-03-23'),
(1, 'A', '2022-12-31', '2023-03-24'),
(1, 'A', '2022-12-31', '2023-03-25'),
(2, 'B', '2022-12-31', '2023-03-21'),
(2, 'B', '2022-12-31', '2023-03-22'),
(2, 'B', '2022-12-31', '2023-03-23'),
(2, 'B', '2022-12-31', '2023-03-24'),
(2, 'B', '2022-12-31', '2023-03-25');
10 rows affected
select *, CAST(rn as float)/CAST(count as float)*100 as Percentile
from (
select MemberID, MemberName, ReportingQuarter, FinalExpectedDay,
ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY FinalExpectedDay) as rn,
count(*) over (partition by MemberID) as count
from mytable
) as s
| MemberID | MemberName | ReportingQuarter | FinalExpectedDay | rn | count | Percentile |
|---|---|---|---|---|---|---|
| 1 | A | 2022-12-31 | 2023-03-21 | 1 | 5 | 20 |
| 1 | A | 2022-12-31 | 2023-03-22 | 2 | 5 | 40 |
| 1 | A | 2022-12-31 | 2023-03-23 | 3 | 5 | 60 |
| 1 | A | 2022-12-31 | 2023-03-24 | 4 | 5 | 80 |
| 1 | A | 2022-12-31 | 2023-03-25 | 5 | 5 | 100 |
| 2 | B | 2022-12-31 | 2023-03-21 | 1 | 5 | 20 |
| 2 | B | 2022-12-31 | 2023-03-22 | 2 | 5 | 40 |
| 2 | B | 2022-12-31 | 2023-03-23 | 3 | 5 | 60 |
| 2 | B | 2022-12-31 | 2023-03-24 | 4 | 5 | 80 |
| 2 | B | 2022-12-31 | 2023-03-25 | 5 | 5 | 100 |
select *, CAST(rn as float)/CAST(count as float)*100 as Percentile
from (
select MemberID, MemberName, ReportingQuarter, FinalExpectedDay,
ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY FinalExpectedDay) as rn,
count(*) over (partition by MemberID) as count
from mytable
) as s
where CAST(rn as float)/CAST(count as float)*100 = 60
| MemberID | MemberName | ReportingQuarter | FinalExpectedDay | rn | count | Percentile |
|---|---|---|---|---|---|---|
| 1 | A | 2022-12-31 | 2023-03-23 | 3 | 5 | 60 |
| 2 | B | 2022-12-31 | 2023-03-23 | 3 | 5 | 60 |