Help with query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Help with query

Author

Topic

benricho
Yak Posting Veteran

84 Posts

Posted - 2001-12-03 : 01:27:35

I have been working on a query without much luck. Basically I have just inserted some dates into a calendar recurrence table, and I want to check if there are any confilcts. My table is as follows:

appRecurrence
--------------
appRecID int (pk)
appID int (fk)
appDate smalldatetime
appStartTime char(4)
appEndTime char(4)

I need to check this table to see if there are any rows that have the same appDate, and start and end times that are the same or overlap.

I have tried using a subquery but haven't been able to figure it out as yet.

Nazim
A custom title

1408 Posts

Posted - 2001-12-03 : 04:15:12

select * from apprecurrence a inner join
(select appdate,appstarttime,appendtime,count(*) Repetitions from apprecurrence
group by appdate,appstarttime,appendtime
having count(*)>1) d
on
a.appdate=d.appdate and a.appstarttime=d.appstartime and a.appendtime=d.appendtime

will give the what u r looking for.
is this what u r looking for?

-------------------------

sica
Posting Yak Master

143 Posts

Posted - 2001-12-03 : 04:24:25

Can you give us more info about appStartTime and appEndTime ?How does it look like?

Sica

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-03 : 06:11:17

You haven't mentioned what you want to do with the appID fk, so I haven't considered it. Also, I'm assuming appStartTime and appEndTime are in HHMM or some other format where the string collation order is the same as time.


SELECT a.appRecID
FROM appRecurrence a
INNER JOIN appRecurrence b ON a.appDate = b.appDate
  AND a.appRecID <> b.appRecID
  AND a.appStartTime < b.appEndTime
  AND b.appStartTime < a.appEndTime
GROUP BY a.appRecID

benricho
Yak Posting Veteran

84 Posts

Posted - 2001-12-03 : 06:36:38

The start time and end times are in 24 hour time, HHMM.

The appID field isn't really relevant to the query, just thought I would put it in to show my complete table.

These examples look great, I'll try them first thing in the morning at work tomorrow and let you know how I go. Thanks for the help!!

sica
Posting Yak Master

143 Posts

Posted - 2001-12-03 : 06:37:10

Nice Arnold!!!

benricho
Yak Posting Veteran

84 Posts

Posted - 2001-12-03 : 17:33:59

sdNazim, this query is good, but it only shows me the conflicts when that start and end times are exactly the same. For example:

Appointment 1: 800 - 930
Appoitnment 2: 900 - 1000

This won't show up as a confilict in your example.

Arnolds solutions works, thanks very much. At first I thought it was having troubles with the example above, but I have to make sure that those times before 1000 have a leading zero, then it works fine (i.e. 0800 - 0930).

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources