concept ,
A teaher will be assiged a class from certain date to certain date on a particular session.
Teacher A is assigned classes from 20-05-2014 to 29-05-2014 . everyday he use to take classes by 9.00 to 11.00
for that in my db I am getting values from the principal as,
INPUTS,
startdate - 20-05-2014
enddate - 29-05-2014
session - 9.00 to 11.00
teacher name - teacher A
from 20 th to 29th from 9.00 to 11.00 teacher A is assigned, so noe one can be assigned on that particular day's session.
so far I have created stored procedure for restricting date alone,
assigned date cannot be added again but ,
dates have a sub checking session,
so it is confusing a lot.
I dono how to make my next move.
Suggestions are welcome on both coding and concept wise.
MY PROCEDURE
DELIMITER $$
create procedure sp_Getconfirmtask(IN sdate datetime, IN edate datetime,OUT outmsg varchar(50))
begin
if not exists(
SELECT * FROM sch_taskassigned WHERE (taskstartdate BETWEEN sdate AND edate)OR (taskenddate BETWEEN sdate AND edate) OR (sdate BETWEEN taskstartdate AND taskenddate) OR (edate BETWEEN taskstartdate AND taskenddate) AND tid=tid) then
set outmsg = 'ADDED SUCESSFULLY';
else
set outmsg = 'RECORD ALREADY PRESENT';
end if;
END $$