Problems with Calculation on same column

[pippotim]

I have a MySQL DB with a Table like this:

User datetime event count
_____________________________________________

BIll 03/04/2004 9:00 AM IN 1
. . . .

BIll 03/04/2004 1:00 PM OUT 5
. . . .

BIll 03/04/2004 2:00 PM IN 10
. . . .
. . . .

BIll 03/04/2004 5:00 PM OUT x


(there are two or more records per day per User)


I will Report:

BIll 03/04/2004 7 (Hours IN)
Ann 03/04/2004 5 (Hours IN)
..........................

How can I do it?

 

[nimmy99]

You can do it using formula

 

[pippotim]

Can You Explain it Better, Please?
I know I must use a formula.

 

[blom0344]

You probably won't be able to pull this off with a formula.

The database-table you are reporting on is not very suited for analyses with a SQL-based tool. The reason is that you want to perform calculations over seperate records.

Normally you would use a cursor-type tool, like processing the data with a procedure in the database (like PL/SQL in ORACLE). In very simple cases you may create a de-normalized view on the table (which will only work with a fixed number of entries per person per day anyway)

This is not a simple matter of applying a formula!!!
(but I stand to be corrected!!)

T. Blom
Information analyst
tbl@shimano-eu.com