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

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
New Comments to this post are disabled

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
Privacy Statement