I have the following query in SQL where I am getting the Incidents of the employees in a report, this by the Date From and Date Until, in that period I must show the total number of Incidents (Fouls, permits, holidays) what it has, with what I have, I get the incidents correctly.
declare @numOperador int, @FechaDesde datetime, @FechaHasta datetime
set @FechaDesde='2017-05-01 00:00:00.000'
set @FechaHasta='2017-06-30 00:00:00.000'
set @numOperador=450135
SELECT CC.NumOperador,
(Nombre + ' ' + ApPaterno + ' ' + ApMaterno) AS Operador,
j.Descripcion,
Incidencias=isnull(COALESCE(SUM(DATEDIFF(DAY, Fecha_Ini, Fecha_Fin) + 1), 0),0)
FROM trkOperadores O
JOIN trkJornada J ON J.cveJornada = O.cveJornada
JOIN CopCalendario CC ON CC.NumOperador = O.NumOperador
JOIN CopCalendarioIncidencias CPI on CC.idNumOperador=CPI.idNumOperador
WHERE O.cveTipoOperador = 2
AND (CC.NumOperador = @NumOperador OR @NumOperador = 0)
AND ((isnull(CPI.Fecha_Ini,0)=0 and isnull(CPI.Fecha_Fin,0)=0) or (CPI.Fecha_Ini >=@FechaDesde and CPI.Fecha_Fin <=@FechaHasta))
AND (CPI.Fecha_Ini>=CC.Jornada_Ini )
GROUP BY CC.NumOperador,(Nombre + ' ' + ApPaterno + ' ' + ApMaterno), j.Descripcion, CC.Jornada_Ini, J.DiasLabores, J.DiasDescanso
But how can I do to indicate that if in my Table of CopCalendarioIncidencias there is no record of one I still the name of the Operator with its NumOperator and the incidences with a value of 0.
When I modify the dates to a date that does not have a record of incidents, the table is left as follows:
Modified
I have modified at the time of obtaining the incident I have removed isnull, leaving as follows:
declare @numOperador int, @FechaDesde datetime, @FechaHasta datetime
set @FechaDesde='2017-05-01 00:00:00.000'
set @FechaHasta='2017-06-30 00:00:00.000'
set @numOperador=450135
SELECT CC.NumOperador,
--(Nombre + ' ' + ApPaterno + ' ' + ApMaterno) AS Operador,
j.Descripcion,
Incidencias=COALESCE(SUM(DATEDIFF(DAY, Fecha_Ini, Fecha_Fin) + 1), 0)
FROM trkOperadores O
JOIN trkJornada J ON J.cveJornada = O.cveJornada
JOIN CopCalendario CC ON CC.NumOperador = O.NumOperador
LEFT JOIN CopCalendarioIncidencias CPI on CC.idNumOperador=CPI.idNumOperador
where O.cveTipoOperador = 2
AND (CC.NumOperador = @NumOperador OR @NumOperador = 0)
--AND ( (CPI.Fecha_Ini >=@FechaDesde and CPI.Fecha_Fin <=@FechaHasta)
--and (CPI.Fecha_Ini>=CC.Jornada_Ini ) )
GROUP BY CC.NumOperador,(Nombre + ' ' + ApPaterno + ' ' + ApMaterno), j.Descripcion, CC.Jornada_Ini, J.DiasLabores, J.DiasDescanso
with this I get that it has 5 Incidences
But I want to show only the incidents that are greater or equal to the start date and greater than or equal to my date Since then I enable what I have commented in the query:
AND ( (CPI.Fecha_Ini >=@FechaDesde and CPI.Fecha_Fin <=@FechaHasta)
and (CPI.Fecha_Ini>=CC.Jornada_Ini ) )
and it does not give me results.
In the Image, this is the result of my query, I also place the Initial Session of my Copcalendar table and the dates of the incidents of CopCalendarioIncidencias, to see if I am missing something that I have not noticed I would appreciate it they will indicate it.