I have a table with MemberID, MemberName, ReportingQuarter, FinalExpectedDay. I need to find the earliest FinalExpectedDay where the Member will have completed 3/5 (60%), 4/5 (80%), 5/5 (90%).
Here is my data:
MemberID, MemberName, ReportingQuarter, FinalExpectedDay
1, A, 12/31/2022, 3/21/2023
1, A, 12/31/2022, 3/22/2023
1, A, 12/31/2022, 3/23/2023
1, A, 12/31/2022, 3/24/2023
1, A, 12/31/2022, 3/25/2023
2, B, 12/31/2022, 3/21/2023
2, B, 12/31/2022, 3/22/2023
2, B, 12/31/2022, 3/23/2023
2, B, 12/31/2022, 3/24/2023
2, B, 12/31/2022, 3/25/2023
My query so far:
SELECT MemberID, FinalExpectedDay, 100 as 'Percentile'
FROM (
SELECT MemberID, FinalExpectedDay, Percentile, ROW_NUMBER () OVER (PARTITION BY MemberID ORDER BY Percentile asc) as PercentileRanking
FROM
(Select MemberID, FinalExpectedDay, PERCENT_RANK() OVER (PARTITION BY MemberID ORDER BY FinalExpectedDay ASC) as Percentile) PercentileRank
Where Percentile >=1)PercentileRanking = 1
)Y
Order by Y.MemberId, Percentile asc
PERCENT_RANK()
doesn't work because the first row will be 0 instead of .20 for 20%.
How do I get to this:
MemberID, MemberName, ReportingQuarter, Percentile, FinalExpectedDay
1, A, 12/31/2022, 60, 3/23/2023
2, B, 12/31/2022, 60, 3/23/2023
3条答案
按热度按时间m1m5dgzv1#
Should work for 60%
jdzmm42g2#
You can do it by divide the row number by count to get the
Percentile
:Demo here
ar5n3qh53#
I converted your example data into easily reproducible DDL/DML. This is really helpful when asking questions like this:
Here we're using a CTE to do the figuring out for us. We determine the total count per member, and the percent completed for each date.
Then we select from it, first for the sixty percent value, then join back to it for the eighty percent value and finally again for the ninty.
| MemberID | MemberName | SixtyExpectedDate | EightyExpectedDate | NintyExpectedDate |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | A | 2023-03-23 | 2023-03-24 | 2023-03-25 |
| 2 | B | 2023-03-23 | 2023-03-24 | 2023-03-25 |