Query question - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Query question

Author

Topic

dbchud
Starting Member

13 Posts

Posted - 2009-08-17 : 10:54:59

I have a table which contains an id, a name and a datetime.

My table is a "log" for everything that happens on accounts (the history for each account is held in this table). The table has millions of rows.

Example data from the table…

ID Name Datetime
1 Name1 2009-06-19 08:00:00.000
2 Name2 2009-06-19 09:00:00.000
3 Name3 2009-06-19 08:30:00.000
4 Name4 2009-06-19 08:30:00.000
5 Name5 2009-06-19 08:30:00.000
6 Name1 2009-06-19 08:00:00.000
7 Name2 2009-06-19 09:00:00.000
8 Name3 2009-06-19 08:00:00.000
9 Name4 2009-06-19 08:00:00.000
10 Name5 2009-06-19 08:00:00.000
11 Name1 2009-06-19 09:00:00.000
12 Name2 2009-06-19 08:00:00.000
13 Name3 2009-06-19 08:00:00.000
14 Name4 2009-06-19 08:00:00.000
15 Name5 2009-06-19 08:00:00.000

So I am trying to find out all of the accounts where Name1 has been entered into the table after a certain date and where Name2 has been entered into the table within one hour of Name1.

BTW there could be several other ids between the two different names.

Hope that makes some form of sense? Would appreciate any help/ thoughts.

Cheers
Gary :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 11:05:56

[code]DECLARE @Sample TABLE
(
ID INT,
Name VARCHAR(20),
[Datetime] DATETIME
)

INSERT @Sample
SELECT 1, 'Name1', '20090619 08:00' UNION ALL
SELECT 2, 'Name2', '20090619 09:00' UNION ALL
SELECT 3, 'Name3', '20090619 08:30' UNION ALL
SELECT 4, 'Name4', '20090619 08:30' UNION ALL
SELECT 5, 'Name5', '20090619 08:30' UNION ALL
SELECT 6, 'Name1', '20090619 08:00' UNION ALL
SELECT 7, 'Name2', '20090619 09:00' UNION ALL
SELECT 8, 'Name3', '20090619 08:00' UNION ALL
SELECT 9, 'Name4', '20090619 08:00' UNION ALL
SELECT 10, 'Name5', '20090619 08:00' UNION ALL
SELECT 11, 'Name1', '20090619 09:00' UNION ALL
SELECT 12, 'Name2', '20090619 08:00' UNION ALL
SELECT 13, 'Name3', '20090619 08:00' UNION ALL
SELECT 14, 'Name4', '20090619 08:00' UNION ALL
SELECT 15, 'Name5', '20090619 08:00'

SELECT n1.ID,
n1.[Datetime],
n2.ID,
n2.[Datetime]
FROM (
SELECT [Datetime],
DATEADD(HOUR, 1, [Datetime]) AS dt,
ID
FROM @Sample
WHERE Name = 'Name1'
) AS n1
INNER JOIN (
SELECT [Datetime],
ID
FROM @Sample
WHERE Name = 'Name2'
) AS n2 ON n2.[Datetime] >= n1.[Datetime]
AND n2.[Datetime] < n1.dt[/code]

N 56°04'39.26"
E 12°55'05.63"

dbchud
Starting Member

13 Posts

Posted - 2009-08-17 : 12:01:33

thanks Peso, will give that a go tomorrow!!

dbchud
Starting Member

13 Posts

Posted - 2009-08-21 : 05:19:12

@Peso

Just to say thanks for your help. After some playing around I got what I needed, cheers.

Gary

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources