Below is my temp table and query – I am assuming I can inner join or use a different condition in the where clause. Where do I filter the final output of the temp table and how? Do I do the inner join in the temp table or in the query below it inner joining the temp table to the main data? I have a left join with the temp table, do I just switch that to be a inner join?

select distinct

rl.LookupValue

into #Risk2

from Caprev.dbo.MemberData md

left join caprev.dbo.RiskGrid r on md.fkRiskGridID = r.pkRiskGridID
left join caprev.dbo.LookupValues rl on r.fkRiskTypeID = rl.pkLookupID

Where rl.LookupValue IN ('Full Risk', 'Global', 'Shared Risk', 'Global Risk', 'Shared Risk MSA SCAN', 'SHARED RISK MSA', 'FULL RISK- TMMC & HMH FULL RISK', 'FULL RISK-HMH','FULL RISK-LCM', 'FULL RISK-PHCMC', 'FULL RISK-TMMC', 'FULL RISK-ANAHEIM REGIONAL', 'FULL RISK-MEMORIAL HEALTH', 'FULL RISK-NORTHRIDGE','GLOBAL RISK-LCM')

select
    --md.fkProcessedMonthID
    pm.StartDate ProcessedPeriod
    --,md.fkEarnedMonthID
    ,em.StartDate EarnedPeiod
    --,md.fkHealthPlanID
    ,h.HealthPlan
    --,md.fkLOBID
    ,l.LOBName
    --,md.fkProductID
    ,p.ProductName
    --,md.fkSiteID
    ,s.SiteName
    --,tmp.CdoCode
    ,sum(md.Amount) Amount
        ,sum(case when md.fkCapitationSubTypeID in (48,59) then md.MMCount
                            else 0 end)MMCount
    --, cs.CapitationSubType
    --, ct.CapitationType
    ,tmp.lookupvalue
    

from CapRev.dbo.MemberData md

left join caprev.dbo.HealthPlan h on md.fkHealthPlanID = h.pkHealthPlanID
left join caprev.dbo.Site s on md.fkSiteID = s.pkSiteID
left join caprev.dbo.Product p on md.fkProductID = p.pkProductID
left join caprev.dbo.LOB l on md.fkLOBID = l.pkLOBID
left join caprev.dbo.CDO c on md.fkCDOId = c.pkCdoID


left join caprev.dbo.CapitationSubType CS on md.fkCapitationSubTypeID = cs.pkCapitationSubTypeID
Left join caprev.dbo.CapitationType CT on md.fkCapitationTypeID = ct.pkCapitationTypeID

left join caprev.dbo.Batch b on md.fkBatchID = b.pkBatchID
left join caprev.dbo.LookupValues lv on b.fkStatusID = lv.pkLookupID

left join caprev.dbo.FiscalCalendar em on md.fkEarnedMonthID = em.pkFiscalCalendarID
left join caprev.dbo.FiscalCalendar pm on md.fkProcessedMonthID = pm.pkFiscalCalendarID


left join caprev.dbo.RiskGrid r on md.fkRiskGridID = r.pkRiskGridID
left join caprev.dbo.LookupValues rl on r.fkRiskTypeID = rl.pkLookupID
left join #Risk2 tmp on tmp.lookupvalue = rl.lookupvalue

Where year(em.StartDate) = 2023
AND h.HealthPlan IN ('LA CARE', 'AETNA HEALTH PLAN', 'AETNA SENIOR', 'UNITED HEALTHCARE')
AND (cs.CapitationSubType IN ('Inst Cap Manual Adjustment', ' Inst Cap Manual Adjustment-Share', 'Inst Cap Manual Adjustment-Deduct', 'Insta Cap Manual Adjustment-Share Deduct', 'Inst Cap Manual Adjustment-Share Fund')
    or ct.CapitationType = 'professional Capitation')
AND LV.LookupValue='posted'
AND c.CdoCode IN ('HCP', 'AC')
AND (pm.StartDate BETWEEN '2024-02-01' AND '2024-05-31')


group by
    --md.fkProcessedMonthID
    pm.StartDate
    --,md.fkEarnedMonthID
    ,em.StartDate
    --,md.fkHealthPlanID
    ,h.HealthPlan
    --,md.fkLOBID
    ,l.LOBName
    --,md.fkProductID
    ,p.ProductName
    --,md.fkSiteID
    ,s.SiteName
    --,tmp.CdoCode
    --, cs.CapitationSubType
    --, ct.CapitationType
    ,tmp.lookupvalue

having tmp.LookupValue is not null