csteinhilber
Programmer
My mind is swimming...
I have a table that's doing some tracking of events. It comes out looking something like:
and I want the report to come out looking like:
seems fairly innucuous.
the report looks alright... except that it isn't sorted by start time since the query is actually sorted first by the process IDs (PID) - necessary, I think, to set up the group for my first loop.
So the above comes out looking like:
instead of the desired result (sorted by start time).
Can't figure out how to get it sorted the way I want.
We're probably looking at upwards of 30,000 records, so any manual sorting technique would be prohibitively slow, I think.
Doing a GROUP BY in the initial query is a possibility, but what I know about GROUP BY only involves producing some arithmetic calculation on a column... and that doesn't seem appropriate here.
Anyone have any ideas?
TIA!
-Carl
I have a table that's doing some tracking of events. It comes out looking something like:
Code:
PID TIME EVENT ...
03001 10:24:30 start ...
03001 10:24:34 end ...
00444 10:24:36 start ...
00444 10:24:37 end ...
00067 10:24:38 start ...
02567 10:24:39 start ...
02567 10:24:41 end ...
00067 10:24:42 end ...
and I want the report to come out looking like:
Code:
PROCESS START END ...
03001 10:24:30 10:24:34 ...
00444 10:24:36 10:24:37 ...
00067 10:24:38 10:24:42 ...
02567 10:24:39 10:24:41 ...
:
seems fairly innucuous.
Code:
<CFQUERY name="eventLog" ...>
SELECT *
FROM APPLOG
ORDER BY eid,time,event desc
</CFQUERY>
<table>
<CFOUTPUT query="eventLog" group="eid">
<tr>
<td>#eventLog.eid#</td>
<CFSET inTime = "">
<CFSET outTime = "">
<CFOUTPUT group="event">
<CFSWITCH expression="#lcase(eventLog.event)#">
<CFCASE value="start">
<CFSET inTime = eventLog.time>
</CFCASE>
<CFCASE value="end">
<CFSET outTime = eventLog.time>
</CFCASE>
:
</CFSWITCH>
</CFOUTPUT>
<td nowrap><span class="small">#inTime#</span></td><td nowrap><span class="small">#outTime#</span></td>
:
</tr>
</CFOUTPUT>
</table>
the report looks alright... except that it isn't sorted by start time since the query is actually sorted first by the process IDs (PID) - necessary, I think, to set up the group for my first loop.
So the above comes out looking like:
Code:
PROCESS START END
00067 10:24:38 10:24:42
00444 10:24:36 10:24:37
02567 10:24:39 10:24:41
03001 10:24:30 10:24:34
Can't figure out how to get it sorted the way I want.
We're probably looking at upwards of 30,000 records, so any manual sorting technique would be prohibitively slow, I think.
Doing a GROUP BY in the initial query is a possibility, but what I know about GROUP BY only involves producing some arithmetic calculation on a column... and that doesn't seem appropriate here.
Anyone have any ideas?
TIA!
-Carl