3> IF OBJECT_ID('dbo.Sessions') IS NOT NULL
4> DROP TABLE dbo.Sessions;
5> GO
1>
2> CREATE TABLE dbo.Sessions
3> (
4> keycol INT NOT NULL IDENTITY PRIMARY KEY,
5> app VARCHAR(10) NOT NULL,
6> usr VARCHAR(10) NOT NULL,
7> starttime DATETIME NOT NULL,
8> endtime DATETIME NOT NULL,
9> CHECK(endtime > starttime)
10> );
11> GO
1>
2> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user1', '20060212 08:30', '20060212 10:30');
3> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user2', '20060212 08:30', '20060212 08:45');
4> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user1', '20060212 09:00', '20060212 09:30');
5> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user2', '20060212 09:15', '20060212 10:30');
6> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user1', '20060212 09:15', '20060212 09:30');
7> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user2', '20060212 10:30', '20060212 14:30');
8> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user1', '20060212 10:45', '20060212 11:30');
9> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user2', '20060212 11:00', '20060212 12:30');
10> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user1', '20060212 08:30', '20060212 08:45');
11> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user2', '20060212 09:00', '20060212 09:30');
12> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user1', '20060212 11:45', '20060212 12:00');
13> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user2', '20060212 12:30', '20060212 14:00');
14> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user1', '20060212 12:45', '20060212 13:30');
15> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user2', '20060212 13:00', '20060212 14:00');
16> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user1', '20060212 14:00', '20060212 16:30');
17> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user2', '20060212 15:30', '20060212 17:00');
18> GO
1>
2> SELECT S1.app, S1.usr,
3> S1.keycol AS key1, S1.starttime AS start1, S1.endtime AS end1,
4> S2.keycol AS key2, S2.starttime AS start2, S2.endtime AS end2
5> FROM dbo.Sessions AS S1
6> JOIN dbo.Sessions AS S2
7> ON S2.app = S1.app
8> AND S2.usr = S1.usr
9> AND (S2.starttime BETWEEN S1.starttime AND S1.endtime
10> OR S1.starttime BETWEEN S2.starttime AND S2.endtime);
11> GO
app usr key1 start1 end1 key2 start2 end2
---------- ---------- ----------- ----------------------- ----------------------- ----------- ----------------------- -----------------------
app1 user1 1 2006-02-12 08:30:00.000 2006-02-12 10:30:00.000 1 2006-02-12 08:30:00.000 2006-02-12 10:30:00.000
app1 user1 3 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000 1 2006-02-12 08:30:00.000 2006-02-12 10:30:00.000
app1 user1 5 2006-02-12 09:15:00.000 2006-02-12 09:30:00.000 1 2006-02-12 08:30:00.000 2006-02-12 10:30:00.000
app1 user2 2 2006-02-12 08:30:00.000 2006-02-12 08:45:00.000 2 2006-02-12 08:30:00.000 2006-02-12 08:45:00.000
app1 user1 1 2006-02-12 08:30:00.000 2006-02-12 10:30:00.000 3 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000
app1 user1 3 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000 3 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000
app1 user1 5 2006-02-12 09:15:00.000 2006-02-12 09:30:00.000 3 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000
app1 user2 4 2006-02-12 09:15:00.000 2006-02-12 10:30:00.000 4 2006-02-12 09:15:00.000 2006-02-12 10:30:00.000
app1 user2 6 2006-02-12 10:30:00.000 2006-02-12 14:30:00.000 4 2006-02-12 09:15:00.000 2006-02-12 10:30:00.000
app1 user1 1 2006-02-12 08:30:00.000 2006-02-12 10:30:00.000 5 2006-02-12 09:15:00.000 2006-02-12 09:30:00.000
app1 user1 3 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000 5 2006-02-12 09:15:00.000 2006-02-12 09:30:00.000
app1 user1 5 2006-02-12 09:15:00.000 2006-02-12 09:30:00.000 5 2006-02-12 09:15:00.000 2006-02-12 09:30:00.000
app1 user2 4 2006-02-12 09:15:00.000 2006-02-12 10:30:00.000 6 2006-02-12 10:30:00.000 2006-02-12 14:30:00.000
app1 user2 6 2006-02-12 10:30:00.000 2006-02-12 14:30:00.000 6 2006-02-12 10:30:00.000 2006-02-12 14:30:00.000
app1 user2 8 2006-02-12 11:00:00.000 2006-02-12 12:30:00.000 6 2006-02-12 10:30:00.000 2006-02-12 14:30:00.000
app1 user1 7 2006-02-12 10:45:00.000 2006-02-12 11:30:00.000 7 2006-02-12 10:45:00.000 2006-02-12 11:30:00.000
app1 user2 6 2006-02-12 10:30:00.000 2006-02-12 14:30:00.000 8 2006-02-12 11:00:00.000 2006-02-12 12:30:00.000
app1 user2 8 2006-02-12 11:00:00.000 2006-02-12 12:30:00.000 8 2006-02-12 11:00:00.000 2006-02-12 12:30:00.000
app2 user1 9 2006-02-12 08:30:00.000 2006-02-12 08:45:00.000 9 2006-02-12 08:30:00.000 2006-02-12 08:45:00.000
app2 user2 10 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000 10 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000
app2 user1 11 2006-02-12 11:45:00.000 2006-02-12 12:00:00.000 11 2006-02-12 11:45:00.000 2006-02-12 12:00:00.000
app2 user2 12 2006-02-12 12:30:00.000 2006-02-12 14:00:00.000 12 2006-02-12 12:30:00.000 2006-02-12 14:00:00.000
app2 user2 14 2006-02-12 13:00:00.000 2006-02-12 14:00:00.000 12 2006-02-12 12:30:00.000 2006-02-12 14:00:00.000
app2 user1 13 2006-02-12 12:45:00.000 2006-02-12 13:30:00.000 13 2006-02-12 12:45:00.000 2006-02-12 13:30:00.000
app2 user2 12 2006-02-12 12:30:00.000 2006-02-12 14:00:00.000 14 2006-02-12 13:00:00.000 2006-02-12 14:00:00.000
app2 user2 14 2006-02-12 13:00:00.000 2006-02-12 14:00:00.000 14 2006-02-12 13:00:00.000 2006-02-12 14:00:00.000
app2 user1 15 2006-02-12 14:00:00.000 2006-02-12 16:30:00.000 15 2006-02-12 14:00:00.000 2006-02-12 16:30:00.000
app2 user2 16 2006-02-12 15:30:00.000 2006-02-12 17:00:00.000 16 2006-02-12 15:30:00.000 2006-02-12 17:00:00.000
1>
2> drop table sessions;
3> GO
1>
2>