Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server inter-percentile range function


IPR

Updated: 28 February 2011


Use the aggregate function IPR to calculate the difference in value between two percentiles in a dataset. 
Syntax
XLeratorDB syntax for IPR function for SQL Server
Arguments
@x
the input values for the function. @ x is an expression of type float or of a type that can be implicitly converted to float.
@kl
is the lower percentile value in the range 0 through 1, inclusive. @kl is an expression of type float or of a type that can be implicitly converted to float.
@ku
is the upper percentile value in the range 0 through 1, inclusive. @ku is an expression of type float or of a type that can be implicitly converted to float.
@mthd
the percentile calculation method. Use ‘INC’ for the inclusive method and ‘EXC’ for the exclusive method. If you enter NULL, @mthd defaults to NULL. For more information about the two methods, see the PERCENTILE and the PERCENTILE_EXC documentation.
Return Types
float
Remarks
·         If @ku <0 or @ku > 1 one, IPR returns an error.
·         If @kl <0 or @kl > 1 one, IPR returns an error.
·         If @mthd not NULL, ‘INC’, or ‘EXC’, IPR returns an error.
·         @kl must remain invariant for a group.
·         @ku must remain invariant for a group.
·         @mthd must remain invariant for a group.
·         IPR is an aggregate function and follows the same conventions as all other aggregate functions in SQL Server.
·         If there is only one value in the dataset, that value will be returned by the function.
·         Available in XLeratorDB / statistics 2008 only
Examples
In this example, we calculate the range from the 10th to the 90th percentile for selected salary inforrmation collected from 10 cities.
SELECT wct.IPR(salary, .10, .90, NULL) as [Inter-Percentile Range]
FROM (VALUES
      ('New York','429-00-6486',236503),
      ('New York','90-70-2526',224472),
      ('New York','87-85-0404',139802),
      ('New York','716-89-3089',185287),
      ('New York','159-78-5370',211900),
      ('New York','195-97-6820',186703),
      ('New York','95-49-2813',167451),
      ('New York','37-20-7422',149462),
      ('New York','44-48-0076',214708),
      ('New York','514-79-0041',226485),
      ('Los Angeles','526-34-4521',196402),
      ('Los Angeles','800-50-0868',205359),
      ('Los Angeles','41-34-3618',195679),
      ('Los Angeles','854-29-9398',131925),
      ('Los Angeles','673-30-3623',171091),
      ('Los Angeles','537-58-8889',110217),
      ('Los Angeles','808-68-4234',192836),
      ('Los Angeles','359-81-6735',209346),
      ('Los Angeles','731-80-2303',182186),
      ('Los Angeles','214-58-0842',125355),
      ('Chicago','456-79-9682',183698),
      ('Chicago','807-97-4784',194282),
      ('Chicago','981-16-3724',156083),
      ('Chicago','252-34-3054',226619),
      ('Chicago','613-28-9452',153366),
      ('Chicago','785-25-8628',205709),
      ('Chicago','451-26-7350',206085),
      ('Chicago','443-94-2401',120587),
      ('Chicago','696-26-8113',171185),
      ('Chicago','277-31-9760',211160),
      ('Dallas','537-88-7532',245231),
      ('Dallas','393-25-3503',238733),
      ('Dallas','612-17-0712',103152),
      ('Dallas','384-93-7285',228842),
      ('Dallas','745-10-7587',154749),
      ('Dallas','950-20-4045',102156),
      ('Dallas','477-48-7550',196533),
      ('Dallas','427-52-8597',238970),
      ('Dallas','891-19-0810',245204),
      ('Dallas','564-79-7612',196946),
      ('Boston','216-84-7134',128035),
      ('Boston','515-84-4073',249093),
      ('Boston','92-43-5775',205026),
      ('Boston','144-08-1092',198120),
      ('Boston','469-14-5012',174143),
      ('Boston','379-92-8313',166215),
      ('Boston','71-22-5132',105058),
      ('Boston','257-39-0324',107247),
      ('Boston','611-57-4279',118561),
      ('Boston','956-53-2865',232789),
      ('Denver','711-81-0072',240720),
      ('Denver','673-39-5028',159706),
      ('Denver','554-33-3980',232493),
      ('Denver','770-03-5304',203310),
      ('Denver','732-47-5077',123106),
      ('Denver','573-18-3567',149999),
      ('Denver','93-92-0334',162657),
      ('Denver','424-76-1468',116322),
      ('Denver','403-47-0063',246058),
      ('Denver','460-58-1833',198043),
      ('Miami','221-04-4153',130962),
      ('Miami','179-09-9839',227246),
      ('Miami','564-76-9437',144027),
      ('Miami','407-48-4081',138549),
      ('Miami','526-79-1840',208006),
      ('Miami','72-68-4977',170109),
      ('Miami','235-72-3903',191669),
      ('Miami','436-62-0474',161164),
      ('Miami','430-52-3914',162507),
      ('Miami','459-27-5541',238972),
      ('Phoenix','576-38-4531',238281),
      ('Phoenix','65-64-1278',197678),
      ('Phoenix','880-29-1997',159183),
      ('Phoenix','304-72-1881',194733),
      ('Phoenix','61-20-1046',221045),
      ('Phoenix','64-95-5514',105577),
      ('Phoenix','262-63-4021',186399),
      ('Phoenix','661-84-1023',234974),
      ('Phoenix','892-31-4821',115076),
      ('Phoenix','319-91-9463',239548),
      ('San Franciso','136-67-6873',148829),
      ('San Franciso','5-41-7374',114161),
      ('San Franciso','381-26-8852',232509),
      ('San Franciso','620-64-6243',112686),
      ('San Franciso','128-33-5550',208679),
      ('San Franciso','422-00-0156',107685),
      ('San Franciso','370-98-5607',133224),
      ('San Franciso','91-58-9543',218955),
      ('San Franciso','911-35-0448',187826),
      ('San Franciso','734-65-1268',223683),
      ('Atlanta','334-97-0585',240384),
      ('Atlanta','405-12-4222',124350),
      ('Atlanta','43-05-7567',233836),
      ('Atlanta','882-97-7996',134091),
      ('Atlanta','368-91-4292',173787),
      ('Atlanta','408-04-5921',140769),
      ('Atlanta','232-13-5280',206307),
      ('Atlanta','88-41-2584',118159),
      ('Atlanta','539-03-7548',116718),
      ('Atlanta','587-63-6935',174801)
      )p(city, id, salary)
 

This produces the following result.
Inter-Percentile Range
----------------------
              123487.2
 
(1 row(s) affected)


In this example we will calculate the same inter-percentile range in each city.
SELECT city
,wct.IPR(salary, .10, .90, 'EXC') as [Inter-Percentile Range]
FROM (VALUES
      ('New York','429-00-6486',236503),
      ('New York','90-70-2526',224472),
      ('New York','87-85-0404',139802),
      ('New York','716-89-3089',185287),
      ('New York','159-78-5370',211900),
      ('New York','195-97-6820',186703),
      ('New York','95-49-2813',167451),
      ('New York','37-20-7422',149462),
      ('New York','44-48-0076',214708),
      ('New York','514-79-0041',226485),
      ('Los Angeles','526-34-4521',196402),
      ('Los Angeles','800-50-0868',205359),
      ('Los Angeles','41-34-3618',195679),
      ('Los Angeles','854-29-9398',131925),
      ('Los Angeles','673-30-3623',171091),
      ('Los Angeles','537-58-8889',110217),
      ('Los Angeles','808-68-4234',192836),
      ('Los Angeles','359-81-6735',209346),
      ('Los Angeles','731-80-2303',182186),
      ('Los Angeles','214-58-0842',125355),
      ('Chicago','456-79-9682',183698),
      ('Chicago','807-97-4784',194282),
      ('Chicago','981-16-3724',156083),
      ('Chicago','252-34-3054',226619),
      ('Chicago','613-28-9452',153366),
      ('Chicago','785-25-8628',205709),
      ('Chicago','451-26-7350',206085),
      ('Chicago','443-94-2401',120587),
      ('Chicago','696-26-8113',171185),
      ('Chicago','277-31-9760',211160),
      ('Dallas','537-88-7532',245231),
      ('Dallas','393-25-3503',238733),
      ('Dallas','612-17-0712',103152),
      ('Dallas','384-93-7285',228842),
      ('Dallas','745-10-7587',154749),
      ('Dallas','950-20-4045',102156),
      ('Dallas','477-48-7550',196533),
      ('Dallas','427-52-8597',238970),
      ('Dallas','891-19-0810',245204),
      ('Dallas','564-79-7612',196946),
      ('Boston','216-84-7134',128035),
      ('Boston','515-84-4073',249093),
      ('Boston','92-43-5775',205026),
      ('Boston','144-08-1092',198120),
      ('Boston','469-14-5012',174143),
      ('Boston','379-92-8313',166215),
      ('Boston','71-22-5132',105058),
      ('Boston','257-39-0324',107247),
      ('Boston','611-57-4279',118561),
      ('Boston','956-53-2865',232789),
      ('Denver','711-81-0072',240720),
      ('Denver','673-39-5028',159706),
      ('Denver','554-33-3980',232493),
      ('Denver','770-03-5304',203310),
      ('Denver','732-47-5077',123106),
      ('Denver','573-18-3567',149999),
      ('Denver','93-92-0334',162657),
      ('Denver','424-76-1468',116322),
      ('Denver','403-47-0063',246058),
      ('Denver','460-58-1833',198043),
      ('Miami','221-04-4153',130962),
      ('Miami','179-09-9839',227246),
      ('Miami','564-76-9437',144027),
      ('Miami','407-48-4081',138549),
      ('Miami','526-79-1840',208006),
      ('Miami','72-68-4977',170109),
      ('Miami','235-72-3903',191669),
      ('Miami','436-62-0474',161164),
      ('Miami','430-52-3914',162507),
      ('Miami','459-27-5541',238972),
      ('Phoenix','576-38-4531',238281),
      ('Phoenix','65-64-1278',197678),
      ('Phoenix','880-29-1997',159183),
      ('Phoenix','304-72-1881',194733),
      ('Phoenix','61-20-1046',221045),
      ('Phoenix','64-95-5514',105577),
      ('Phoenix','262-63-4021',186399),
      ('Phoenix','661-84-1023',234974),
      ('Phoenix','892-31-4821',115076),
      ('Phoenix','319-91-9463',239548),
      ('San Franciso','136-67-6873',148829),
      ('San Franciso','5-41-7374',114161),
      ('San Franciso','381-26-8852',232509),
      ('San Franciso','620-64-6243',112686),
      ('San Franciso','128-33-5550',208679),
      ('San Franciso','422-00-0156',107685),
      ('San Franciso','370-98-5607',133224),
      ('San Franciso','91-58-9543',218955),
      ('San Franciso','911-35-0448',187826),
      ('San Franciso','734-65-1268',223683),
      ('Atlanta','334-97-0585',240384),
      ('Atlanta','405-12-4222',124350),
      ('Atlanta','43-05-7567',233836),
      ('Atlanta','882-97-7996',134091),
      ('Atlanta','368-91-4292',173787),
      ('Atlanta','408-04-5921',140769),
      ('Atlanta','232-13-5280',206307),
      ('Atlanta','88-41-2584',118159),
      ('Atlanta','539-03-7548',116718),
      ('Atlanta','587-63-6935',174801)
      )p(city, id, salary)
GROUP BY city

This produces the following result.
city         Inter-Percentile Range
------------ ----------------------
Atlanta                    122867.1
Boston                     142185.7
Chicago                    101208.2
Dallas                     142972.7
Denver                     128523.8
Los Angeles                 97216.5
Miami                      106078.7
New York                    94733.2
Phoenix                    132894.4
San Franciso               123441.3
 
(10 row(s) affected)


In this example, we only select those cities where the interpercentile range, using the inclusive method, is less than 100,000.
SELECT city
,wct.IPR(salary, .10, .90, 'INC') as [Inter-Percentile Range]
FROM (VALUES
      ('New York','429-00-6486',236503),
      ('New York','90-70-2526',224472),
      ('New York','87-85-0404',139802),
      ('New York','716-89-3089',185287),
      ('New York','159-78-5370',211900),
      ('New York','195-97-6820',186703),
      ('New York','95-49-2813',167451),
      ('New York','37-20-7422',149462),
      ('New York','44-48-0076',214708),
      ('New York','514-79-0041',226485),
      ('Los Angeles','526-34-4521',196402),
      ('Los Angeles','800-50-0868',205359),
      ('Los Angeles','41-34-3618',195679),
      ('Los Angeles','854-29-9398',131925),
      ('Los Angeles','673-30-3623',171091),
      ('Los Angeles','537-58-8889',110217),
      ('Los Angeles','808-68-4234',192836),
      ('Los Angeles','359-81-6735',209346),
      ('Los Angeles','731-80-2303',182186),
      ('Los Angeles','214-58-0842',125355),
      ('Chicago','456-79-9682',183698),
      ('Chicago','807-97-4784',194282),
      ('Chicago','981-16-3724',156083),
      ('Chicago','252-34-3054',226619),
      ('Chicago','613-28-9452',153366),
      ('Chicago','785-25-8628',205709),
      ('Chicago','451-26-7350',206085),
      ('Chicago','443-94-2401',120587),
      ('Chicago','696-26-8113',171185),
      ('Chicago','277-31-9760',211160),
      ('Dallas','537-88-7532',245231),
      ('Dallas','393-25-3503',238733),
      ('Dallas','612-17-0712',103152),
      ('Dallas','384-93-7285',228842),
      ('Dallas','745-10-7587',154749),
      ('Dallas','950-20-4045',102156),
      ('Dallas','477-48-7550',196533),
      ('Dallas','427-52-8597',238970),
      ('Dallas','891-19-0810',245204),
      ('Dallas','564-79-7612',196946),
      ('Boston','216-84-7134',128035),
      ('Boston','515-84-4073',249093),
      ('Boston','92-43-5775',205026),
      ('Boston','144-08-1092',198120),
      ('Boston','469-14-5012',174143),
      ('Boston','379-92-8313',166215),
      ('Boston','71-22-5132',105058),
      ('Boston','257-39-0324',107247),
      ('Boston','611-57-4279',118561),
      ('Boston','956-53-2865',232789),
      ('Denver','711-81-0072',240720),
      ('Denver','673-39-5028',159706),
      ('Denver','554-33-3980',232493),
      ('Denver','770-03-5304',203310),
      ('Denver','732-47-5077',123106),
      ('Denver','573-18-3567',149999),
      ('Denver','93-92-0334',162657),
      ('Denver','424-76-1468',116322),
      ('Denver','403-47-0063',246058),
      ('Denver','460-58-1833',198043),
      ('Miami','221-04-4153',130962),
      ('Miami','179-09-9839',227246),
      ('Miami','564-76-9437',144027),
      ('Miami','407-48-4081',138549),
      ('Miami','526-79-1840',208006),
      ('Miami','72-68-4977',170109),
      ('Miami','235-72-3903',191669),
      ('Miami','436-62-0474',161164),
      ('Miami','430-52-3914',162507),
      ('Miami','459-27-5541',238972),
      ('Phoenix','576-38-4531',238281),
      ('Phoenix','65-64-1278',197678),
      ('Phoenix','880-29-1997',159183),
      ('Phoenix','304-72-1881',194733),
      ('Phoenix','61-20-1046',221045),
      ('Phoenix','64-95-5514',105577),
      ('Phoenix','262-63-4021',186399),
      ('Phoenix','661-84-1023',234974),
      ('Phoenix','892-31-4821',115076),
      ('Phoenix','319-91-9463',239548),
      ('San Franciso','136-67-6873',148829),
      ('San Franciso','5-41-7374',114161),
      ('San Franciso','381-26-8852',232509),
      ('San Franciso','620-64-6243',112686),
      ('San Franciso','128-33-5550',208679),
      ('San Franciso','422-00-0156',107685),
      ('San Franciso','370-98-5607',133224),
      ('San Franciso','91-58-9543',218955),
      ('San Franciso','911-35-0448',187826),
      ('San Franciso','734-65-1268',223683),
      ('Atlanta','334-97-0585',240384),
      ('Atlanta','405-12-4222',124350),
      ('Atlanta','43-05-7567',233836),
      ('Atlanta','882-97-7996',134091),
      ('Atlanta','368-91-4292',173787),
      ('Atlanta','408-04-5921',140769),
      ('Atlanta','232-13-5280',206307),
      ('Atlanta','88-41-2584',118159),
      ('Atlanta','539-03-7548',116718),
      ('Atlanta','587-63-6935',174801)
      )p(city, id, salary)
GROUP BY city
HAVING wct.IPR(salary, .10, .90, 'INC') < 100000
 

This produces the following result.
 
city         Inter-Percentile Range
------------ ----------------------
Chicago                     62617.8
Los Angeles                 81916.5
Miami                       90628.3
New York                    78990.8
 
(4 row(s) affected)
 

In this example we calculate the 40th percentile for a variety of data groupings, including one
NULL value and a GROUP that only contains one member.
SELECT dsc
      ,wct.IPR(x, .40, .60, 'INC') as [Inter-Percentile Range]
FROM (VALUES
      ('ABC', 15),('ABC',20),('ABC',35),('ABC',40),('ABC',50),
      ('DEF', 7),('DEF',10),('DEF',17),('DEF',20),('DEF',25),('DEF',70),
      ('GHI', 21),('GHI',28),('GHI',NULL),('GHI',38),('GHI',31),('GHI',52),
      ('JKL', 37)
      ) p(dsc, x)
GROUP BY dsc
 

This produces the following result.
dsc Inter-Percentile Range
---- ----------------------
ABC                       8
DEF                       3
GHI                       4
JKL                       0
 

(4 row(s) affected)



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service