Home > Enterprise >  How can I create a query in SQL Server, using as base table a date function and linking it to anothe
How can I create a query in SQL Server, using as base table a date function and linking it to anothe

Time:10-03

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:

function date

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:

shift

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:

enter image description here

Desired result:

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
  • Related