Help on a report please

In news:3ecf68d7$1@tnews.web.dev-archive.com,
Paul Summerfield <pjsummerfield@hotmail.com> typed:
> What I need to do is show on a report how long someone was in the
> building therefore find the first IN then there next OUT for that
> person and work out the duration and keep doing this for the day as
> they can go in /out all day

> IN 01/01/2003 12:00:00 OUT 01/01/2003 12:15:00 duration 15min
> IN 01/01/2003 12:20:00 OUT 01/01/2003 12:30:00 duration 10 min
> IN 01/01/2003 13:00:00 OUT 01/01/2003 18:00:00 duration 5 hours

Hi Paul,

You need to join the table to itself.

CREATE CURSOR Test (EmpID I, Status C(10), ClockTime T)
INSERT INTO Test VALUES (1, "In", DATETIME())
INSERT INTO Test VALUES (2, "In", DATETIME() + 10000)
INSERT INTO Test VALUES (1, "Out", DATETIME() + 20000)
INSERT INTO Test VALUES (2, "Out", DATETIME() + 30500)
INSERT INTO Test VALUES (1, "In", DATETIME() + 40000)
INSERT INTO Test VALUES (2, "In", DATETIME() + 50050)
INSERT INTO Test VALUES (1, "Out", DATETIME() + 65000)
INSERT INTO Test VALUES (2, "Out", DATETIME() + 75000)
INSERT INTO Test VALUES (1, "In", DATETIME() + 100000)
INSERT INTO Test VALUES (2, "In", DATETIME() + 100000)
INSERT INTO Test VALUES (1, "Out", DATETIME() + 200000)
INSERT INTO Test VALUES (2, "Out", DATETIME() + 305000)
INSERT INTO Test VALUES (1, "In", DATETIME() + 400000)
INSERT INTO Test VALUES (2, "In", DATETIME() + 500500)
INSERT INTO Test VALUES (1, "Out", DATETIME() + 650000)
INSERT INTO Test VALUES (2, "Out", DATETIME() + 750000)

SELECT ;
Test1.EmpID, ;
Test1.Status AS StatusIn, ;
Test1.ClockTime AS TimeIn, ;
Test2.Status AS StatusOut, ;
MIN(Test2.ClockTime) AS TimeOut, ;
MIN(Test2.ClockTime - Test1.ClockTime) AS TimeElapsed ;
FROM Test AS Test1 ;
INNER JOIN Test AS Test2 ON Test1.EmpID = Test2.EmpID AND ;
Test2.Status = "Out" AND ;
Test2.ClockTime > Test1.ClockTime ;
WHERE Test1.Status = "In" ;
GROUP BY Test1.EmpID, Test1.Status, Test1.ClockTime ;
ORDER BY Test1.EmpID, Test1.ClockTime

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden@mvps.org www.cindywinegarden.com