THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

www.SQLServerBible.com

Count of rows within five minutes of first instance

In the SSWUG Virtual conference, Earl asks,

Paul, I am looking @ several million records. In each record is a nvarchar that IDs the record and a datetime for when the transaction took place for that nvarchar ID. I need to capture the nvarchar ID when a number of the records for that nvarchar ID was within a time period of the other records with the same nvarcharID. It seems that a cursor is the best way to go through each record to verify a count for the nvarchar ID each time the record was within (5) minutes of the original record. Would you have a set oriented method for extracting these sets of records?

Here's your script Earl,

USE TempDB
CREATE TABLE T1 
(
        
T1ID INT NOT NULL IDENTITY PRIMARY KEY
,
        
RecordID NVARCHAR(50
) NOT NULL,
        
TransTime DateTime 
NOT NULL
  )
GO
INSERT T1 (RecordIDTransTime
)
    
VALUES ('abc''1/1/1980'
)
INSERT T1 (RecordIDTransTime
)
    
VALUES ('qwerty''1/1/1980'
)
GO 3
INSERT T1 (RecordIDTransTime
)
    
VALUES ('abc'GETDATE
())
INSERT T1 (RecordIDTransTime
)
    
VALUES ('qwerty'GETDATE
())
INSERT T1 (RecordIDTransTime
)
    
VALUES ('qwerty'GETDATE
())
INSERT T1 (RecordIDTransTime
)
    
VALUES ('xyz'GETDATE
()) 
WAITFOR delay '000:000:000.050'
GO 25 

WITH FirstRecord 
AS
  
(
  
SELECT RecordIDMIN(TransTimeAS 
FirstTime
    
FROM 
T1
    
GROUP BY RecordID
)
SELECT T1.RecordIDCOUNT(*) AS 
COUNT
  
FROM 
T1 
    
JOIN 
FirstRecord
      
ON T1.RecordID 
FirstRecord.RecordID
     
AND T1.TransTime <= DATEADD(n,1FirstTime
)
  
GROUP BY 
T1.RecordID

 

Published Thursday, June 26, 2008 1:52 PM by Paul Nielsen

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jon said:

Have you ever considered indenting your SQL?  It would make it much easier to read.

June 26, 2008 3:21 PM
 

Denis Gobo said:

Andy Leonard called me out on the Software Development Meme, so here goes: How old were you when you

July 1, 2008 1:03 PM

Leave a Comment

(required) 
(required) 
Submit

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog

Syndication

News

news item test
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement