cfquery group = not working with date

OK, I'm stuck and this is getting way late. I'm going to post the whole bit of code for this report. I'm thinking I'm using the array wrong. The structure for this report was 'borrowed' from another one, but that other report didn't have the date grouping. That's why I'm having these problems. Any help is greatly appreciated!!

<cfcase value="12">

<h2>WIR - Week In Review</h2>
<h3>BY DATE</h3>
<cfset START = DateFormat(URL.ReportBeginDate,"mm/dd/yy")>
<cfset END = DateFormat(DateAdd("d",1,URL.ReportEndDate),"mm/dd/yy")>

<!--- This query sets up a distinct list of tickets for the report. --->
<cfquery name="qryDistTckts" datasource="#Session.PMDBDataSource#">
select distinct a.SCTicket
from tblSCTicket a
where (a.OutageStart between '#START#' and '#END#')
order by a.SCTicket asc
</cfquery>

<!--- Define the array --->
<cfset aryDistTckts = arrayNew(1)>

<cfloop query="qryDistTckts">
<cfset aryDistTckts[CurrentRow] = SCTicket>
</cfloop>

<!--- *** Start outputting each ticket individually *** --->
<cfloop index="j" from="1" to="#qryDistTckts.RecordCount#">

<cfquery name ="qryWIRDate" datasource="#Session.PMDBDataSource#">
SELECT
'Date' as ReportGroup
, convert(datetime, convert(varchar, SCTicket.OutageStart, 104),104) as OutageDate
, SCTicket.OutageStart
, SCTicket.OutageEnd
, '1' as SortField
, Pri.Abbrev
, SCTicket.SCTicket
, SCTicket.IssueTitle
, SCTicket.RootCause
, SCTicket.Impact
, SCTicket.Resolution
, SCTicket.ResultOfChange
, CASE WHEN SCTicket.ResultOfChange = '1' THEN ', Result Of Change' END as ROCIndicator
FROM
tblSCTicket SCTicket
INNER JOIN tblPriorities Pri
ON SCTicket.PriorityID = Pri.PriorityID
WHERE (SCTicket.SCDateEntered between '#Start#' and '#End#')
AND SCTicket.SCTicket = '#aryDistTckts#'
ORDER BY OutageDate, OutageStart,
SCTicket
</cfquery>

<cfoutput query="qryWIRDate" group="OutageDate">
<P></P>

#DateFormat(OutageDate, "dddd, mm-dd-yy")#

<cfoutput>

<table class="ticket">
<tr>
<th>When:</th>
<td>#DateFormat(OutageStart, "mm/dd/yy")# from #TimeFormat(OutageStart, "h:mm tt")# until #TimeFormat(OutageEnd, "h:mm tt")#</td>
</tr>
<tr>
<th>Priority:</th>
<td>#Abbrev#, #SCTicket# #ROCIndicator#</td>
</tr>
<tr>
<th>Brief Description:</th>
<td>#IssueTitle#</td>
</tr>
<tr>
<th>Cause:</th>
<td>#RootCause#</td>
</tr>
<tr>
<th>Resolution:</th>
<td>#Resolution#</td>
</tr>
</table>
</cfoutput>
</cfoutput>

<!--- Ticket Impact Details --->
<cfquery name="qryAppInfo" datasource="#Session.PMDBDataSource#">
select a.SCTicket
,bu.BusinessUnitName
,ce.CenterName
from tblSCTicket a
inner join tblUserFacingApps uf on a.SCTicket = uf.SCTicket
left join tblUserFacingAppImpact ufi on uf.UFAID = ufi.UFAID
inner join tblBusinessUnits bu on ufi.BusinessUnitID = bu.BusinessUnitID
inner join tblCenters ce on ufi.LocationID = ce.ID
where a.SCTicket='#aryDistTckts#'
<!--- Used to eliminate UK/US Application --->
AND #PreserveSingleQuotes(ukExcludeApp)#
order by ce.CenterName ASC, bu.BusinessUnitName ASC
</cfquery>

<cfif qryAppInfo.RecordCount NEQ 0> <!--- Start Ticket Impact Details Verification --->

<table class="appInfoSm" width=50%>
<tr>
<th>Center</th>
<th>Business Unit</th>
</tr>
<cfoutput query="qryAppInfo">
<tr>
<td>#CenterName#</td>
<td>#BusinessUnitName#</td>
</tr>
</cfoutput>
</table>
<style type="text/css">
table.appInfoSm {
margin: auto auto;
padding: 0;
width: 50%;
border-collapse: collapse; }

table.appInfoSm th{
font: 0.75em normal;
padding: 0.25em;
text-transform: uppercase;
border-bottom: black 0.13em solid; }

table.appInfoSm td{
font: 0.75em normal;
padding: 0.25em;
text-align: center;
border-bottom: black 0.06em solid; }
</style>
</cfif> <!--- End Ticket Impact Details Verification --->
</cfloop>

<!---*****************************************************************************************************
<div style="page-break-after: always;"></div>

<cfoutput><h3>BY CENTER</h3></cfoutput>

<cfset START = DateFormat(URL.ReportBeginDate,"mm/dd/yy")>
<cfset END = DateFormat(DateAdd("d",1,URL.ReportEndDate),"mm/dd/yy")>

