Record Combination

[stanley1610]

I have a table in this format:

 

Name: Varchar

Food: Varchar

Date: Date

 

There's no primary key.

e.g. Sam eats icecream on 19 Sep 2007, then the record will store

Sam, icecream, 19 Sep 2007.

 

If the case:

Sam, icecream, 19 Sep 2007.

Susan, icecream, 20 Sep 2007.

Jacky, icecream, 21 Sep 2007.

 

 

Then Susan and Jacky did "repeat eat" because Susan and Jacky repeat the same food "icecream" as Sam within 14 days.

 

My report (in JasperReports) will draw how many repeat eat there is. The result will look like:

 

Report A -

FOOD, NUMBER OF REPEAT

icecream, 2

orange, 3

 

Report B -

NAME, FOOD NUMBER OF REPEAT

Sam, biscuit, 1

Sam, orange, 1

Susan, biscuit, 1

 

The most critical challenge is the number of records in the table around 20,000. If combining them directly, it will be around 400,000,000 records in immediate table.

 

How can I do the reports? Many thanks.

[csbac]

Hi!

Use GROUP BY in the SQL clause(s), I'd say ...

Sebastian