Fairly difficult UPDATE 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)

Fairly difficult UPDATE query

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-03-14 : 14:17:53

Hi!
I have a (temp) table with a schedule for a day divided into 15 minute increments, each row representing a 15 minute segment. The table has an Unavailable column, specifying whether or not a resource is available at that time slot or not. I also have an Availability table which has up to 5 schedule rows for any particular day for any particular resource e.g.
(all times are represented as integers, # of minutes since 12:00 AM)
Day Start End
Monday 450 750
Monday 1020 1050
Monday 1065 1170
Tuesday ... ...
I want to update the temp table's Unavailable column, that if a 15 minute segment(row) is not between (actually, >= Start AND < End) a valid time range for that day, Unavailable should be 1, otherwise 0.

Thanks!!

Sarah Berger MCSD

Jay99

468 Posts

Posted - 2002-03-14 : 14:28:28

Yikes. You are designing/coding yourself into a messy corner.

1.) Forget about the temp table. Its information that you can calculate on the fly.
2.) Be consistent with your use of time measurements. All the convertions are going to get messy. If you are on sql 2k at least write yourself a udf_converttimetonumminutes user-defined function.

My suggestion would be to rethink design a bit. If you can clarify what data your are trying to store and how it is going to be used, maybe someone her can help you with a simpler design.

Jay

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-14 : 14:36:38

Usually problems of this type are solved pretty well with a correlated subquery.

Assuming the availability and temp tables have resourceID's, and the unavailable column defaults to zero, it might look like:

update #temp
set unavailable = 1
from #temp t
where not exists (
select 1
from availability
where resourceID = t.resourceID and start >= t.start and [end] < t.[end] )

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-03-14 : 14:45:58

Hiya!
Seems I got it very soon after posting. Gee, I should've posted hours ago!
UPDATE #TempAppt SET Unavailable = 1 WHERE TimeStart <> ANY (SELECT TimeStart FROM #TempAppt INNER JOIN (SELECT StartTime,Endtime FROM DayAvailability WHERE DrID = @Dr AND DayofWeek = DatePart(dw,@Date) AND Units <> 0)DA ON TimeStart >= DA.StartTime AND TimeStart < DA.EndTime)

BTW, I'm using a temp table because this data is calculated- it generates a whole day of empty slots (null data), then is joined with the real schedule tables to yield a full day. A temp table turned out to be the easiest and fastest way to work it. (look up Garth's article on Calculating Running Totals)

Cheers!!

Sarah Berger MCSD

Jay99

468 Posts

Posted - 2002-03-14 : 15:00:09

quote:

BTW, I'm using a temp table because this data is calculated- it generates a whole day of empty slots (null data), then is joined with the real schedule tables to yield a full day. A temp table turned out to be the easiest and fastest way to work it. (look up Garth's article on Calculating Running Totals)

You have peaked my curiosity. What does the running totals article have to do with this situation?

Jay

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-03-14 : 21:31:04

There is an Appointments table which holds all appointments for a resource for a day, and an Availability table which holds the schedule. The two do not strictly correspond because appointments might only be scheduled for part of the day. The data I needed was for a report to show the full day, with filled slots where applicable, and null slots, and if the slot is null, whether it is an available slot or not. So I needed the temp table to generate null rows for every 15 minutes from the start of the earliest range in the Availability table, to the end of the latest range. The TimeStart field in the temp table is calculated by doing a loop like this:
While @StartTime < (last endtime)
insert into #table (TimeStart,...)values (@StartTime,...)
Set @starttime = @starttime + 15

then I replace the null rows that have corresponding appointments with the actual appointments. What I like about this solution is that it's very fast - the temp table and Availability table are not apt to be large.

Cheers!

Sarah Berger MCSD

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-03-14 : 21:33:45

Hey Sarah

Why not get your own log in ? They are free

Damian

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-03-16 : 21:56:46

Hi,Damian
Simondeutsch is my own login. Deutsch is my maiden name, and I was registered before my marriage. I use this name on all my work-related mail and posts, as I sometimes find it safer than to use a female name (though thank G-d harassment is not a problem on SQLTeam at all) . Also, makes it easier to remember one username for many sites.
But thanks for your concern anyway