Min,Max with Ranking

Transact-SQL
1

Hi Team,

need help on building the query

Existing query with output:
DECLARE @TABLE TABLE
(PLANID VARCHAR(10),Measurekey VARCHAR(10),submeasurekey VARCHAR(10)
,source_mesurement_year VARCHAR(10),source_Reported_year VARCHAR(10),Percentile varchar(100),value decimal(28,10) )

INSERT INTO @TABLE

SELECT 'SC','CDC','EYEEXAM','2016','2017','10th_percentile','39.6600000000'
UNION ALL
SELECT 'SC','CDC','EYEEXAM','2016','2017','33th_Percentile','51.6900000000'
UNION ALL
SELECT 'SC','CDC','EYEEXAM','2016','2017','66th_percentile','60.3400000000'
UNION ALL
SELECT 'SC','CDC','EYEEXAM','2016','2017','90th_percentile','68.2100000000'
UNION ALL
SELECT 'SC','CDC','EYEEXAM','2016','2017','Above90th_percentile','1.0000000000'

SELECT * FROM @TABLE

PLANID Measurekey submeasurekey source_mesurement_year source_Reported_year Percentile value
SC CDC EYEEXAM 2016 2017 10th_percentile 39.6600000000
SC CDC EYEEXAM 2016 2017 33th_Percentile 51.6900000000
SC CDC EYEEXAM 2016 2017 66th_percentile 60.3400000000
SC CDC EYEEXAM 2016 2017 90th_percentile 68.2100000000
SC CDC EYEEXAM 2016 2017 Above90th_percentile 1.0000000000

Expected output:

PLANID Measurekey submeasurekey source_mesurement_year source_Reported_year Percentile value Min Max Rank
SC CDC EYEEXAM 2016 2017 10th_percentile 39.66 0 0.3966 1
SC CDC EYEEXAM 2016 2017 33th_Percentile 51.69 0.3967 0.5169 2
SC CDC EYEEXAM 2016 2017 66th_percentile 60.34 0.517 0.6034 3
SC CDC EYEEXAM 2016 2017 90th_percentile 68.21 0.6035 0.6821 4
SC CDC EYEEXAM 2016 2017 Above 90th_percentile 1 0.6822 1 5
2

Which specific version of SQL Server? IN particular, is it SQL 2012 or later? It makes in difference in the best way to write the query, since LEAD/LAG are only available from 2012 on.

3

For SQL2012+, something like this:

SELECT *, 
    ISNULL(LAG(value / 100.00, 1) OVER(ORDER BY CASE WHEN value = 1 THEN 100 ELSE value END) + .0001, 0) AS Min,
    CASE WHEN value = 1 THEN 1.00 ELSE value / 100.00 END AS Max,
    ROW_NUMBER() OVER(ORDER BY CASE WHEN value = 1 THEN 100 ELSE value END) AS Rank
FROM @TABLE
4

Awesome. thanks you so much for the quick reply

5

i tried to add some couple of addtional records

DECLARE @TABLE TABLE
(PLANID VARCHAR(10),Measurekey VARCHAR(10),submeasurekey VARCHAR(10)
,source_mesurement_year VARCHAR(10),source_Reported_year VARCHAR(10),Percentile varchar(100),value decimal(28,10) )

INSERT INTO @TABLE

SELECT 'SC','CDC','EYEEXAM','2016','2017','10th_percentile','39.6600000000'
UNION ALL
SELECT 'SC','CDC','EYEEXAM','2016','2017','33th_Percentile','51.6900000000'
UNION ALL
SELECT 'SC','CDC','EYEEXAM','2016','2017','66th_percentile','60.3400000000'
UNION ALL
SELECT 'SC','CDC','EYEEXAM','2016','2017','90th_percentile','68.2100000000'
UNION ALL
SELECT 'SC','CDC','EYEEXAM','2016','2017','Above90th_percentile','1.0000000000'

union all

SELECT 'SC','CDC','BP','2017','2018','10th_percentile','39.6600000000'
UNION ALL
SELECT 'SC','CDC','BP','2017','2018','33th_Percentile','51.6900000000'
UNION ALL
SELECT 'SC','CDC','BP','2017','2018','66th_percentile','60.3400000000'
UNION ALL
SELECT 'SC','CDC','BP','2017','2018','90th_percentile','68.2100000000'
UNION ALL
SELECT 'SC','CDC','BP','2017','2018','Above90th_percentile','1.0000000000'

PLANID Measurekey submeasurekey source_mesurement_year source_Reported_year Percentile value Min Max Rank
SC CDC EYEEXAM 2016 2017 10th_percentile 39.6600000000 0.0000000000000000 0.3966000000000000 1
SC CDC BP 2017 2018 10th_percentile 39.6600000000 0.3967000000000000 0.3966000000000000 2
SC CDC BP 2017 2018 33th_Percentile 51.6900000000 0.3967000000000000 0.5169000000000000 3
SC CDC EYEEXAM 2016 2017 33th_Percentile 51.6900000000 0.5170000000000000 0.5169000000000000 4
SC CDC EYEEXAM 2016 2017 66th_percentile 60.3400000000 0.5170000000000000 0.6034000000000000 5
SC CDC BP 2017 2018 66th_percentile 60.3400000000 0.6035000000000000 0.6034000000000000 6
SC CDC BP 2017 2018 90th_percentile 68.2100000000 0.6035000000000000 0.6821000000000000 7
SC CDC EYEEXAM 2016 2017 90th_percentile 68.2100000000 0.6822000000000000 0.6821000000000000 8
SC CDC EYEEXAM 2016 2017 Above90th_percentile 1.0000000000 0.6822000000000000 1.0000000000000000 9
SC CDC BP 2017 2018 Above90th_percentile 1.0000000000 0.0101000000000000 1.0000000000000000 10

rank is not coming properly
the Unique value is PlanID,Measurekey,submeasurekey,source_measurementyear,source_reported_year,percentile

6

You didn't state that originally. Try this then:

SELECT *, 
    ISNULL(LAG(value / 100.00, 1) OVER(PARTITION BY PlanID, Measurekey, submeasurekey,
        source_measurement_year, source_reported_year 
        ORDER BY CASE WHEN value = 1 THEN 100 ELSE value END) + .0001, 0) AS Min,
    CASE WHEN value = 1 THEN 1.00 ELSE value / 100.00 END AS Max,
    ROW_NUMBER() OVER(PARTITION BY PlanID, Measurekey, submeasurekey, 
        source_measurement_year, source_reported_year 
        ORDER BY CASE WHEN value = 1 THEN 100 ELSE value END) AS Rank
FROM @TABLE