How to find double records

robleer

Hi,

I have a BO which stores appointsments for a user.

I want to find out if there are double appointments. This is not prohibited, but I want to send the user a message to inform him about possible conflicts in his agenda.

What is the best way to find (and show) two or more records on the same date?

In SQL I would do it something like this:

SELECT DISTINCT Count(*) From Appointments WHERE Appointments.Date=Appointments.Date AND Appointments.ID<>Appointments.ID

aware_support

FIND Appointment WHERE COUNT Appointment WHERE (Appointment.Date=ThisAppointment.Date) > 0

aware_support

Correction:

FIND Appointment WHERE COUNT Appointment WHERE (Appointment.Date=ThisAppointment.Date AND Appointment.ID<>ThisAppointment.ID) > 0

robleer

Ok, I will try that.

Thanks

robleer

I tries it but this is not exactly what I'm trying to achieve.

I want a list with a number of double planned appointments and then inform the user about it.

To start I need a list like something this:

Date: Number of appointments:

30-11-2011 4
01-12-2011 2
02-12-2011 3
03-01-2012 2
a.s.o.

aware_support

You need to store the count in an attribute and define a rule that calculates it:

Appointment.DoubleCount = COUNT Appointment WHERE EXISTS (Appointment WHERE (Appointment.Date = ThisAppointment.Date AnD Appointment.ID<>ThisAppointment.ID)

Then use the query in the previous message to display the list

robleer

Nah, that results in a strange list no one understands because every appointment shows how many doubles there are.

The list will look like this

30-11-2011 4
30-11-2011 4
30-11-2011 4
30-11-2011 4
01-12-2011 2
01-12-2011 2
02-12-2011 3
02-12-2011 3
02-12-2011 3
03-01-2012 2
03-01-2012 2

I want to show that there are 2 or more appointments on one day. Shouldn't be to difficult, but how can this be done??

aware_support

Implement the query as a stored procedure and use DISTINCT

jacklcs

"Implement the query as a stored procedure and use DISTINCT"

Using AwareIM Query? How? I wrote on the SQL Statement it show error already....may be I did wrong..

please advice further...

tford

Search the User Guide for "EXEC_SP Action"