Question:
I need to check the number of notes issued with model 55 to create a report. The problem is that I need to put zero values in the quantity column even though I don't have any value.
Ex:
empresa filial data quantidade
5 1 01/2012 30
5 1 02/2012 42
5 1 03/2012 30
5 1 04/2012 52
5 2 01/2012 33
5 2 02/2012 42
However, I've consulted the entire year, specifically I'd like it to look like this:
empresa filial data quantidade
5 1 01/2012 30
5 1 02/2012 42
5 1 03/2012 30
5 1 04/2012 52
5 1 05/2012 0
5 1 06/2012 0
5 1 07/2012 0
5 1 08/2012 0
5 1 09/2012 0
5 1 10/2012 0
5 1 11/2012 0
5 1 12/2012 0
5 2 01/2012 33
5 2 02/2012 42
5 2 03/2012 0
5 2 04/2012 0
5 2 05/2012 0
5 2 06/2012 0
5 2 07/2012 0
5 2 08/2012 0
5 2 09/2012 0
5 2 10/2012 0
5 2 11/2012 0
5 2 12/2012 0
I've tried with coalesce(quantidade, 0) , case when(quantidade is null) then quantidade = 0 and even some procedures, but I failed miserably.
In summary: Is it possible to "force" count() to also inform null values as 0 even though there is no information?
Edit:
Consultations being carried out:
As requested, I will put as I am doing, I was going to put a dummy code, but I will inform a real one, I believe it is clearer. SQL Query:
SELECT E.EMPRESA,F.NOMEFANTASIA,F.FILIAL,
EXTRACT(MONTH FROM S.DATASAIDA)||'/'||EXTRACT(YEAR FROM S.DATASAIDA) AS DATASAIDA,
S.MODELO, COUNT(*) AS QUANTIDADE
FROM ES02_SAIDA S
INNER JOIN SC00_FILIAL F ON (F.IDFILIAL = S.IDFILIAL)
INNER JOIN SC00_EMPRESA E ON (E.IDEMPRESA = F.IDEMPRESA)
WHERE E.EMPRESA IN (:IDEMPRESA)
AND S.DATASAIDA BETWEEN ''||:DATAINI||'' AND ''||:DATAFIM||''
AND (S.MODELO = '55' OR S.MODELO = '57')
GROUP BY E.EMPRESA,F.NOMEFANTASIA,F.FILIAL,
EXTRACT(MONTH FROM S.DATASAIDA)||'/'||EXTRACT(YEAR FROM S.DATASAIDA),
S.MODELO
ORDER BY 5,1,2
Result:
EMPRESA NOMEFANTASIA FILIAL DATASAIDA MODELO QUANTIDADE
5 EMPRESA 1 1/2012 55 44
5 EMPRESA 1 2/2012 55 34
5 EMPRESA 1 3/2012 55 53
5 EMPRESA 1 4/2012 55 48
5 EMPRESA 1 5/2012 55 76
5 EMPRESA 1 6/2012 55 39
5 EMPRESA 1 7/2012 55 22
5 EMPRESA 1 8/2012 55 34
5 EMPRESA 1 9/2012 55 32
5 EMPRESA 1 10/2012 55 39
5 EMPRESA 1 11/2012 55 38
5 EMPRESA 1 12/2012 55 77
5 EMPRESA2 2 1/2012 55 38
5 EMPRESA2 2 2/2012 55 59
5 EMPRESA2 2 3/2012 55 53
5 EMPRESA2 2 4/2012 55 8
Edit 2:
Good afternoon, thank you very much for your help and trying to solve my question. However, I come back with another question for you that would be about the use of UNION . I'm using the following SQL:
SELECT TABELA.EMPRESA, TABELA.NOMEFANTASIA, TABELA.FILIAL, TABELA.DATASAIDA, TABELA.MODELO, COUNT(TABELA.MODELO) AS QUANTIDADE FROM( SELECT E.EMPRESA,F.NOMEFANTASIA,F.FILIAL, EXTRACT(MONTH FROM S.DATASAIDA)||'/'||EXTRACT(YEAR FROM S.DATASAIDA) AS DATASAIDA, S.MODELO, COUNT(*) AS QUANTIDADE FROM ES02_SAIDA S INNER JOIN SC00_FILIAL F ON (F.IDFILIAL = S.IDFILIAL) INNER JOIN SC00_EMPRESA E ON (E.IDEMPRESA = F.IDEMPRESA) WHERE E.EMPRESA IN (:IDEMPRESA) AND S.DATASAIDA BETWEEN ''||:DATAINI||'' AND ''||:DATAFIM||'' AND (S.MODELO = '55' OR S.MODELO = '57') group by 1,2,3,4,5 UNION SELECT distinct NULL AS EMPRESA,'TOTAIS POR FILIAL' AS NOMEFANTASIA,F.filial AS FILIAL, null AS DATASAIDA,null as MODELO, --'QTD='||(SUM(1))||' MÉDIA= '||(SUM(1)/COUNT(S.modelo)) AS QUANTIDADE (SUM(1)/COUNT(S.modelo)) AS QUANTIDADE FROM ES02_SAIDA S INNER JOIN SC00_FILIAL F ON (F.IDFILIAL = S.IDFILIAL) INNER JOIN SC00_EMPRESA E ON (E.IDEMPRESA = F.IDEMPRESA) WHERE E.EMPRESA IN (:IDEMPRESA) AND S.DATASAIDA not BETWEEN ''||:DATAINI||'' AND ''||:DATAFIM||'' AND (S.MODELO = '55' OR S.MODELO = '57') group by 1,2,3,4,5 )TABELA GROUP BY TABELA.EMPRESA, TABELA.NOMEFANTASIA, TABELA.FILIAL, TABELA.DATASAIDA, TABELA.MODELO ORDER BY TABELA.FILIAL asc nulls last, TABELA.DATASAIDA asc nulls last, TABELA.EMPRESA asc nulls last
Answer:
For this kind of problem, I created a procedure that returns the days of a given period:
SET TERM ^ ;
create or alter procedure DIAS_PERIODO (
INICIO date,
FIM date)
returns (
DIA date)
as
begin
DIA = :INICIO;
WHILE (DIA <= :FIM) DO
BEGIN
suspend;
DIA = DIA + 1;
END
end^
SET TERM ; ^
Then you can do an SQL in this style:
select count(t.ID),
d.DIA
from (select dia from DIAS_PERIODO('01.01.2017', '31.01.2017')) d left JOIN
tramitacao t on t.DATA = d.DIA
group by d.dia
(SQL different from yours because I tested here with a table of my system that has dates)
Result:
COUNT DIA
0 01/01/2017
24 02/01/2017
23 03/01/2017
13 04/01/2017
22 05/01/2017
78 06/01/2017
0 07/01/2017
0 08/01/2017
55 09/01/2017
42 10/01/2017
27 11/01/2017
47 12/01/2017
94 13/01/2017
0 14/01/2017
0 15/01/2017
28 16/01/2017
101 17/01/2017
81 18/01/2017
49 19/01/2017
68 20/01/2017
0 21/01/2017
0 22/01/2017
90 23/01/2017
110 24/01/2017
89 25/01/2017
86 26/01/2017
126 27/01/2017
0 28/01/2017
0 29/01/2017
65 30/01/2017
154 31/01/2017