duplicate rows issue 1

[VinShiyam]

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.

 

[gmmastros]

What version of SQL Server?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[VinShiyam]

Version 2000.
Version 2005 would work as well.

Thank you.

 

[gmmastros]

In this case, there will be a big difference in the syntax because SQL2005 added some windowing functions (which is what you really need for this query).


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

The SQL2005 version:

Declare @Temp Table(ActionDateTime DateTime, Action VarChar(20), ClientId VarChar(20))

Insert Into @Temp Values('2009-12-16 08:55:45.554','Arrive','abc')
Insert Into @Temp Values('2009-12-16 14:25:33.519','Leave','abc')

Insert Into @Temp Values('2009-12-16 15:55:45.554','Arrive','abc')
Insert Into @Temp Values('2009-12-16 17:25:33.519','Leave','abc')

;With Blah As
(
    Select *, Row_Number() Over(Partition By ClientId, DateDiff(Day, ActionDateTime, 0) Order By ClientId, ActionDateTime) As RowNumber
    From   @TEmp
    Where  Action In ('Arrive','Leave')
) 
Select A.ClientId, 
       A.ActionDateTime As ArriveTime, 
       B.ActionDateTime As LeaveTime, 
       DateDiff(Minute, A.ActionDateTime, B.ActionDateTime)
From   Blah As A
       Inner join Blah As B
         On  A.ClientId = B.ClientId
         And (A.RowNumber -1) / 2  = (B.RowNumber -1) / 2 
         And A.Action = 'Arrive'
         And B.Action = 'Leave'

Notice that this uses an @Temp table variable. This is so that I could test the functionality. If this works for you, you'll need to modify it to use your tables instead.

In the previous query, you'll see that each visit has it's own row in the results. You can modify this slightly so that it sums the minutes for each day, like this:

Declare @Temp Table(ActionDateTime DateTime, Action VarChar(20), ClientId VarChar(20))

Insert Into @Temp Values('2009-12-16 08:55:45.554','Arrive','abc')
Insert Into @Temp Values('2009-12-16 14:25:33.519','Leave','abc')

Insert Into @Temp Values('2009-12-16 15:55:45.554','Arrive','abc')
Insert Into @Temp Values('2009-12-16 17:25:33.519','Leave','abc')

;With Blah As
(
    Select *, Row_Number() Over(Partition By ClientId, DateDiff(Day, ActionDateTime, 0) Order By ClientId, ActionDateTime) As RowNumber
    From   @TEmp
    Where  Action In ('Arrive','Leave')
) 
Select	A.ClientId, 
        DateAdd(Day, DateDiff(Day, 0, A.ActionDateTime), 0), 
        Sum(DateDiff(Minute, A.ActionDateTime, B.ActionDateTime))
From    Blah As A
        Inner join Blah As B
          On A.ClientId = B.ClientId
          And (A.RowNumber -1) / 2  = (B.RowNumber -1) / 2 
          And A.Action = 'Arrive'
          And B.Action = 'Leave'
Group By A.ClientId, DateAdd(Day, DateDiff(Day, 0, A.ActionDateTime), 0)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[VinShiyam]

Hi George,

Thank you so much for your help. This works perfect.

I really appreciate your time and effort to help me out.

Many Many Thanks.

Shiyam

 

[gmmastros]

You're welcome.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom