?
Solved

Self Join query to ID a pattern fo data Needed

Posted on 2009-04-01
5
Medium Priority
?
246 Views
Last Modified: 2012-05-06
I have a table that has three fields.
acct_nbr
theID
datetime to the second.

I need a query that will allow me to find a pattern where sequential acct_nbr's numbers have been accessed by the same theID based on datetime to the second. Listed below is an example of the data. It does not represent the pattern I am trying to find.  While I can sort by acct_nbr,datetime, and theID I can not bring the required pattern to the forefront.  There are 20 millions records in the table.

All help is greatly appreciated.

Data looks like
acct_nbr                     datetime                                      theID
123911111      2007-12-28 23:01:54.000      206936
133333333      2007-12-28 22:19:15.000      969023
644444444      2007-12-28 23:00:28.000      999633
155555555      2007-12-29 08:10:40.000      590635

0
Comment
Question by:frogman22
[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
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24041985
So the access has to take place during the SAME second?  Your requirement is unclear:
"sequential acct_nbr's numbers have been accessed by the same theID based on datetime to the second"


;with AccountsAccessed as
(select theid,[datetime] as tDate,acct_nbr
from SomeTable b
join
(select theid,[datetime]
from SomeTable
group by theid,[datetime]
having count(*)>1
)a
on a.theid=b.theid and b.[datetime]=c.[datetime]
)
select * from AccountsAccessed a1
join AccountsAccessed a2
on a1.theid=a2.theid
and a1.tdate=a2.tdate
and a1.acct_nbr=a2.acct_nbr-1

0
 

Author Comment

by:frogman22
ID: 24042365
Sorry for the confusion. I meant "to the second "in regards to sequential order.  Does the query sample need to be modified
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24042595
Try this

select a1.theid, a1.acct_nbr, a2.acct_nbr, a1.[datetime]
from YourTable a1 inner join YourTable a2
   on a1.theid = a2.theid and a1.[datetime] = a2.[datetime] and a1.acct_nbr = a2.acct_nbr+1
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 24042611
based on your last post this should work

select a1.theid, a1.acct_nbr, a2.acct_nbr, a1.[datetime]
from YourTable a1 inner join YourTable a2
   on a1.theid = a2.theid and a1.[datetime] = a2.[datetime]
0
 

Author Comment

by:frogman22
ID: 24042962
It is running. We will see what the results are:-)
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

770 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