SQL - Return records to zero

The solution below is a small example of how this can be solved in a simple way. It is based on this response and considers that there is a table named Numbers (or any other name) in the database . To create the table see this link .

SQL (which uses Protheus's SRA020 table as an example), is as follows:

SELECT 
 NUMBER ANO_MES,
 COUNT(RA_ADMISSA)
FROM 
 NUMBERS N 
 LEFT JOIN SRA020 
 ON (LEFT(RA_ADMISSA, 6) = NUMBER)
WHERE 
 NUMBER >= 201601 AND NUMBER <= 201608
GROUP BY NUMBER

Explaining:

The Numbers table is a table that has only one field of type int (primary key) and is populated with 999999 records (can be overflowed if needed).

Within these 999,999 records, there will be, for example the records (integers): 201601, 201602, 201603 (which are also dates in the formed anums ).

Well, in the example above (which can easily be replicated to the query SQL), the JOIN between the Protheus table and Number is done with a simple LEFT (RA_ADMISSA, 6), which returns the first 6 digits of the field (that is, the year and month). From there, simply limit the period consulted in WHERE.

In this small example, we show the number of admissions made from 01/2016 to 08/2016. Since in Jul and Ago they have not yet had admissions (or records in table SRA020), sql returns 0 for those periods.

This happens because the query is made on top of the NUMBERS and a LEFT JOIN is done with the SRA020, it is done like this, obviously all periods (integers) are in NUMBERS, but the opposite is not true .

Numbers is a Swiss army knife and as such has several uses. See here .

Opa Caio, Is only one question a normal query or is it in a View?

Sure you can do CASE Follow a sample excerpt in Protheus on table SC6

    CASE WHEN C6_BLQ = ' ' THEN (SC6.C6_QTDVEN - SC6.C6_QTDENT) ELSE 0 END 'SALDO',
,(SC6.C6_QTDVEN - SC6.C6_QTDENT) WHEN 0 THEN 'FATURADO' ELSE CASE SC9.C9_BLEST WHEN '' THEN 'LIBERADO' ELSE 'BLOQUEADO' END   END  AS STATUS, 
CASE WHEN C6_BLQ = ' ' THEN (SC6.C6_QTDVEN - SC6.C6_QTDENT) ELSE 0 END 'SALDO',
CASE (SC6.C6_QTDVEN - SC6.C6_QTDENT) WHEN 0 THEN 'FATURADO' ELSE CASE SC9.C9_BLEST WHEN '' THEN 'LIBERADO' ELSE 'BLOQUEADO' END   END  AS STATUS,

With the help of a colleague, we come to this end:

    SELECT Substring(C5_EMISSAO, 1, 6) AS EMISSAO, 
   Sum(C6_QTDVEN - C6_QTDENT)  AS PERDA 
   INTO #TEMP1 
FROM   SC5010 SC5 WITH (NOLOCK) 
       INNER JOIN SC6010 SC6 WITH (NOLOCK) 
               ON C6_FILIAL = '01' 
                  AND C6_NUM = C5_NUM 
                  AND C6_PRODUTO = @CPRODUTO
                  AND SC6.D_E_L_E_T_ = ' ' 
    LEFT JOIN SD2010 SD2 WITH (NOLOCK) 
              ON D2_FILIAL = '01' 
                 AND D2_PEDIDO = C5_NUM 
                 AND D2_COD = C6_PRODUTO 
                 AND D2_SERIE != 'R' 
                 AND SD2.D_E_L_E_T_ = ' ' 
    LEFT JOIN SF2010 SF2 WITH (NOLOCK) 
          ON F2_FILIAL = '01' 
             AND F2_DOC = C5_NOTA 
             AND F2_SERIE = C5_SERIE 
             AND SF2.D_E_L_E_T_ = ' ' 
WHERE  C5_FILIAL = '01' 
        AND Substring(C5_EMISSAO, 1, 6) BETWEEN @CDATAINI AND @CDATAFIM
        AND C5_MODAL = '2' 
        AND C5_NOTA != ' ' 
        AND SC5.D_E_L_E_T_ = ' ' 
GROUP  BY Substring(C5_EMISSAO, 1, 6) 
ORDER  BY Substring(C5_EMISSAO, 1, 6) DESC 

    --Cria temporaria com todos os meses de consulta
SELECT DISTINCT SUBSTRING(C5_EMISSAO, 1, 6) EMISSAO
    INTO #ANOMESSP
FROM SC5010 SC5
    WHERE D_E_L_E_T_ = ' ' 
    AND SUBSTRING(C5_EMISSAO, 1, 6) BETWEEN @CDATAINI AND @CDATAFIM

SELECT A.EMISSAO, ISNULL(B.PERDA, 0) PERDA 
    FROM #ANOMESSP A 
    LEFT JOIN #TEMP1 B on A.EMISSAO = B.EMISSAO
ORDER BY EMISSAO DESC

Thank you JMFROLIM for all the help too!

Hugs