hello, I've been working on this query for a while and
cant get it to work the way I need it to, i am close
though.....here is the problem - the cas table is
keyed by [iCasacApptID], the cats table has a
composite key of [iCasacApptID, cTreatSelectID]. So
when I output the results if iCasacApptID is found in
the cats table twice, then two records are outputed in
my report instead of one....so, if there are 2
TreatSelects to one CasacApptID, then I want that to
be one row..I though that
<table>
<cfoutput>
<tr>
<td>#QRYtp.TreatSelect#</td>
</tr>
</cfoutput>
</table>
would accomplish this....but it isnt. here is my code
below....can anyone see what I am missing...
Thanks yet again!
<cfloop query="treatCount">
<cfif treatcount.TreatCount gt 6>
<cfquery name="QRYtp" datatasource="#DB#">
Select cas.iClientID ,
cd1.vCodeDecode as TreatSelect,
cas.iCasacApptID,
'#treatCount.TreatProgID#' as TrueProgID,
'#treatCount.TreatProg#' as TrueProg,
'#treatCount.HRACode#' as TrueHRA
>From CasacAppt cas,
CasacApptTreatSelect cats,
CodeDecode cd1
Where '#treatCount.TreatProgID#' = cas.iTreatProgID
and cas.iCasacApptID = cats.iCasacApptID
AND cats.cTreatSelectID = cd1.icodedecodeID
AND cas.dCasacDate = '#variables.DBeginDate#'
AND cas.dCasacDate <= '#variables.dEndDate#'
AND cas.iCasacApptID in (select cs.icasacApptID
from CasacApptTreatSelect cs)
Union
Select cas.iClientID ,
'None' as TreatSelect,
cas.iCasacApptID,
'#treatCount.TreatProgID#' as TrueProgID,
'#treatCount.TreatProg#' as TrueProg,
'#treatCount.HRACode#' as TrueHRA
>From CasacAppt cas,
CodeDecode cd1,
Client cl
Where cas.iClientID = cl.iClientID
AND '#treatCount.TreatProgID#' = cas.iTreatProgID
AND cas.dCasacDate >= '#variables.DBeginDate#'
AND cas.dCasacDate <= '#variables.dEndDate#'
AND cas.iCasacApptID not in (select
cs.icasacApptID from CasacApptTreatSelect cs)
</cfquery>
<table align="center" border="1" width="650">
<tr>
<cfoutput query="QRYtp" group="iCasacApptID">
<td>#QRYtp.iCasacApptID#</td>
<td>#QRYtp.TrueProg#</td>
<td>#QRYtp.iClientID#</td>
<TD>
<table>
<cfoutput>
<tr>
<td>#QRYtp.TreatSelect#</td>
</tr>
</cfoutput>
</table>
</TD>
</tr></cfoutput>
</table></cfif>
</cfloop>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
