declare @fdate int =201605, @tdate int =201704
drop table #temp3
drop table #temp1
drop table #temp2
select YEAR_MO, count(*) MBRMTH into #temp3 from [VW_MEMBMTHS] group by YEAR_MO order by 1
SELECT a.LOB, a.PROD_NAME, a.HCG_MR_line HCG_Detail, a.YEAR_MO, PROV_LNAME BillingProviderName,MBRMTH MemberMonth,sum(AMT_PAID) ClaimsAmtPaid ,
sum(MR_procs_raw)Utilization into #temp1
FROM [XXXXXXX].[dbo].[Table1] a with(nolock)
left outer join [XXXXXXX].[dbo].[DERS] b on PROV_ID=PROV_BILL
left outer join #temp3 c on a.YEAR_MO=c.YEAR_MO
where a.YEAR_MO between @fdate and @tdate and HCG_MR_line in('P84','P85')
group by a.HCG_MR_line, a.LOB, a.PROD_NAME, a.YEAR_MO,PROV_LNAME ,MBRMTH
order by a.YEAR_MO,PROV_LNAME
--select * from #temp1 order by cntprovd desc
select * ,ROW_NUMBER() over ( PARTITION BY year_MO order by year_MO,ClaimsAmtPaid desc ) AS RowNo into #temp2
from #temp1 order by year_MO,ClaimsAmtPaid desc
select * from #temp2 where RowNo<21
