Using percentile or fractions in TSQL the easy way

A lot of tasks for reporting uses percentile or fractions for performance so you can state such as 25 % of the work was done in this time. It can be accomplished in a lot of ways but the easiest I have found is the following:

Percentile Table Variable


DECLARE @PercentileTable TABLE (Percentile FLOAT)

INSERT INTO @PercentileTable VALUES (25.0)
INSERT INTO @PercentileTable VALUES (50.0)
INSERT INTO @PercentileTable VALUES (75.0)
INSERT INTO @PercentileTable VALUES (95.0)

Using the percentile table


;WITH DataTable
AS
(
SELECT
ROW_NUMBER() OVER (Partition By PartitionType ORDER BY TimeUsed ASC) AS GroupNumber
, COUNT(ID) OVER (Partition By PartitionType) AS GroupMax
, PartitionType
, TimeUsed
FROM tblTable
)

SELECT *
, PercentileTable.Percentile * MemoryTable.GroupMax / 100.0 AS Fraction
, FLOOR(PercentileTable.Percentile * MemoryTable.GroupMax / 100.0) AS FractionRounded
FROM DataTable
CROSS APPLY @PercentileTable PercentileTable
WHERE DataTable.GroupNumber = FLOOR(PercentileTable.Percentile * DataTable.GroupMax / 100.0)

tblTable is the table containing the data that are the basis for the percentile or fraction.
The process has the great advantage that the value shown are a value that actually exists and not a weighted average.

Leave a Reply

Your email address will not be published. Required fields are marked *