CODEWITHSUNDEEP
sql
Herman
Herman

Reputation: 157

SQL: Show only the Percentile

I have this working query

SELECT MIN([DealerCode])   
    ,[201309]   
    ,RowNum = Row_Number() OVER(Order By [201309])   
    ,Rnk = RANK() OVER(ORDER BY [201309])   
    ,DenseRnk = DENSE_RANK() OVER(ORDER BY [201309])   
    ,NTile4  = NTILE(100) OVER(ORDER BY [201309])   
    ,BM = RANK() OVER(ORDER BY [201309])*0.7 

FROM [SA_Sew].[dbo].[Sew_YTD_Composite$]   
where Ratio_ID = 'fi02u' AND DealerCode like '%VW%'    
GROUP BY [201309];

How would I be able to get the NTile4 calculation as a field to be able to use it in the Where Field? NTile4 = 70 is benchmark and I need to only see the benchmark.

Thanks!

Upvotes: 0

Views: 64

Answers (1)

Deepshikha
Deepshikha

Reputation: 10264

Write as:

select * from 
(
SELECT MIN([DealerCode]) as MinDealerCode   
    ,[201309]   
    ,RowNum = Row_Number() OVER(Order By [201309])   
    ,Rnk = RANK() OVER(ORDER BY [201309])   
    ,DenseRnk = DENSE_RANK() OVER(ORDER BY [201309])   
    ,NTile4  = NTILE(100) OVER(ORDER BY [201309])   
    ,BM = RANK() OVER(ORDER BY [201309])*0.7 

FROM [SA_Sew].[dbo].[Sew_YTD_Composite$]   
where Ratio_ID = 'fi02u' AND DealerCode like '%VW%'    
GROUP BY [201309]
) as T
where NTile4  = 70;

Upvotes: 2

Related Questions