I have a query of queries, which works fine and when I dump the results all looks good. When I try a cfoutput, however, I only get results for one row, repeating. It's probably something basic, but I'm stumped...Ideas anyone??
Query:
<cfquery name="pb_bp_time_diff" dbtype="query">
SELECT pb_bp_mod_time.mod_ID, pb_bp_enroll_time.enroll_ID, pb_bp_mod_time.mod_date,pb_bp_enroll_time.enroll_date
FROM pb_bp_enroll_time, pb_bp_mod_time
WHERE pb_bp_mod_time.mod_date BETWEEN <cfqueryparam cfsqltype="cf_sql_timestamp" value="#form.startdate#"> AND <cfqueryparam cfsqltype="cf_sql_timestamp" value="#form.enddate#"> AND pb_bp_mod_time.ssn = pb_bp_enroll_time.ssn
ORDER BY pb_bp_mod_time.mod_date
Output Table:
<table width="80%" border="0">
<tr class="style6">
<th scope="col"><span class="style9">Mod ID</span></th>
<th scope="col"><span class="style9">Enroll ID</span></th>
<th scope="col"><span class="style9">Enroll Date</span></th>
<th scope="col"><span class="style9">Mod Date</span></th>
<th scope="col"><span class="style9">Months to Enroll</span></th>
</tr>
<cfoutput query="pb_bp_time_diff">
<tr>
<td><span class="style6">#pb_bp_mod_time.mod_ID#</span></td>
<td><span class="style6">#pb_bp_enroll_time.enroll_ID#</span></td>
<td><span class="style6">#pb_bp_enroll_time.enroll_date#</span></td>
<td><span class="style6">#pb_bp_mod_time.mod_date#</span></td>
<td><span class="style6">#DateDiff("m", pb_bp_enroll_time.enroll_date, pb_bp_mod_time.mod_date)#</span></td>
</tr>
</cfoutput>
</table>
CFdump sample:
time to add - query
ENROLL_DATE ENROLL_ID MOD_DATE MOD_ID
1 2002-12-03 00:00:00.0 4243 2003-01-01 00:00:00.0 4333
2 2000-08-17 00:00:00.0 733 2003-01-03 00:00:00.0 4349
3 2002-08-27 00:00:00.0 3858 2003-01-09 00:00:00.0 4377
4 2000-06-16 00:00:00.0 447 2003-01-10 00:00:00.0 4379
Cfoutput sample:
Mod ID Enroll ID Enroll Date Mod Date Months to Enroll
7873 292 2000-05-08 00:00:00.0 2004-12-19 19:35:13.707 55
Query:
<cfquery name="pb_bp_time_diff" dbtype="query">
SELECT pb_bp_mod_time.mod_ID, pb_bp_enroll_time.enroll_ID, pb_bp_mod_time.mod_date,pb_bp_enroll_time.enroll_date
FROM pb_bp_enroll_time, pb_bp_mod_time
WHERE pb_bp_mod_time.mod_date BETWEEN <cfqueryparam cfsqltype="cf_sql_timestamp" value="#form.startdate#"> AND <cfqueryparam cfsqltype="cf_sql_timestamp" value="#form.enddate#"> AND pb_bp_mod_time.ssn = pb_bp_enroll_time.ssn
ORDER BY pb_bp_mod_time.mod_date
Output Table:
<table width="80%" border="0">
<tr class="style6">
<th scope="col"><span class="style9">Mod ID</span></th>
<th scope="col"><span class="style9">Enroll ID</span></th>
<th scope="col"><span class="style9">Enroll Date</span></th>
<th scope="col"><span class="style9">Mod Date</span></th>
<th scope="col"><span class="style9">Months to Enroll</span></th>
</tr>
<cfoutput query="pb_bp_time_diff">
<tr>
<td><span class="style6">#pb_bp_mod_time.mod_ID#</span></td>
<td><span class="style6">#pb_bp_enroll_time.enroll_ID#</span></td>
<td><span class="style6">#pb_bp_enroll_time.enroll_date#</span></td>
<td><span class="style6">#pb_bp_mod_time.mod_date#</span></td>
<td><span class="style6">#DateDiff("m", pb_bp_enroll_time.enroll_date, pb_bp_mod_time.mod_date)#</span></td>
</tr>
</cfoutput>
</table>
CFdump sample:
time to add - query
ENROLL_DATE ENROLL_ID MOD_DATE MOD_ID
1 2002-12-03 00:00:00.0 4243 2003-01-01 00:00:00.0 4333
2 2000-08-17 00:00:00.0 733 2003-01-03 00:00:00.0 4349
3 2002-08-27 00:00:00.0 3858 2003-01-09 00:00:00.0 4377
4 2000-06-16 00:00:00.0 447 2003-01-10 00:00:00.0 4379
Cfoutput sample:
Mod ID Enroll ID Enroll Date Mod Date Months to Enroll
7873 292 2000-05-08 00:00:00.0 2004-12-19 19:35:13.707 55