duplicate rows problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

duplicate rows problem

Author

Topic

Shiyam
Starting Member

2 Posts

Posted - 2009-12-16 : 12:30:47

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.

Thank you,

Shiyam

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-16 : 13:07:08

Something like this?

declare @freq table (ActionDateTime datetime, Action varchar(50), ClientID varchar(50))
insert @freq
          select '20091216 08:55:45.554', 'Arrive', 'abc'
union all select '20091216 14:25:33.519', 'Leave', 'abc'
union all select '20091216 08:55:45.554', 'Arrive', 'xyz'
union all select '20091216 14:25:33.519', 'Leave', 'xyz'
union all select '20091216 15:55:45.554', 'Arrive', 'abc'
union all select '20091216 16:25:33.519', 'Leave', 'abc'
union all select '20091217 15:55:45.554', 'Arrive', 'abc'
union all select '20091217 16:25:33.519', 'Leave', 'abc'

select a.ClientID, b.ActionDay, 
    datediff(minute, max(case when Action = 'Arrive' then ActionDateTime end),
                     max(case when Action = 'Leave' then ActionDateTime end)) as FirstStayDurationInMinutes
from @freq a inner join (
    select ClientID, ActionDay, min(ActionDateTime) as FirstLeave from (
        select *, dateadd(day, datediff(day, '19000101', ActionDateTime), '19000101') as ActionDay from @freq) a
    where Action = 'Leave'
    group by ClientID, ActionDay) b
    on a.ClientID = b.ClientID and ActionDateTime between ActionDay and FirstLeave
group by a.ClientID, b.ActionDay

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Shiyam
Starting Member

2 Posts

Posted - 2009-12-16 : 15:39:20

Hi Ryan,

Thank you so much for your time and effort. I really appreciate your help. I've posted the same question on a different forum and received a similar answer. Great Minds think alike. :)

Thanks again.

Shiyam

Thank you,

Shiyam

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-17 : 05:38:24

Thanks Shiyam

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources