Status based on date range...

joejoethecactus · #1 (**permalink**) 05-08-08, 06:05

Background
I'm currently in the planning phase of an asset tracking database. Engineers will utilize the database to reserve equipment from inventory so it will be available to them for the duration of their project date range. The basic table design is complete, with one exception... I don't know how to track the status of a given item.

Summary
Imagine if you will, two columns within a 'project' table that store the start and end date of a particular project.

The equipment tables are split by various attributes. But essentially, the engineers choose items (according to serial number), which will then be assigned to their project.

Question
1. How can I disable the selection of an item if a provided date range overlaps an existing project assignment? If at all possible, I'd like the item to be visible in the equipment list, albeit not selectable.

r937 · #2 (**permalink**) 05-08-08, 06:57

this might help --
Absences between a date range...

mike_bike_kite · #3 (**permalink**) 05-08-08, 07:03

Do a union between 2 select statements where the first one is for equipment that is not being reserved and the other is for equipment that is reserved. You can order all the results by name so it's easier to read. Please note you haven't given any table/field details so I'm guessing. Also note I haven't got a MySQL database to hand to try this on but I'm sure it's nearlly there.

Code:

select	e.id, e.name, status='available'
from	Equipment e
where	not exists(
		select	1
		from	Reserved r,
			Project p
		where	p.id = users_project_id
			and r.equip_id = e.id
			and (	p.start_date between r.start_date and r.end_date
				or p.end_date between r.start_date and r.end_date
				or r.start_date between p.start_date and p.end_date
				or r.end_date between p.start_date and p.end_date ) )
union
select	e.id, e.name, status='reserved'
from	Equipment e
where	exists(
		select	1
		from	Reserved r,
			Project p
		where	p.id = users_project_id
			and r.equip_id = e.id
			and (	p.start_date between r.start_date and r.end_date
				or p.end_date between r.start_date and r.end_date
				or r.start_date between p.start_date and p.end_date
				or r.end_date between p.start_date and p.end_date ) )
order by name

Your proposed set up means a piece of equipment has to be reserved for the whole life of the project - what if it's only needed for part of the project?

If easiest to reserve equipment for the whole length of the project and there is no financial penalty for doing this then this is what will be done - why not add a cost_per_day to the equipment table so users are aware of costs and can put in more realistic requirements.

This could also be improved by showing who's got it reserved so perhaps they can reorganise dates.

Mike

Rudy - sorry I'd already typed this all out when I saw you response and didn't have the strength of will to delete it

joejoethecactus · #4 (**permalink**) 05-08-08, 14:26

thank you for the replies thus far... both were helpful in allowing me to better understand the logic behind the issue (the simplicity of rudy's where statement with the functionality of mike's select union). however, i'm still confused as to how the specific date information should be stored.

to re-use the linked example:

Code:

          @start     @end            
             |         |               
1   FR---TO  |         |               
             |         |               
2         FR-|-TO      |               
             |         |               
3            | FR---TO |               
             |         |               
4         FR-|---------|-TO       
             |         |               
5            |      FR-|-TO 
             |         |               
6            |         |  FR---TO

each numbered line item can be adapted to represent an item in the equipment table. the 'fr' and 'to' reflect a date range reserved for an existing project. the @start and @end are date values for a new project (result: items 1 and 6 will be available for reservation by the new project).

new question
what's the best way to organize my data?

in my original example, do the dates stored in the projects table result in a many to many relationship with the equipment table -- each project may have multiple items and each item may have multiple projects?

if so, say i split the 2 date columns (fr and to) into a new table (for 2 one to many relationships with projects and equipment), how do @start and @end (the inputs for a new project) fit into the picture?

***edit***

to answer questions...

most projects are remote, requiring the equipment to be on-site for the duration. there are instances where spares will be taken, which will be the only requirement that may incur some sort of financial penalty. the database has point of contact information for each project that was omitted for post brevity. i also did not include db, table, and column specifics for this reason.

mike_bike_kite · #5 (**permalink**) 05-08-08, 16:45

Quote:

Originally Posted by joejoethecactus

i'm still confused as to how the specific date information should be stored.

These were the tables and fields I assumed. I guess you could ignore the dates in the reserved table and just use the start and end dates for the associated project. I think that covers what you're after.
Code:
User: id, name, phone, email, etc
Project: id, name, user_id, start_date, end_date
Equipment: id, name, cost_per_day
Reserved: project_id, equipment_id, start_date, end_date

Mike

joejoethecactus · #6 (**permalink**) 05-08-08, 17:30

ugh, i was over-thinking it. thanks again.

Internet Services

Web Development

Digital Marketing

Consumer Tech