Comparing the dates on multiple rows

Question

Post reply

Comparing the dates on multiple rows

tmitchelar

Hall of Fame

Points: 3391
More actions
June 12, 2012 at 8:35 am

#257787

Lets say I have 5 records with a sell date. I want to show a count of all the records with a sell date within 3 minutes of that record. I was thinking I'd need a recursive join - but have never used one. Can someone give me some pointers? So for example:
TransactionID | SellDateTime | Count of Sells occurred within 3 minutes
1 | 6/12/2012 9:30 AM | 2
2 | 6/12/2012 9:30 AM | 1
3 | 6/12/2012 9:32 AM | 2
4 | 6/12/2012 9:34 AM | 2
5 | 6/12/2012 9:36 AM | 1
Any ideas on how to accomplish this? Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

What about providing something like this:

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Ken McKelvey SSCoach Points: 18484 More actions · Answer 2

I do not understand your results, but the following should get you started:

-- *** Test Data ***

CREATE TABLE #t

(

TransactionId int NOT NULL

,SellDateTime datetime NOT NULL

);

INSERT INTO #t

VALUES (1, '20120612 09:30')

,(2, '20120612 09:30')

,(3, '20120612 09:32')

,(4, '20120612 09:34')

,(5, '20120612 09:36');

-- *** Test Data ***

SELECT *

FROM #t T1

CROSS APPLY

(

SELECT COUNT(*) AS NoSells

FROM #t T2

WHERE T2.SellDateTime BETWEEN T1.SellDateTime AND DATEADD(minute, 3, T1.SellDateTime)

) D;

tmitchelar Hall of Fame Points: 3391 More actions · Answer 3

Here you go. So I want to go through all the dates and figure out how many other dates happened within a set timeframe - 3 minutes - for example.

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable

(

ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table

DateValue DATETIME,

)

--===== Setup any special required conditions especially where dates are concerned

SET DATEFORMAT DMY

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table

INSERT INTO #mytable

(ID, DateValue)

SELECT '1','Jun 12 2007 9:30AM' UNION ALL

SELECT '2','Jun 12 2007 9:30AM' UNION ALL

SELECT '3','Jun 12 2007 9:32AM' UNION ALL

SELECT '4','Jun 12 2007 9:34AM' UNION ALL

SELECT '5','Jun 12 2007 9:36AM'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT #mytable OFF

SELECT * FROM #mytable

tmitchelar Hall of Fame Points: 3391 More actions · Answer 4

Ken - that's exactly what I was trying to do - thank you very much.