A real Yak challenge... - 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)

A real Yak challenge...

Author

Topic

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-08-13 : 23:36:43

Hi orl

I'm trying to work out a nice, neat, set-based solution (because setbasedisthetruepath after all) to the following:

I have a table Bookings
assetid startdate enddate
654164 2002-03-30 00:00:00.000 2002-12-26 00:00:00.000
654164 2001-01-01 00:00:00.000 2001-01-19 00:00:00.000
etc

and a table Assets
assetid type active
654164 'Yak Head Collar' -1
654165 'Yak Head Collar' -1

The user makes a request, like they want to borrow 2 Yak Head collars. So far so good.

Now here's the tricky bit....

On a calendar, I'd like to grey out all the days where there are NOT 2 'Yak Head Collars' available.

Now to find out whether a request is possible for a given day is trivial, but how can I return a list of all possible days (say in the current month), without cursoring through each day?

suggestions please...

PS here's your code for test data

create table Bookings (assetid int, startdate datetime, enddate datetime)

insert into Bookings
select 654164, CONVERT(datetime, '2002-03-30'), CONVERT(datetime, '2002-12-26')

insert into Bookings
select 654164, CONVERT(datetime, '2001-01-01'), CONVERT(datetime, '2001-01-19')

create table assets (assetid int, type nvarchar(50), active int)

insert into assets
select 654164, 'Yak Head Collar', -1

insert into assets
select 654165, 'Yak Head Collar', -1

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 08/13/2002 23:40:41

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-14 : 03:57:14

Probably not the best way but...

You will need a temp table of dates #dates - that's easy and been covered before

select *
from #Dates d
where ((select count(*) from Assets where type = @type and Active = -1)
- (select count(*) from Bookings b, Assets a where b.assetid = a.assetid and a.type = @type and d.date between b.startdate and b.enddate)) >= 2

==========================================
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.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-08-15 : 04:34:42

Thanks nigel

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources