>Hi all, I need your help on this please: I have 1 table with day, hours and minutes of the day, this is being assigned to 1 person, when doing this for several days on the week, I need to know, the next available hour of the day to be assigned. Example for guy "Doe", I assign monday from 8am to 10am, tuesday from 9am to 11am. For guy "Johnson" I assign monday from 10am to 11am and I want to assign the same hour for tuesday, but I need the system alerts that hour is already in use, next available hour will be tuesday 11am.
>Doe Monday 8am - 10 am Johnson Monday 10am - 11am
>Doe Tuesday 9am - 11 am Johnson Tuesday 10am - 11am (error) must be (11am -12m)
>
>T.I.A.
Assuming you have a Calendar table that has hours intervals and days.
select min(Interval) as NextAvailable from Calendar C
where not exists (select 1 from Assignments A where A.[DayOfWeek] = C.[Day Of Week]
and A.[Interval] = C.[Interval])
were C.[DayOfWeek] = 'Tuesday';
If it's not broken, fix it until it is.
My Blog