x

select first row when duplicate rows are available

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.

more ▼

asked Dec 16 '09 at 02:22 PM in Default

Shiyam gravatar image

Shiyam
15 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Dec 16 '09 at 02:37 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

Thank you Mr. Riley. This works perfect. I appreciate your time and help. Many Many Thanks
Dec 16 '09 at 03:02 PM Shiyam
Nice work Kev. I thought I had a better answer and then, by the time I'd fixed my mistakes I realised I got back to where you were in the first place!
Dec 17 '09 at 06:26 AM David Wimbush
Thank you. I could have made it slicker, but I like to show people how to do it almost step by step, so 'get the arrive times', 'get the leave times', join them, and hey presto! It's how I learned to do SQL - break it down into chunks then once you've got an answer, tune it! I'm sure there's a one-line CTE cross apply fancy-schmancy solution, but I feel that the OP could learn more from this.
Dec 17 '09 at 06:57 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x472

asked: Dec 16 '09 at 02:22 PM

Seen: 2949 times

Last Updated: Dec 16 '09 at 02:22 PM