Question using MAX in sql server 2008 R2 1

[wbodger]

I have a system where participants come in to see if they qualify for a study. They first sign a consent and they can have up to four consents (visit code BLN1, BLN2, BLN3, BLN4). If they are determined to be ineligible, then they get a record in ineligible using that same visit code (BLN1, BLN2 etc.). Now, I need to run a report that grabs the greatest visit code from consent then checks to see if they have a corresponding record (by id and visitcode) in ineligible, or finalg at which point I assign their status: ineligible (if matching record in ineligible), confirmed (if matching record in finalg) or the default pending (if no record in either ineligible or finalg). My dilemma (and I'm not sure why it is a dilemma right now, but I cannot come up with a solution), if I have

SELECT max(c.STUDY_PROTOCOL_VISIT_CODE)
      ,c.STUDY_PARTICIPANT_ID
      ,CASE
		when i.STUDY_PARTICIPANT_ID IS not null then 'Ineligible'
		when f.STUDY_PARTICIPANT_ID IS not null then 'Confirmed'
		else 'Pending'
		end as status
  FROM [dbo].[tblBLConsent] c left outer join dbo.tblIneligibility I on c.study_participant_id = i.study_participant_id and c.STUDY_PROTOCOL_VISIT_CODE=i.STUDY_PROTOCOL_VISIT_CODE
	left outer join dbo.tblFinalG f on c.STUDY_PARTICIPANT_ID=f.STUDY_PARTICIPANT_ID and c.STUDY_PROTOCOL_VISIT_CODE=f.STUDY_PROTOCOL_VISIT_CODE
where C.DELETEFLAG=0
GROUP BY c.STUDY_PARTICIPANT_ID, i.STUDY_PARTICIPANT_ID, f.STUDY_PARTICIPANT_ID
order by c.STUDY_PARTICIPANT_ID

Then I get everything including multiple records from those few IDs that actually have more than one visit code in tblBLConsent. However, I only want the most recent visit code, so how do I join on only the max visit code as it won't allow me to join on max(c.study_protocol_visit_code)=i.study_protocol_visit_code

Thanks!
Willie

 

[imex]

Try:

with CTE_M as
(
    SELECT STUDY_PARTICIPANT_ID, max(STUDY_PROTOCOL_VISIT_CODE) as STUDY_PROTOCOL_VISIT_CODE
    FROM [dbo].[tblBLConsent]
    where DELETEFLAG = 0
    GROUP BY STUDY_PARTICIPANT_ID
)

SELECT m.STUDY_PROTOCOL_VISIT_CODE
      ,M.STUDY_PARTICIPANT_ID
      ,CASE
           when i.STUDY_PARTICIPANT_ID IS not null then 'Ineligible'
           when f.STUDY_PARTICIPANT_ID IS not null then 'Confirmed'
           else 'Pending'
       end as status
FROM CTE_M as m
left outer join dbo.tblIneligibility I 
    on m.study_participant_id = i.study_participant_id and 
       m.STUDY_PROTOCOL_VISIT_CODE = i.STUDY_PROTOCOL_VISIT_CODE
left outer join dbo.tblFinalG f 
    on m.STUDY_PARTICIPANT_ID = f.STUDY_PARTICIPANT_ID and 
       m.STUDY_PROTOCOL_VISIT_CODE = f.STUDY_PROTOCOL_VISIT_CODE
order by m.STUDY_PARTICIPANT_ID

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[wbodger]

Yup, that was it, thanks!