Help with select multiple records by date range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with select multiple records by date range

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2012-01-11 : 12:44:00

I need to select records in a table where the Same user (userid) has downloaded the same document (docid) more than once within a 1 minute time range (insertdate - datetime column). In this example table data:

DownloadId InsertDate UserId DocId
1 2012-01-11 11:48:01.463 700 1500
2 2012-01-11 11:48:31.463 700 1500
3 2012-01-11 11:45:51.463 700 1700
4 2012-01-11 12:30:22.837 700 1700

This query would return the downloadids 1 and 2 since these are the only rows meeting the condition (same userid, docid and have been downloaded within a 1 minute range on the same date, time (hour, minute).

I'm gotten this far in my query, but it isnt returning the expected results and I also dont think it is checking the entire date properly.

SELECT DISTINCT t1.downloadid
FROM atest t1
INNER JOIN atest t2
ON t1.userid = t2.userid
AND t1.docid = t2.docid
AND datediff(MINUTE, t1.insertdate, t2.insertdate) = 0
group by t1.downloadid
HAVING COUNT(*) > 1

Thanks for any help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 12:56:47

[code]
SELECT DownloadId, InsertDate, UserId, DocId
FROM
(
SELECT *,COUNT(1) OVER (PARTITION BY UserId,DocId,CAST(InsertDate AS smalldatetime)) AS Cnt
FROM Table
)t
WHERE Cnt>1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ljp099
Yak Posting Veteran

79 Posts

Posted - 2012-01-11 : 13:06:45

Thanks.Your query isnt returning a result. How do I modify the query to allow it to query on date range (find all records with insert date within a one minute range for example)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 13:19:56

[code]
CREATE TABLE #temp
(
DownloadId int,
InsertDate datetime,
UserId int,
DocId int
)
INSERT #temp
SELECT 1, '2012-01-11 11:48:01.463', 700, 1500 UNION ALL
SELECT 2, '2012-01-11 11:48:31.463', 700, 1500 UNION ALL
SELECT 3, '2012-01-11 11:45:51.463', 700, 1700 UNION ALL
SELECT 4, '2012-01-11 12:30:22.837', 700, 1700

SELECT DownloadId, InsertDate, UserId, DocId
FROM
(
SELECT *,COUNT(1) OVER (PARTITION BY UserId,DocId,DATEADD(minute,DATEDIFF(minute,0,InsertDate),0)) AS Cnt
FROM #temp
)t
WHERE Cnt>1

DROP TABLE #Temp

output
-----------------------------------
DownloadId InsertDate UserId DocId
1 2012-01-11 11:48:01.463 700 1500
2 2012-01-11 11:48:31.463 700 1500

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ljp099
Yak Posting Veteran

79 Posts

Posted - 2012-01-11 : 13:37:25

Thanks again. Last issue I'm finding is the date diff. If I update the insertdate on downloadid = 2:

update temp set insertdate = '2012-01-11 11:49:00.463' where downloadid = 2

Then your query doesnt return a result, but I need to return results the insertdate is within 1 minute (60 second) range.

1 2012-01-11 11:48:01.463 700 1500
2 2012-01-11 11:49:00.463 700 1500

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 02:14:15

so you want anything within 60 s to be considered as one even if minute part value has changed?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ljp099
Yak Posting Veteran

79 Posts

Posted - 2012-01-12 : 11:50:47

Yes. Any record meeting the conditions of same userid, docid within a 60 second range is to be considered a duplicate record and is what I am trying to identify in my query. I tried changing your DateDiff to seconds, but get an overflow error.

DATEADD(minute,DATEDIFF(second,0,Download_Date__c),0))

throws: The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-12 : 12:48:26

quote:
Originally posted by ljp099

Yes. Any record meeting the conditions of same userid, docid within a 60 second range is to be considered a duplicate record and is what I am trying to identify in my query. I tried changing your DateDiff to seconds, but get an overflow error.

DATEADD(minute,DATEDIFF(second,0,Download_Date__c),0))

throws: The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

You are mixing SECONDS and MINUTES, that will not work. You could try this:

SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, Download_Date__c), 0)

But, are you asking to group the ROWS by 60 second intervals not just by minute? If so, how do you determine how to group them? What is the logic? Also, if the above date logic doesn't work, please post DDL, DML and expected output, per this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 13:06:44

do you mean this then?


SELECT t.*
FROM #temp t
OUTER APPLY (SELECT TOP 1 InsertDate 
             FROM #temp
             WHERE UserId = t.UserId
             AND  DocId = t.DocId
             AND InsertDate > t.InsertDate 
             ORDER BY InsertDate ) t1
OUTER APPLY (SELECT TOP 1 InsertDate 
             FROM #temp
             WHERE UserId = t.UserId
             AND  DocId = t.DocId
             AND InsertDate < t.InsertDate 
             ORDER BY InsertDate DESC) t2
WHERE DATEDIFF(ss,t.InsertDate,t1.InsertDate)<=60
OR   DATEDIFF(ss,t2.InsertDate,t.InsertDate)<=60

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ljp099
Yak Posting Veteran

79 Posts

Posted - 2012-01-12 : 16:31:55

That did it! Thanks for all your help. I wouldn't have bee able to put this query together.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 04:39:58

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources