Re: View challenge

From: Cimode <cimode_at_hotmail.com>
Date: 13 Aug 2006 10:52:22 -0700
Message-ID: <1155491542.684705.312870_at_74g2000cwt.googlegroups.com>

frebe73_at_gmail.com wrote:
> I have a number of tables as below:
> employee(*employeeid, ....)
> schedule(*scheduleid, recur_interval, ...)
> emp_schedule(*employeeid, *scheduleid, *valid_from, valid_to)
> workshift(*scheduleid, *shiftid, starttime, endtime)
>
> recur_interval indicates the number of days for which the schedule
> should recur.
> starttime and endtime indicates the first occurrence of the workshift.
>
> Now I want to create a view (without using stored procedure) that
> returns every occurrence of the workshifts for every employee, like
> below.
>
> create view emp_calendar as
> select
> employeeid,
> starttime,
> endtime
> .....
>
> Does someone has a solution (preferably MySQL) for this, or is it
> simply impossible?
I am not convinced workshift is really useful...SQL Server version of what you may be seeking to establish...

employee: employeeid, employee_name

insert employee
select 1, 'Doe'

schedule: scheduleid, start_time, end_time

insert schedule
select 1, 8, 12
insert schedule
select 2, 8, 17
insert schedule
select 3, 14, 17

employee_schedule: employeeid, scheduleid, start_date, end_date

insert employee_schedule_application
select 1, 1, '2006-08-07', '2006-08-21'
insert employee_schedule_application
select 1, 2, '2006-08-22', '2006-08-26'
insert employee_schedule_application
select 1, 3, '2006-08-27', '2006-09-15'

Below would return current active workshift a specific employee

create view current_calendar(_at_employeeid int)

as

select employee.employee_name, schedule.start_time, schedule.end_time from employee

	inner join employee_schedule
		on employee.employeeid = employee_schedule.employeeid
	inner join
		on employee_schedule.scheduleid = schedule.scheduleid

where employee_schedule.start_date > = getdate() AND employee_schedule.start_date < dateadd(d, 1, getdate()) AND employee.employeeid = _at_employeeid

Ex: For instance, calling current_calendar(_at_employeeid int) in '2006-08-08' would return

'Doe', 8, 12 because that schedule applies in the timeframe.

If the procedure is called on '2006-08-24'

then it would return

'Doe', 8, 17

The rest is just looping and presentation and making sure the overlapping constraint are implemented...This design is far from being complete but it's a start...Hope this helps...

> Fredrik Bertilsson
Received on Sun Aug 13 2006 - 19:52:22 CEST