SQL - Return zeroed records

Question

Good morning,

I have a query that is only returning the months with sales consumption, which should be correct. Only now a blessed user wants me to return every month, including those who have no sales. How do I return to zero including what is being deleted in the query? rs.

Follows code:

SELECT Substring(C5_EMISSAO, 1, 6) AS EMISSAO, 
   Sum(C6_QTDVEN - C6_QTDENT)  AS PERDA 
FROM   SC5010 SC5 WITH (NOLOCK) 
       INNER JOIN SC6010 SC6 WITH (NOLOCK) 
               ON C6_FILIAL = '01' 
                  AND C6_NUM = C5_NUM 
                  AND C6_PRODUTO = '6020210'
                  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 '201507' AND '201606'
        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 

And this is the current comeback:

As you can see, they skip a few months. Remembering that the LOSS can be zero but in these months had sale (that does not appear in this consultation).

I need to bring every month of the break contained in Where

If anyone has the light at the end of the tunnel,

Hugs!

Answer 1

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

SQL (which uses Protheus table SRA020 as an example), is the following:

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 int field (primary key) and is populated with 999999 records (can be spared with more if needed).

Within these 999999 records, there will be, for example, the records (whole numbers): 201601, 201602, 201603 (which are also dates in the formed anomes).

Well, in the example above (which can be easily replicated to the question’s 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 date field (ie the year and month). Thereafter, it is sufficient to limit the period consulted in the WHERE.

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

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

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

Answer 2

Opa Caio, Face only a doubt is a normal query or this in a View?

Sure you can do the CASE Follow an example excerpt in Protheus in 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,

Answer 3

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

Thanks JMFROLIM for all the help too!

Hugs