Generating SQL report

[csteinhilber]

My mind is swimming...

I have a table that's doing some tracking of events. It comes out looking something like:

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:

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.

<CFQUERY name=&quot;eventLog&quot; ...>
	SELECT * 
	  FROM APPLOG
	 ORDER BY eid,time,event desc
</CFQUERY>

<table>
<CFOUTPUT query=&quot;eventLog&quot; group=&quot;eid&quot;>
<tr>
<td>#eventLog.eid#</td>

<CFSET inTime = &quot;&quot;>
<CFSET outTime = &quot;&quot;>
<CFOUTPUT group=&quot;event&quot;>
<CFSWITCH expression=&quot;#lcase(eventLog.event)#&quot;>
<CFCASE value=&quot;start&quot;>
	<CFSET inTime = eventLog.time>
</CFCASE>
<CFCASE value=&quot;end&quot;>
	<CFSET outTime = eventLog.time>
</CFCASE>
          :
</CFSWITCH>
</CFOUTPUT>

<td nowrap><span class=&quot;small&quot;>#inTime#</span></td><td nowrap><span class=&quot;small&quot;>#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:

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

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

 

[r937]

if you have 30,000 records, even assuming you combine start and end records, this means 15,000 lines printed to the web page? whoa

how good/decent is the data? does each start have a matching end? does any process have multiple starts without matching ends in between?

you might consider a self-join in the database, thuse you can return both the start and end times for each process, and therefore sort by start time...

and then the CF code becomes simple (if not trivial)...

and then you can use query results caching...

and then it becomes a lot easier to add Next/Prev logic so you don't print 15,000 records...

rudy

 

[csteinhilber]

Does each process have matching start/ends? Of course not, Rudy ;-) Jeesh... what do you think this is, nirvana?

And, wouldn't you know it... it's just a CSV file that I'm trying to read via the Merant Text Driver... so I'm afraid any self-joins "in the database" are beyond the technology.

Anyway to do the join on the initial query, then depend on Query-on-query to produce the sorted (sordid?) results? I've never done a self-join (which I assume is joining a table to itself).


-Carl

 

[r937]

somebody, can't think who, in another thread, mentioned a technique for simulating a query, using QueryNew, QueryAddRow, etc.

i've never used it myself but this seems a great example where you might want to

so after your eventlog query has returned the data, loop through it like you're doing now, and instead of outputting the matched rows, load them into a new query

then use query-of-query to get what you want in timestamp

yes, a self-join is a join of the table to itself -- but in this case you probably want that control loop, because after all, it'll have all sorts of error checking to catch those non-nirvana situations i mentioned (starts without a matching end, multiple starts without matching ends in between, etc.)

rudy


i
not sure

 

[rlarson]

If your interested a self join on this table would like like:

SELECT a1.eid, a1.time as start, a2.time as end
FROM APPLOG a1, APPLOG a2
WHERE a1.eid = a2.eid
ORDER BY time

 

[rlarson]

Correction

SELECT a1.eid, a1.time as start, a2.time as end
FROM APPLOG a1, APPLOG a2
WHERE a1.eid = a2.eid
AND a1.event = 'start'
AND a2.event = 'end'
ORDER BY time