SQL, join table on itself, order by created within a certain timespan
I have created a table with 4 columns and sorted on a big amount of dataID:
ID Type Item Date AU761201 12.37.47 121212322 2012-07-12 22:21:12 AU761201 12.37.42 157856388 2012-05-12 23:21:05 AU761201 12.37.42 164687782 2009-02-12 12:37:24 BD871015 15.31.62 651564488 2007-10-25 15:12:23 DU551023 19.35.14 048040408 2011-11-05 14:15:23 DU551023 19.35.14 056505654 2011-11-04 23:23:23 . . . . . . . . . . . . . . . . . . . .
What I want to do is to examine if any “ID” has bought two identical goods (same “Type” number) within the timespace of 24hours (DU 551023 in the example given)and sort out the IDs for which this applies.
My brain is currently bleeding from dealing with this som all help is deeply appreciated.
3 Solutions collect form web for “SQL, join table on itself, order by created within a certain timespan”
SELECT T1.id FROM TableName T1 INNER JOIN TableName T2 ON T1.id = T2.id AND T1.Type = T2.Type AND T1.Item > T2.Item WHERE ABS(HOUR(TIMEDIFF(T1.date, T2.date))) <= 24
For SQL Server
SELECT T1.id FROM TableName T1 INNER JOIN TableName T2 ON T1.id = T2.id AND T1.Type = T2.Type AND T1.Item > T2.Item WHERE ABS(DATEDIFF(hh, T1.date, T2.date)) <= 24
First, you need an unique key in this table (something to distinguish to records). Suppose it is Item (otherwise change it in T1.Item != T2.Item).
select distinct T1.ID from Table T1, Table T2 where T1.ID = T2.ID and T1.Type = T2.Type and Abs(T1.Date - T2.Date) < 1 and T1.Item != T2.Item order by T1.ID
if its SQL server then you can do it with exists
SELECT DISTINCT a.ID FROM MyTable AS a WHERE EXISTS ( SELECT 1 FROM MyTable AS b WHERE a.ID = b.ID AND a.TYPE = b.Type AND ABS(DATEDIFF(hour, a.Date, b.Date)) <= 24 ) ORDER BY a.ID
or you can use join
SELECT DISTINCT a.ID FROM MyTable AS a INNER JOIN MyTable AS b ON a.ID=b.ID AND a.TYPE=b.Type WHERE ABS(DATEDIFF ( hour , a.Date , b.Date ))<=24 ORDER BY a.ID