How to indicate with value 0 that there are no records in SQL?

0

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.

    
asked by Molitaa 03.07.2017 в 17:24
source

1 answer

0

Assuming that when you make this query you get records:

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

You just have to make the next meeting indicating that you want to obtain if or if all the records you currently have, something like this:

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

Then if the above is showing you results is when you proceed to apply the corresponding filters using WHERE or HAVING taking into account whether it is a filter during the consultation or prior to it.

    
answered by 03.07.2017 в 17:43