Identifying duplicate values within found set

[Chalkster]

I will try and explain this as simply as possible

I have a table with bookings for a travel agency. Each booking record contains a primary key field with unique values, an email contact address and a timestamp for when the booking was made

What I am trying to do for marketing and tracking purposes is to identify repeat clients within specified time periods.

Its easy enough to find bookings made within any time frame and any email addresses that occur more than once in the database.

What I am actually looking for in stage one is to take any specific date range eg Jan-June 2010 and find which clients made multiple bookings within that period (eg if they booked in Feb and again in March they should be included but not if they booked in March and August)).

For each email address I would like to know number of bookings and to gather the unique bookingIDs for each client.

I have thought of a couple of ways of doing this with scripting and using either a secondary table or creating a field to use as a flag as an additional relationship criteria , but because of the size of the table and number of records involved this would be a time consuming solution for each query. What I am looking for is if there is any straightforward simple method I am missing?

[comment]

Start by finding bookings that are within the date range AND have duplicates in the email field. Sort the found records by email. Use a summary field (Count of BookingID), placed in a sub-summary by email part, to count the bookings in each group.

[Chalkster]

Thanks comment - that does work to an extent but I guess I didnt explain what I was looking for properly - it doesnt exclude email addresses that only made a single booking during the selected date range, it just reports on how many bookings were made per email address in that date range

if client A made 5 bookings in total but only 3 were in the correct date range it returns 3

If client B made 7 bookings but none of them in the date range selected then B does not appear .... so far so good

But if client C made 4 bookings but only one of them in the selected date range C is returned with a count of 1

What I am actually trying to get (maybe I didnt explain myself properly first time around) is a found set that contains only those clients that made multiple booking within the date range searched for and omits any email addresses with less than 2 bookings made in that period, and to gather those unique BookingIDs into the result.

The reason I want need the actual BookingIDs is that from there we can see produce a clients history, whether he travels domestically or internationally, the spend per booking, number and type of passengers per booking, profit per booking etc. In order to find these I first need to know which customers made multiple bookings within a set period and to be able to identify those booking records. The ways I have been looking at so far are pretty time consuming because of the size of the data sets - there is an average of 500K booking records per year and the database covers a 5 yr history

If it is any use I have attached an empty copy of the tables I am using showing the relationships between them, but the starting point (using the table tsBooking) for the whole process is to identify only those email addresses (tsBooking::Email) creating multiple bookings within a selected date range (tsBooking::Created) and also to be able to list the ID (tsBooking:: ID) of each of those bookings per email address.

TSdata.fp7.zip

[comment]

only those clients that made multiple booking within the date range

Right you are - I should have seen that, sorry. This is not possible using only find. I believe the fastest method would be something like:

1. Find the records in the date range;

2. Sort by client, descending + Reorder based on summary field sCount;

3.

Loop 

Exit Loop If [ GetSummary ( Bookings::sCount ; Bookings::Client ) < 2 or Get (RecordNumber) =  Get (FoundCount) ] 

Go to Record [ Get (RecordNumber) + GetSummary ( Bookings::sCount ; Bookings::Client ) ] 

End Loop 

# 

If [ GetSummary ( Bookings::sCount ; Bookings::Client ) = 1 ] 

Omit Multiple Records [ Get (FoundCount) - Get (RecordNumber)  + 1 ] 

End If 

[Chalkster]

That worked - thanks