Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stuck on a query with row wise calculation.....

Posted on 2007-04-04
7
Medium Priority
?
353 Views
Last Modified: 2012-06-27
i m trying to figure out instances from a table where two rows are inserted in less than 5 mins apart for a same customer by a different user. i have tried doing the grouping by but if the same customer was called later on during the day then it gets out of that condition....... following is the sceario that would explain it more clearly....


id        customerid          dateTime                  user
1            10                    today 9:00 am           2
2            10                    today 9:02 am           3
3            15                    today 9:05 am           6
4            15                    today 9:06 am           6
5            10                    today 9:30 am           3


In the above example the query should only bring up customer id 10.... as there was atleast one instance where there are two records less than 10 minutes apart by two different users.....  If i use group by clause and go with Max and min values comparing them to be less than 10 minutes apart for the above data set i will get empty recordset....as customerid 15 was less than 10 minutes apart but was by the same user......

i hope i explained it okay...
thanks in advance...
0
Comment
Question by:PremkumarBalwani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 10

Expert Comment

by:ksaul
ID: 18851533
I think it will take an self-join like:

SELECT *
FROM YourCustomerTable c1
INNER JOIN YourCustomerTable c2 ON c1.customerid = c.customerid
WHERE ABS(DATEDIFF(minute,c1.dateTime, c2.dateTime)) > 5
AND c1.user <> c2.user
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 18851563
i do hope u do not have a column called datetime - this is a SQL reserved word and u can get into all sorts of trouble.

try
Select
c1.id,
c1.customerid,
c1.[dateTime]
c1.user
from
Customers  c1
inner join
customers c2
on
c1.customerid = c2.customerid
and
datediff(minute,c1.[datetime],c2.[datetime]) < 5
and
c1.user <> c2.user

/Richard
0
 
LVL 14

Expert Comment

by:mherchl
ID: 18851582
try this:

select distinct a.customerid
   from yourtable a
   join yourtable b on a.customerid = b.customerid and a.user <> b.user and datediff(s, a.datetime, b.datetime) <= 60*5
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 6

Expert Comment

by:DocGyver
ID: 18851642
This query could be very expensive against a table with a lot of rows.  Most likely you should add a WHERE clause to limit the scope to a given day but keep in mind if you do that you need to account for the event happening over a day boundary.  Adding something like:

WHERE DateTime > DateAdd(DD, -1, getdate())

will only look for the event in the last 24 hours

select
FROM
     MyTab T1
     JOIN MyTab T2 ON
         T1.ID <> T2.ID AND T1.CUSTOMERID = T2.CUSTOMERID AND
         ABS(T1.DateTime - T2.DateTime) < 300 AND
         T1.USER <> T2.USER
0
 
LVL 1

Author Comment

by:PremkumarBalwani
ID: 18851648
Hello,
thank you all for your solutions... is there any way i can get around without having to do a self join.... i say this because its a huge, huge table as it records history of every transaction and doing a self join would just be disastrous.....

thanks,
0
 
LVL 23

Accepted Solution

by:
Christopher Kile earned 1000 total points
ID: 18852023
Frankly, this is more of an issue for a trigger.  Add a field called posted_within_5min BIT to your recordset and default it to 0.  Assume your table name is Table1:

CREATE TRIGGER ON Table1
AFTER INSERT
AS

DECLARE @id INTEGER, @customerid INTEGER
DECLARE @datetime DATETIME, @user INTEGER

DECLARE c1 READ-ONLY CURSOR FOR
SELECT
id,
customerid,
[datetime],
[user]
FROM
inserted

OPEN c1
FETCH NEXT FROM c1 INTO
      @id, @customerid, @datetime, @user
WHILE @@FETCH_STATUS = 0
BEGIN
      IF EXISTS(
            SELECT * FROM Table1
            WHERE
            customerid = @customerid
            AND
            [user] <> @user
            AND
            DATEDIFF(mi, [datetime], @datetime) < 5
            )
      BEGIN
            -- Update the master table as the
            -- insert has already taken place
            UPDATE Table1 SET
                  posted_within_5min = 1
            WHERE
                  id = @id
      END
      FETCH NEXT FROM c1 INTO
            @id, @customerid, @datetime, @user
END      


This trigger is good for one insertion or a batch insertion.  Old records can be marked using a batch update process, which will be long but will have to happen only once. Your query then becomes:

SELECT
*
FROM Table1
WHERE
posted_within_5min <> 0
0
 
LVL 1

Author Comment

by:PremkumarBalwani
ID: 18854041
thanks cpkilekofp!!

although at present i used the other solutions as a quick fix, but i will implement the trigger when i get a chance just coz its more clean and does not require huge database query everytime you run it. will just have to see how the trigger affects the performance of the data access and write in a work environment.

thank you all for your ideas...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question