[SQL] Group by Transaction within an hour

[Remote]

I am trying to track when a user calls in to change enrollment information with the help of a staff member.

I am trying to write a query that will track calls made. In a single call, there can be multiple transactions performed. I have a transaction table which I need to count unique calls.

I am having trouble figuring out how to group these transaction. I want to group by transactions per customer within an hour.

For example, transaction for performed by one staff member made to a single customer could look like this. There are 3 unique calls for this customer

STAFF MEMBER | CUSTOMER| TRANSACTION DATE

JIM | JON| 2008-07-02 09:02:18.000 -- 1st call

JIM | JON| 2008-07-02 09:02:20.000 -- 1st call

JIM | JON| 2008-07-02 09:02:22.000 -- 1st call

JIM | JON| 2008-07-02 09:03:19.000 -- 2nd call (hour after first transaction of 1st call)

JIM | JON| 2008-07-02 09:04:22.000 -- 2nd call (hour after first transaction of 2nd call)

any thoughts on how the group would work so I get back only the first transactions of the unique call?

[+Nik Louch Subscriber²]

Convert the date to a string and use a substring?

[ramesees]

Are those the only fields in that table?

There doesnt seem to be enough unique information that would be needed to break down the data further into 1st call, 2nd call etc.. or what action was performed. Without this there is no way of knowing whats a first call and whats a subsequent call - its just a list of times in a table with no contextual info

[medium_pimpin]

As stated above, convert the date/time to varchar and parse out the hours with a substring function.

substr(Trans_Date,12,2)

[Remote]

  ramesees said:

Are those the only fields in that table?

There doesnt seem to be enough unique information that would be needed to break down the data further into 1st call, 2nd call etc.. or what action was performed. Without this there is no way of knowing whats a first call and whats a subsequent call - its just a list of times in a table with no contextual info

There is more information available, I was just trying to simplify with the example.

The main goal is to charge the client for customers that want to change their enrollment via phone. Since they can change multiple things during one call, I want to use that hour timeframe from the first and last transaction to identify a single call. But a customer could call more then one time a day and speak with the same rep.

I will try the substring, not sure if that will work or how to work it

[James Rose]

Actually you want to look at the function: DATEPART

Here is the SQL Server info on that function. If you are using Oracle, Sybase, MySQL they have this sort of function too.

http://msdn.microsoft.com/en-us/library/aa...65(SQL.80).aspx