I am trying to create a query using a function of dates and a table of shifts, which can show me the shifts of workers each day, when I have a shift or rest depending on the day,
What do I have: I have a date function that gives me a range of dates that I add, I attach an example:
I have a table of shifts, with only the days that a person has a shift, if a day has a break, the date or the row does not appear, I attach an example:
It can be seen that in the shift table there are only records when a person has a shift.
Problem: when I perform the join between the function and the shift table through the date field, the result is that it only shows me the record when it has a shift and no, it does not put the date when it has a break, I attach an example:
Desired result:
The idea is that when the worker has a break, the row will be blank, only showing the date and his ID, or saying the word break.
I hope you can help me. Thank you so much.
CodePudding user response:
Use LEFT JOIN for avoiding few date missing which has transaction in table. Use two subquery here for getting appropriate result. In first subquery function CROSS JOIN with transaction table where retrieving distinct id_trabajador for specified date range. If it doesn't do then id will blank in result where no transaction exists for specific id in a certain date. In second subquery retrieve all rows for given date range.
-- SQL Server
SELECT tmp.fecha, tmp.id_trabajador
, tmd.inicio, tmd.termino
, COALESCE(CAST(tmd.jornada AS varchar(20)), 'DESCANSO') jornada
FROM (SELECT * FROM shift_cmr..fnRangoFechas('01-sep-2021', '31-dec-2021') t
CROSS JOIN (SELECT id_trabajador
FROM shift_cmr..trabajadores_turnos_planificados
WHERE fecha BETWEEN '2021-09-01' AND '2021-12-31'
GROUP BY id_trabajador) tt
) tmp
LEFT JOIN (SELECT *
FROM shift_cmr..trabajadores_turnos_planificados
WHERE fecha BETWEEN '2021-09-01' AND '2021-12-31') tmd
ON tmp.fecha = tmd.fecha
AND tmp.id_trabajador = tmd.id_trabajador
CodePudding user response:
You need to start with the date table and LEFT JOIN
everything else
SELECT
dates.fecha,
sh.id_trabajador,
sh.inicio,
sh.termino,
jornada = ISNULL(CAST(sh.jornada AS varchar(10)), 'DESCANSO')
FROM shift_cmr..fnRangoFechas('01-sep-2021', '31-dec-2021') dates
LEFT JOIN shift_cmr..trabajadores_turnos_planificados sh
ON sh.fecha = dates.fecha
This only gives you one blank row per date. If you need a blank row for every id_trabajador
then you need to cross join that
SELECT
dates.fecha,
t.id_trabajador,
sh.inicio,
sh.termino,
jornada = ISNULL(CAST(sh.jornada AS varchar(10)), 'DESCANSO')
FROM shift_cmr..fnRangoFechas('01-sep-2021', '31-dec-2021') dates
CROSS JOIN shift_cmr..trabajadores t -- guessing the table name
LEFT JOIN shift_cmr..trabajadores_turnos_planificados sh
ON sh.fecha = dates.fecha AND t.id_trabajador = sh.id_trabajador