New Tracking Query - help needed

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 2000 Forums

SQL Server Development (2000)

New Tracking Query - help needed

Author

Topic

abarsami
Yak Posting Veteran

68 Posts

Posted - 2003-01-21 : 01:53:28

Here is the data I am working with:

ID From Date To Date Client FakeIP DayOfMo
1 2002-12-03 14:39:10.000 2002-12-03 19:37:04.000 45 1.2.3.4 3
2 2002-12-03 19:37:04.000 2002-12-12 10:21:38.000 45 2.3.4.3 3
3 2002-12-03 14:45:10.000 2002-12-03 19:37:04.000 45 34.343.34 12
4 2002-12-03 19:37:04.000 2002-12-12 10:21:38.000 45 2.3.4.3 12
5 2002-11-13 11:37:35.000 2002-11-13 17:42:15.000 45 55.55.44.55 13
6 2002-11-13 17:42:15.000 2002-11-13 20:04:08.000 45 55.54.44.44 13
7 2002-11-13 18:04:08.000 2002-11-14 11:18:27.000 45 34.334.34.34 13
8 2003-01-15 06:46:10.000 2003-01-15 08:10:10.000 56 43.323.23.23 15
9 2003-01-15 06:47:06.000 2003-01-15 16:58:42.000 56 32.343.34.32 15

I need a query that searches for overlapping dates within the same group of (Client, Day of mo)
Specifically where the (from date) is less than the (to date of the previous record within the same group)

So groups are divided by

(client,day of mo)

group 1 (45,3)
group 2 (45,12)
group 3 (45,13)
group 4 (56,15)

So a query would search if the (from date) is less than the (to date of the previous record) for each group.

The query should return only these results, because the from date of #7 is less than the to date of #6.
The from date of #9 is less than the to date of #8.

6 2002-11-13 17:42:15.000 2002-11-13 20:04:08.000 45 55.54.44.44 13
7 2002-11-13 18:04:08.000 2002-11-14 11:18:27.000 45 34.334.34.34 13
8 2003-01-15 06:46:10.000 2003-01-15 08:10:10.000 56 43.323.23.23 15
9 2003-01-15 06:47:06.000 2003-01-15 16:58:42.000 56 32.343.34.32 15

The from date of #3 is less than the to date of #1, but it is not within the same group.
#3 is day of week 12 with a client of 45, #1 is day of week 3 with client of 45. Even though the client matches the day of week is different. So none of the records below are returned.

1 2002-12-03 14:39:10.000 2002-12-03 19:37:04.000 45 1.2.3.4 3
3 2002-12-03 14:45:10.000 2002-12-03 19:37:04.000 45 34.343.34 12

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-21 : 03:35:13

Have you tried this yourself?
There are a lot of threads on overlapping dates on this forum.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources