I have a table "freq" with the following fields: ActionDateTime, Action, ClientID.
Each of our client's action is recorded in this table. The first row for a client will be:
"2009-12-16 08:55:45.554", "Arrive", "abc".
here the datetime indicates the date and time the action(arrive) was performed. (So client "abc" arrived in the store on Dec 16 2009 @ 8:55:45).
The last row for the same client will be as follows: "2009-12-16 14:25:33.519","Leave","abc".
I need to create a query which displays all clients and the duration of their stay in the store. ((ActionDateTime when Action="Leave") - (ActionDateTime when Action = "Arrive")) I was able to figure this part out.
The problem is when the same client visits the store more than once on the same day.In other words the same ClientID has more than one "Arrive" and "Leave" in the "Action" field. When I have duplicate ClientIDs I need to query only their first visit, and ignore all other subsequent visits. I've tried using min and subqueries, but i am not getting the correct results.
Could you please help me find a solution for this problem?
I appreciate any input.
asked Dec 16, 2009 at 02:22 PM in Default
here's one (quickly thrown together and not very elegant) way:
answered Dec 16, 2009 at 02:37 PM
Kev Riley ♦♦