select first row when duplicate rows are available

Question

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.

Answer 1

here's one (quickly thrown together and not very elegant) way:

declare @freq table 
  (ActionDateTime datetime, Action varchar(50), ClientID varchar(50))

insert into @freq
select '2009 dec 16 08:55:45.554', 'Arrive', 'abc'
union select  '2009 dec 16 09:30:45.554', 'Leave', 'abc'
union select  '2009 dec 16 09:55:45.554', 'Arrive', 'abc'
union select  '2009 dec 16 10:55:45.554', 'Leave', 'abc'
union select  '2009 dec 17 09:55:45.554', 'Arrive', 'abc'
union select  '2009 dec 17 10:55:45.554', 'Leave', 'abc'


select
    arrive.clientid,
    datediff(mi,arrive.Arrive,leave.leave) as DurationInMins
from (
    select
    	convert(datetime, convert(varchar, actiondatetime,103),103) as DayDate,
    	min(actiondatetime) as Arrive,
    	clientid
    from @freq
    where action = 'Arrive'
    group by
    	convert(datetime, convert(varchar, actiondatetime,103),103),
    	clientid
    ) arrive
join
    (	
select
    convert(datetime, convert(varchar, actiondatetime,103),103) as DayDate,
    min(actiondatetime) as Leave,
    clientid
from @freq
where action = 'Leave'
group by
    convert(datetime, convert(varchar, actiondatetime,103),103),
    clientid
    ) leave
    on arrive.daydate = leave.daydate and arrive.clientid = leave.clientid

select first row when duplicate rows are available

Follow this question

Related Questions