Hi,

For a reservation system, I would like to be able to highlight those reservations where the dates and times are duplicates, so those confirming the reservations will easily see double-bookings. So, for the following list of dates & times

Jan 1, 10-3
Jan 2, 4-5
Jan 1, 10-3
Jan 5, 6-7
Jan 1, 3-5

I would like to highlight the two "Jan 1, 10-3" records. I can't figure out how to idenfity the duplicates. I'm outputting these results from a query. Each record has a primary key. The database is SQL Server.

Any help would be much appreciated.

Thank you.

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!