Skip to main content
Inspiring
July 22, 2008
Question

cfquery group = not working with date

  • July 22, 2008
  • 12 replies
  • 928 views
I'm working on a report in CFML that should group records by date, something like:
Tuesday, July 1, 2008
When: July 1 from 8:00 AM until 8:12 AM
Location: Chicago
Business Unit: WLS
Priority: 1, Ticket23
Brief Description: Hanes tidy whiteys
Cause: 'Cause I said so
Impact: Wrench
Resolution: We don't wanna change the world

When: July 1 from 9:00 AM until 9:12 AM
Location: New Zealand
Business Unit: Foo
Priority: 1, Ticket34
Brief Description: Glibby glab
Cause: Because we like you!
Impact: Ouch
Resolution: Nafr nafr

Wednesday, July 2, 2008
When: July 2 from 8:00 AM until 8:12 AM
Location: Berwyn
Business Unit: CTU
Priority: 5, Ticket24
Brief Description: lsdjfkjds
Cause: XXXXXXXXX
Impact: XXXXXXXXX
Resolution: XXXXXXXXXXXXXXX

What's happening is the dddd, mm/dd/yyyy are appearing for every record. I'm attaching the pertinent section of code. Thanks for any help!

This topic has been closed for replies.

12 replies

Inspiring
July 24, 2008
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.
Inspiring
July 23, 2008
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!!
Inspiring
July 23, 2008
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?
Inspiring
July 23, 2008
Yeah, I think the query is fine, there's just something squirrely with my cf code.
Inspiring
July 23, 2008
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.
Inspiring
July 23, 2008
ChiTownJohnnyB wrote:
> The data from the message from 7/22 4:39 is the recordset that I get from the query

I ran a small test with ms sql and it seems to work perfectly. If your code and query are the same as what you posted here, I am not certain why it does would not work for you.







Inspiring
July 22, 2008
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.

Inspiring
July 22, 2008
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.
Inspiring
July 22, 2008
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
Inspiring
July 22, 2008
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?
Inspiring
July 22, 2008
Bob and cfSearching,
Thanks for the quick replies, but neither worked. Still getting the same output.

John