SQL Server How to get running percentages or earliest date

ldxq2e6h  于 2023-04-04  发布在  其他
关注(0)|答案(3)|浏览(123)

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
m1m5dgzv

m1m5dgzv1#

SELECT MemberID, MemberName, ReportingQuarter, MIN(FinalExpectedDay) AS EarliestCompletionDay
FROM (
    SELECT MemberID, MemberName, ReportingQuarter, FinalExpectedDay, 
        ROW_NUMBER() OVER (PARTITION BY MemberID, ReportingQuarter ORDER BY FinalExpectedDay) AS RowNum,
        COUNT(*) OVER (PARTITION BY MemberID, ReportingQuarter) AS TotalRows
    FROM YourTable
) t
WHERE RowNum >= CEILING(TotalRows * 0.6)
GROUP BY MemberID, MemberName, ReportingQuarter

Should work for 60%

jdzmm42g

jdzmm42g2#

You can do it by divide the row number by count to get the Percentile :

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

Demo here

ar5n3qh5

ar5n3qh53#

I converted your example data into easily reproducible DDL/DML. This is really helpful when asking questions like this:

DECLARE @table TABLE (MemberID INT, MemberName NVARCHAR(20), ReportingQuarter DATE, FinalExpectedDay DATE);
INSERT INTO @table (MemberID, MemberName, ReportingQuarter, FinalExpectedDay) VALUES
(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');
;WITH allData AS  (
        SELECT MemberID, MemberName, ReportingQuarter, FinalExpectedDay, 
               COUNT(*) OVER (PARTITION BY MemberID ORDER BY (SELECT 1)) AS Total, COUNT(*) OVER (PARTITION BY MemberID ORDER BY FinalExpectedDay) AS RunningTotal,
               (COUNT(*) OVER (PARTITION BY MemberID ORDER BY FinalExpectedDay)+.0) / (COUNT(*) OVER (PARTITION BY MemberID ORDER BY (SELECT 1))+.0) AS PctRunningTotal
          FROM @table
)

SELECT sixty.MemberID, sixty.MemberName, sixty.FinalExpectedDay AS SixtyExpectedDate, eighty.FinalExpectedDay AS EightyExpectedDate, ninty.FinalExpectedDay AS NintyExpectedDate
  FROM allData sixty
    INNER JOIN allData eighty
      ON sixty.MemberID = eighty.MemberID
      AND eighty.PctRunningTotal BETWEEN .8 AND .8999
    INNER JOIN allData ninty
      ON sixty.MemberID = ninty.MemberID
      AND ninty.PctRunningTotal >= .9
 WHERE sixty.PctRunningTotal BETWEEN .6 AND .7999

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 |

相关问题