Hi again,

I figured out a way to do this, but it seems like such a long-winded way to go about it. If anyone has any way to make this neater, please let me know.


I. First, I concatenate the date and times for each event into a single string.

<cfquery name= "qry1" datasource = "blah">
Select CAST(eventtime AS CHAR(10)) + CAST(eventtdate AS CHAR(10)) AS "everything" , originalid
from mytable
</cfquery>

II. Then I select the records that have a count > 1:

<cfquery name = "qry2" dbtype = "query">
SELECT qry1.everything, Count(qry1.everything) AS CountOfEverything
FROM qry1
GROUP BY qry1.everything
HAVING Count(qry1.everything)>1
</cfquery>

III. Then, I get the id of those records where count is >1
<cfquery name = "qry3" dbtype = "query">
SELECT qry1.originalid as newid
FROM qry1, qry2
WHERE qry1.everything = qry2.everything
GROUP BY qry1.originalid
</cfquery>

IV. Then, I loop through my original query that outputs all dates, and test for the id
<cfloop query="Getevent">
<cfoutput>
<cfquery name = "qry4" dbtype = "query">
select newid
from qry3
where qry3.newid = #originalid#
</cfquery>

Then I use an if statement to find where newid = originalid.

There's got to be a better way!