<!--- This query sets up a distinct list of tickets for the report. --->
<cfquery name="qryDistTckts" datasource="#Session.PMDBDataSource#">
SELECT DISTINCT
a.SCTicket

...and it goes on from there to use the same dataset, only sorting first by Center, then by Business Unit. A center can have many business units.

cfSearching, your suggestion worked and I got it going, sort of. Turns out the problem is a loop and array I'm using outside the query. I commented out the loop and this code --> "AND SCTicket.SCTicket = '#aryDistTckts#'" along with code I have in there after it to show child detail for the info we're working with. So I got that going for me, which is nice. Now I have to see if I can get the child info associated with the main ticket info which is what we've been working with. I'll play around that. If I get stuck again, I know where to go.

Thanks again!!

ChiTownJohnnyB wrote:
> Yeah, I think the query is fine, there's just something squirrely
> with my cf code.

Did you try the sample above and does it work differently than your current code?

If it does, try isolating your code. In a new script, run only the query and the <cfoutput> code you posted above. What are the results?

Yeah, I think the query is fine, there's just something squirrely with my cf code.

I'm positive I have the <cfoutput> tags nested and closed the way JR Bob Dobbs suggested. Here's a copy 'n paste of that bit of code:
<cfoutput query="qryWIRDate" group="OutageDate">
<P></P>
#DateFormat(OutageDate, "dddd, mm-dd-yy")#

<cfoutput>
<table class="ticket">
<tr>
<th>When:</th>
<td>#DateFormat(OutageStart, "mm/dd/yy")# from #TimeFormat(OutageStart, "h:mm tt")# until #TimeFormat(OutageEnd, "h:mm tt")#</td>
</tr>
<tr>
<th>Priority:</th>
<td>#Abbrev#, #SCTicket# #ROCIndicator#</td>
</tr>
<tr>
<th>Brief Description:</th>
<td>#IssueTitle#</td>
</tr>
<tr>
<th>Cause:</th>
<td>#RootCause#</td>
</tr>
<tr>
<th>Resolution:</th>
<td>#Resolution#</td>
</tr>
</table>
</cfoutput>

</cfoutput>

The data from the message from 7/22 4:39 is the recordset that I get from the query. I posted that to show that the date conversion is working to eliminate the times from the OutageStart date/time field. I just ran the report again, double checking and the grouping is still not working.

And you have nested <cfoutput...> blocks in your display? I did not see
any in your original post. It should look something like this.

<cfoutput query="myRecordSet" group="aColumn">
<!--- output here will appear once per unique value of 'aColumn'* --->

<cfoutput>
<!--- output here will appear once per row in the record set --->
</cfoutput>

<!--- output here will appear once per unique value of 'aColumn'* --->
</cfoutput>

NOTE: * it is not actually once per unique value in the specified
column, but any time the value in the column changes. Thus strange
results can be had with improperly sorted record sets.

Updated: Whoops. I overlooked JR Bob Dobbs' first comment. Are you positive you added the extra <cfoutput> tags as he showed in his response above? The results suggests otherwise.

Ian, I trimmed out fields that are irrelevant to the question (and a company security risk to put on the 'net).

ReportGroup OutageDate OutageStart OutageEnd SCTicket
Date 6/1/2008 12:00:00 AM 6/1/2008 12:00:00 PM 6/1/2008 3:15:00 PM IM1944299
Date 6/2/2008 12:00:00 AM 6/2/2008 7:40:00 AM 6/2/2008 10:24:00 AM IM1944858
Date 6/3/2008 12:00:00 AM 6/3/2008 5:26:00 PM 6/3/2008 5:40:00 PM IM1951161
Date 6/4/2008 12:00:00 AM 6/4/2008 7:58:00 AM 6/4/2008 10:55:00 AM IM1952228
Date 6/4/2008 12:00:00 AM 6/4/2008 8:00:00 AM 6/4/2008 8:47:00 AM IM1952094
Date 6/4/2008 12:00:00 AM 6/4/2008 8:00:00 AM 6/4/2008 8:31:00 AM IM1952096
Date 6/5/2008 12:00:00 AM 6/5/2008 3:00:00 AM 6/5/2008 9:00:00 AM IM1953703
Date 6/5/2008 12:00:00 AM 6/5/2008 6:30:00 AM 6/5/2008 12:12:00 PM IM1953979
Date 6/5/2008 12:00:00 AM 6/5/2008 6:50:00 PM 6/5/2008 8:00:00 PM IM1954403
Date 6/5/2008 12:00:00 AM 6/5/2008 11:00:00 PM 6/6/2008 12:16:00 AM IM1954455
Date 6/6/2008 12:00:00 AM 6/6/2008 12:22:00 AM 6/6/2008 5:00:00 AM IM1954571
Date 6/8/2008 12:00:00 AM 6/8/2008 10:00:00 AM 6/8/2008 1:40:00 PM IM1956365

ChiTownJohnnyB wrote:
> Bob and cfSearching,
> Thanks for the quick replies, but neither worked. Still getting the same output.
>
> John


Start with showing what the record set actually looks like that is
produced by the query. Can you post the first few rows, especially the
date field you are trying to group on?

Bob and cfSearching,
Thanks for the quick replies, but neither worked. Still getting the same output.

John