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

Reply via email to