Patricia G. L. Hall wrote: > > Select > 'False' as FSHUnknown, > 'False' as E2Unknown, > '' as ptGnRHaFlare, > '' as PtGnRHaSupp, > ci.caseid as clinicCycleID, > Case count(stim.stimulationid) > When Then 1 > Else 0 > End as PtUnstimulated, > stim.labNormalFSHLevel as labFshUpLimitNormal, > stim.maxFSHLevel as ptMaxFshLevel, > stim.labNormalE2Level as labE2UpLimitNormal, > stim.maxE2Level as ptMaxE2Level, > Case stim.clomiphenedose > When Then > When NULL Then 0 > Else 1 > End AS ptClomiphene, > stim.clomiphenedose as ptClomipheneDosage, > Case stim.fshTotalIU > When Then 0 > When NULL Then 0 > Else 1 > End As PtFSH, > stim.fshTotalIU as ptFSHDosage, > stim.gnrhagonistid, > gnrh.gnrhagonisttypename, > Case stim.GNRHAntagonistDays > When Then 0 > When NULL Then 0 > Else 1 > End as PtGnRHA_Antagonist, > Case > (Select count(etp.etprocedureid) from eTransferProtocolLink etp Where > etp.protocolid = 12 and etp.etProcedureid = et.etProcedureid) > When then 0 > When NULL then 0 > Else 1 > End as transGIFT, > Case > (Select count(etp.etprocedureid) from eTransferProtocolLink etp Where > etp.protocolid IN (19,13) and etp.etProcedureid = et.etProcedureid) > When then 0 > When NULL then 0 > Else 1 > End as transZIFT, > Case > (Select count(etp.etprocedureid) from eTransferProtocolLink etp Where > etp.protocolid NOT IN (19,13,12,6) and etp.etProcedureid = > et.etProcedureid) > When then 0 > When NULL then 0 > Else 1 > End as transIVF, > Case et.AHPERFORMED > When 'None' Then 1 > When 'Some' Then 2 > When 'All' Then 3 > Else 0 > End as AZHid, > et.treatmentAsIntended as asIntended, > Case count(et.etprocedureid) > When Then 0 > When null Then 0 > Else 1 > End as transferAttempted, > et.proceduredate > from caseInfo ci > Left Outer Join stimulation stim on ci.caseid = stim.caseid > Left Outer join gnrhagonisttype gnrh on stim.gnrhagonisttypeid = > gnrh.gnrhagonisttypeid > Left Outer Join etProcedure et on ci.caseID = et.caseid > Left Outer Join eTransferProtocolLink etp on et.etProcedureID = > etp.etProcedureID > Where > ci.caseid IN (#session.sartdata.cycles.cyclespk#) > Group By > ci.caseid, > stim.labNormalFSHLevel, > stim.maxFSHLevel, > stim.labNormalE2Level, > stim.maxE2Level, > stim.clomiphenedose, > stim.fshTotalIU, > stim.gnrhagonistid, > gnrh.gnrhagonisttypename, > stim.GNRHAntagonistDays, > et.etProcedureID, > et.AHPERFORMED, > et.treatmentAsIntended, > et.proceduredate
You should be able to just do a MAX() around the Gift field (if I understand your intentions correctly, you need them around some other CASE statements as well) and add group by statements as needed. Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com