Minute table?

I got no responses to the 'Complex query' thread, which probably
contained too much information. I'll try simplifying it:
I have 2 tables
create table Minima (MI_ID int, MI_BeginTime smalldatetime, MI_EndTime
smalldatetime, MI_RequiredStaff tinyint)
this table contains the required staff between certain time.
MI_RequiredStaff is the column that contains the number of employees
required at that time.
create table PlannedWork (PW_ID int, PW_Date smalldatetime,
PW_BeginTime smalldatetime, PW_EndTime smalldatetime, MI_StaffID int)
this table contains times when an employee will work.
Now I'm looking for a query which can give me the Minima which aren't
fullfilled for a certain day. Of course if an employee is used to fill
a certain minima he/she can't be used for another one.
I have no clue on how to begin with this. But I was thinking maybe
it's possible to use something like a minute-table (like you have a
table with all the days). I don't know if that has ever been used and
if it's appropriate for this.
Thanks in advance,
Stijn Verrept.Stijn,
There is no 'easy' fix for this. I feel you need to identify the smallest
period of time to use as slots, unless you have already defined this in the
XX_BeginTime and XX_EndTime inasmuch that such times will always match for
any time of the day (e.g. a standard 3 shift system commencing at 0700 and
running a stright 8 hours each). Any such system will need to be the same fo
r
both the employee and required time slots.
Once identified, you can create a temp table of the required slots for a
given period of time (be it a single shift, day, w, etc) and then update
those records with the counts of staff matching that shift.
Example: I use a straight 3 shift system of 0700-1500, 1500-2300 and
2300-0700.
Day 1 requires 3,5,2 (respectively).
The Temp table will have Date, Start, End, Required, Planned columns.
The Date, Start, End, Required columns are populated immediately from the
Minima table, and then each row is updated with the count of Employtees
matching those times/dates from the PlannedWork table.
The final act is to calculate against the Required and Planned columns to
determine overages or shortfalls in the numbers.
The whole code would be contained in a Stored Procedure with the relevent
Params to determine any period of time.
This is a *very* basic and simplified version of what you may find to the a
solution, but you could find it beneficial to break the problem down into
steps as above to assist you.
Hope this assists,
Tony
"Stijn Verrept" wrote:

> I got no responses to the 'Complex query' thread, which probably
> contained too much information. I'll try simplifying it:
> I have 2 tables
> create table Minima (MI_ID int, MI_BeginTime smalldatetime, MI_EndTime
> smalldatetime, MI_RequiredStaff tinyint)
> this table contains the required staff between certain time.
> MI_RequiredStaff is the column that contains the number of employees
> required at that time.
> create table PlannedWork (PW_ID int, PW_Date smalldatetime,
> PW_BeginTime smalldatetime, PW_EndTime smalldatetime, MI_StaffID int)
> this table contains times when an employee will work.
> Now I'm looking for a query which can give me the Minima which aren't
> fullfilled for a certain day. Of course if an employee is used to fill
> a certain minima he/she can't be used for another one.
> I have no clue on how to begin with this. But I was thinking maybe
> it's possible to use something like a minute-table (like you have a
> table with all the days). I don't know if that has ever been used and
> if it's appropriate for this.
> --
> Thanks in advance,
> Stijn Verrept.
>|||Stijn Verrept wrote:
> I got no responses to the 'Complex query' thread, which probably
> contained too much information. I'll try simplifying it:
> I have 2 tables
> create table Minima (MI_ID int, MI_BeginTime smalldatetime, MI_EndTime
> smalldatetime, MI_RequiredStaff tinyint)
> this table contains the required staff between certain time.
> MI_RequiredStaff is the column that contains the number of employees
> required at that time.
> create table PlannedWork (PW_ID int, PW_Date smalldatetime,
> PW_BeginTime smalldatetime, PW_EndTime smalldatetime, MI_StaffID int)
> this table contains times when an employee will work.
> Now I'm looking for a query which can give me the Minima which aren't
> fullfilled for a certain day. Of course if an employee is used to fill
> a certain minima he/she can't be used for another one.
> I have no clue on how to begin with this. But I was thinking maybe
> it's possible to use something like a minute-table (like you have a
> table with all the days). I don't know if that has ever been used and
> if it's appropriate for this.
> --
> Thanks in advance,
> Stijn Verrept.
Hi Stijn,
No keys at all in your tables? Is every column really nullable or do
you expect us to guess? Surely you could do better...
Assuming you add some sensible keys and constraints and assuming you
don't allow PlannedWork for any employee to overlap (easily prevented
with a trigger), try something like the following query.
I don't know why you would put date and time in separate columns so
I've ignored PW_Date altogether.
SELECT mi_id, mi_begintime, mi_endtime, mi_requiredstaff,
SUM(DATEDIFF(MINUTE,work_start,work_end)
) AS work_time
FROM
(SELECT M.mi_id, M.mi_begintime, M.mi_endtime, M.mi_requiredstaff,
mi_requiredstaff*DATEDIFF(MINUTE, mi_begintime, mi_endtime)
AS required_time,
CASE WHEN M.mi_begintime > P.pw_begintime
THEN M.mi_begintime ELSE P.pw_begintime END AS work_start,
CASE WHEN M.mi_endtime < P.pw_endtime
THEN M.mi_endtime ELSE P.pw_endtime END AS work_end
FROM Minima AS M
LEFT JOIN PlannedWork AS P
ON M.mi_begintime < P.pw_endtime
AND M.mi_endtime > P.pw_begintime) AS T
GROUP BY mi_id, mi_begintime, mi_endtime, mi_requiredstaff,
required_time
HAVING SUM(DATEDIFF(MINUTE,work_start,work_end)
)< required_time ;
However, this won't prevent an employee being allocated to more than
one Minima. To do that I think you will have to pre-allocate employees
to each Minima on a best-fit basis. I don't think you'll be able to
find the best-fits using only declarative SQL because I'm pretty
certain this is an NP-complexity problem - I think there's even a
mathematical name for it.
I discussed a logically similar best-fit problem in the following
thread. My solution could help you get an arbitrary match between
Minima and PlannedWork but an arbitrary match I'd suggest isn't what
you are looking for. I guess you'll need to build a table of possible
assignments and then pull out the minimum cost ones. If you post some
sample data someone may be able to help further.
http://groups.google.co.uk/group/co...2c682dab331565c
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Tony Scott wrote:

