Microsoft SQL query help: Find max date based on conditions, else return NULL

Greetings Experts,

I have previously opened this question before and closed it thinking it was working.  Apparently it isnt.
Here is the orginal question;

http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_23622144.html

I will explain what I am attempting to accomplish.  I am trying to write a query that will determine the max date of columm ONLY if all the dates are present for the rest of the grouping.  If there is a single NULL, then return NULL.

For example
StudyID          SiteCertDate       SiteCertType
   A                 01/01/2008           IRB Approval
   A                 02/02/2008           Brochure
    A                   NULL                 1592
   B                  05/01/2008          IRB Approval
   B                    NULL                  Brochure
   B                    NULL                  1592
    C               04/01/2008              IRB Approval
   C              03/01/2008             Brochure
   C              05/01/2008               1592

Using this example, the results would look like this

StudyID          SiteCertDate      
    A                    NULL
    B                    NULL
    C                05/01/2008   <---MAX date of grouping StudyID=C

Below is the query I have been working with but for what ever reason I keep getting a result set that shows the max date even if any of the other dates are NULL.  

select
case when sum(case when isnull(sinv.sitecertvaluedate,'19000102') = '19000102' then 1 else 0 end)  =0 then max(sinv.sitecertvaluedate) else NULL end as 'Sitecertdate',
sinv.stdinv_id

FROM trialworksIkaria.dbo.tbl_sitecertstdinv SINV
left outer join trialworksikaria.dbo.tbl_sitecertstudy S
on S.sitecertstudy_ID = Sinv.sitecertstudy_ID

Where
S.sitecertname = "Brochure "  or  s.sitecertname ="IRB Approval" or  s.sitecertname ="1592"

group by
sinv.stdinv_id















Start Free Trial