Estoy tratando de crear un sistema de reserva mediante el cual los clientes puedan elegir uno de una lista de ranuras disponibles. Las máquinas tragamonedas son conocidas (09:00 - 17:00, cada 15 minutos).

La duración de la reserva depende de la cantidad de artículos a recolectar (calculados en otro lugar), y siempre se redondeará a la ranura de 15 minutos más cercana.

Lo que intento hacer es consultar las reservas existentes y devolver una lista de ranuras y el número de reservas en cada ranura. Si una reserva está 'en' un intervalo de tiempo (por ejemplo, una reserva para las 09:00 - 10:00 será en 09:00, 09:15, 09: 30m y 09:45), solo quiero agregar 1 a cada uno de los recuentos de esas ranuras.

Luego uniré este resultado con una lista de personal para mostrar cuántos empleados están disponibles (y, por lo tanto, si el espacio está libre), pero esto no es parte de esta pregunta.

Ejemplo de datos de devolución:

TimeSlot | BookingsCount
------------------------
09:00    | 2
09:15    | 2
09:30    | 1
09:45    | 1
10:00    | 0
create table ScheduleTimes (t time);
insert into ScheduleTimes (t) values(('09:00'));
insert into ScheduleTimes (t) values(('09:15'));
insert into ScheduleTimes (t) values(('09:30'));
insert into ScheduleTimes (t) values(('09:45'));
insert into ScheduleTimes (t) values(('10:00'));
insert into ScheduleTimes (t) values(('10:15'));
insert into ScheduleTimes (t) values(('10:30'));
insert into ScheduleTimes (t) values(('10:45'));
insert into ScheduleTimes (t) values(('11:00'));
insert into ScheduleTimes (t) values(('11:15'));
insert into ScheduleTimes (t) values(('11:30'));
insert into ScheduleTimes (t) values(('11:45'));
insert into ScheduleTimes (t) values(('12:00'));
insert into ScheduleTimes (t) values(('12:15'));
insert into ScheduleTimes (t) values(('12:30'));

create table CollectionSchedule (ScheduleID int, CustomerID int, CollectionStart time, CollectionEnd time);
insert into CollectionSchedule values (1, 111, '09:00', '10:00');
insert into CollectionSchedule values (2, 222, '09:00', '09:30');
insert into CollectionSchedule values (3, 333, '09:30', '10:00');
insert into CollectionSchedule values (4, 444, '10:00', '10:15');
insert into CollectionSchedule values (5, 555, '10:00', '11:00');
insert into CollectionSchedule values (6, 666, '10:15', '10:45');
insert into CollectionSchedule values (7, 777, '10:45', '11:00');

He creado un SQL Fiddle que está disponible aquí y que tiene configurado un esquema de demostración. El El esquema no está escrito en piedra y se puede cambiar si es necesario, es justo lo que se me ocurrió hasta ahora.

Aquí está el esquema de demostración:

Calendario de recolección (lista de citas ya realizadas):

create table CollectionSchedule (ScheduleID int, CustomerID int, CollectionStart time, CollectionEnd time);

ScheduleTimes (Una lista de franjas horarias disponibles);

create table ScheduleTimes (t time);

Aquí está mi último intento de consulta:

select 
    st.t,
    SUM(sq1.intimeframe) as BookingsCount
from
    ScheduleTimes st
    left join (
        select 
            st.t as Slot,
            case when (st.t <= cs.CollectionStart and st.t < cs.CollectionEnd) then 1 else 0 end as intimeframe 
        from CollectionSchedule cs, @ScheduleTimes st
    ) sq1 on st.t = sq1.Slot
group by st.t

Pero esto devuelve valores incorrectos para el número de reservas en cada ranura porque cuenta las entradas varias veces, y es claro que estoy yendo por el camino equivocado y estoy un poco perdido. Cualquier ayuda sería muy apreciada.

1
Marcos Foster 26 jun. 2020 a las 16:24

2 respuestas

La mejor respuesta

¿Algo como esto devuelve los valores apropiados?

SELECT Agg.T, COUNT(*) 
FROM(SELECT s.T FROM CollectionSchedule as c
INNER JOIN ScheduleTimes as s 
ON s.T >= c.CollectionStart AND s.T < c.CollectionEnd) as Agg
GROUP BY Agg.T;

Violín de SQL

1
user3561813 26 jun. 2020 a las 14:41

Solo una unión izquierda en la intersección de intervalo

select 
    st.t,
    count(cs.ScheduleID) n
from
    ScheduleTimes st
    left join CollectionSchedule cs on cs.CollectionStart < dateadd(minute, 15, st.t) and  st.t < cs.CollectionEnd
group by st.t
0
Serg 26 jun. 2020 a las 14:01