> There is no 'easy' fix for this. I feel you need to identify the
> smallest period of time to use as slots, unless you have already
> defined this in the XX_BeginTime and XX_EndTime inasmuch that such
> times will always match for any time of the day (e.g. a standard 3
> shift system commencing at 0700 and running a stright 8 hours each).
> Any such system will need to be the same for both the employee and
> required time slots.
This could indeed simplify things (if the employee and required slots
are the same. I'm going to ask if this will be the case.

> This is a very basic and simplified version of what you may find to
> the a solution, but you could find it beneficial to break the problem
> down into steps as above to assist you.
True, thanks a lot!
Stijn Verrept.|||David Portas wrote:

> No keys at all in your tables? Is every column really nullable or do
> you expect us to guess? Surely you could do better...
Indeed I can, and I also did (see the 'complex query' thread) however I
got no replies there and now I get 2. Sometimes it's better to provide
less info ;). The tables are of course completely fictional, just to
give the general idea of the problem.

> However, this won't prevent an employee being allocated to more than
> one Minima.

> To do that I think you will have to pre-allocate employees
> to each Minima on a best-fit basis. I don't think you'll be able to
> find the best-fits using only declarative SQL because I'm pretty
> certain this is an NP-complexity problem - I think there's even a
> mathematical name for it.
Thank you very much, I'm going to look up that NP-complexity problem
(which sounds complex ;). And look into the thread you provided! The
possible assignments indeed seems a good idea, the benefit is I would
only need to do this for the Minima and the ServiceGratings which will
speed up the final query.
Thanks again,
Stijn Verrept.|||No problem at all.
Just as a sideline to this, if you should find that the 'slots' do not
match, then you may be forced to use 'compromise' or 'lowest denominator'
slots, those being the smallest unit of time that can fit within both sets o
f
data. Example would be that if the Employees and shifts always start on the
hour and work in hours, then the unit would be hours, and you would then nee
d
to use a single row per hour. If the employees or shifts could start on the
half-hour, then that would be the unit of measure.
I would be interested in your findings and final solution, as this is the
type of problem I deal with day-in and day-out
Tony
"Stijn Verrept" wrote:

> Tony Scott wrote:
>
> This could indeed simplify things (if the employee and required slots
> are the same. I'm going to ask if this will be the case.
>
> True, thanks a lot!
> --
> Stijn Verrept.
>