sql server 2005

How can I write a query against A to display B? When the count(PlanEffDt) is 2, I only want to display the FFSContr_ID record with the max(PlanEffDt).

FFSContr_ID      Par_id                Trend_type      TypeOfContract      Year      count(PlanEffDt)      PlanEffDt                                                
101853             956006143 A      CTR                  MDCR                     2009       1             6/1/2009                                                
241296             956006143 A      CTR                  MDCR                     2010       1             1/1/2010                                                
241297             956006143 A      CTR                  MDCR                     2011       1             1/1/2011                                                
218955             956006143 A      CTR                  MDCR                     2011       1             7/1/2011                                                
241298             956006143 A      CTR                  MDCR                     2012       1             7/1/2012                                                
241299             956006143 A      CTR                  MDCR                     2013       1             7/1/2013
277568             956006143 A      CTR                  MDCR                     2014       1             7/1/2014

LastOfFFSContr_ID      Par_id                 Trend_type      TypeOfContract      Year      count(PlanEffDt)      MaxOfPlanEffDt
101853                        956006143 A      CTR                  MDCR                     2009           1                      6/1/2009
241296                        956006143 A      CTR                  MDCR                     2010           1                      1/1/2010
218955                        956006143 A      CTR                  MDCR                     2011           2                      7/1/2011
241298                        956006143 A      CTR                  MDCR                     2012           1                      7/1/2012
241299                        956006143 A      CTR                  MDCR                     2013           1                      7/1/2013
277568                        956006143 A      CTR                  MDCR                     2014           1                      7/1/2014
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

case b.planeffdt = 2 then b.maxofplaneffdt
     else a.planeffdt
from tableA a inner join tableb b on
a.ffscontr_id = b.last_ffscontr_id
            SELECT TOP 1 FFSContr_ID
            FROM TRP.FFS_Contracts B
            WHERE A.Par_id = B.Par_id
            AND A.TypeOfContract = B.TypeOfContract
            AND A.[Year] = B.[Year]
            ORDER BY PlanEffDt DESC) FFSContr_ID,
      Par_id, TypeOfContract, [Year], count(PlanEffDt), max(PlanEffDt)
FROM TRP.FFS_Contracts A
WHERE TypeOfContract in ('MDCR','MPPO') and Trend_type = 'CTR'
GROUP BY Par_id, TypeOfContract, [Year]

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HNA071252Author Commented:
It works. Thank you so much! You've made my day.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.