Creating a quarterly report in SQL

Question

I'm creating a quarterly report of item usage in Firebird, and got stuck at this point:

The select below is working, but it brings me the monthly usage of last 3 months like the image below:

Script:

SELECT
--(SELECT SUM(L.QTDE) FROM GELANSAI L WHERE L.CONSOL = 'T' AND (L.MES = (EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE))) AND L.ANO = 2023) GROUP BY L.ITEM) AS CONSUMO1,
--(SELECT SUM(L.QTDE) FROM GELANSAI L WHERE L.CONSOL = 'T' AND (L.MES = (EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE))) AND L.ANO = 2022) GROUP BY L.ITEM) AS CONSUMO2,
--(SELECT SUM(L.QTDE) FROM GELANSAI L WHERE L.CONSOL = 'T' AND (L.MES = (EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE))) AND L.ANO = 2022) GROUP BY L.ITEM) AS CONSUMO3,
C.MES,
C.ANO,
L.ITEM,
I.NOME,
I.UNI_CON,
I.CONVER,
I.UNI_COMP,
MAX(I.EST_MAX) EST_MAXIMO,
MAX(I.CUSTO) PRECO,
MAX(EST_MIN) EST_MINIMO,
MAX(I.ESTOQUE) ESTOQUE,
SUM(L.QTDE) QUANTIDADE
FROM GECADSAI C INNER JOIN GELANSAI L ON C.ANO = L.ANO AND C.MES = L.MES AND C.DOC = L.DOC
LEFT JOIN GEITENS I ON L.ITEM = I.COD
    WHERE   (((C.MES = (EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE))) AND C.ANO = 2023 ))
    OR ((C.MES = (EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE)))) AND C.ANO = 2022)
    OR (C.MES = (EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE))) AND C.ANO = 2022))
        AND C.CDC NOT BETWEEN 9901 AND 9999
        AND I.REF = 1
        AND L.CONSOL = 'T'
        AND C.CONSOL = 'T'
             GROUP BY L.ITEM, I.NOME, C.ANO, C.MES, I.UNI_CON, I.VLRMED, I.UNI_COMP, I.CONVER

Result:

But, as you can see, the QUANTIDADE column split monthly line by line the total usage.

ITEM_NAME	MONTH	USAGE
ITEM A	JAN	7000
ITEM A	DEZ	3000
ITEM A	NOV	4000
ITEM B	JAN	200
ITEM B	DEZ	350
ITEM B	NOV	500

And I'd like to, each month of consumption to be a column of my select, so, it will be something like this:

ITEM_NAME	JAN	DEZ	NOV
ITEM A	7000	3000	4000
ITEM B	200	350	500

I also tried the commented subselect, but it will for sure returns me, multiple rows error.

SELECT
--(SELECT SUM(L.QTDE) FROM GELANSAI L WHERE L.CONSOL = 'T' AND (L.MES = (EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE))) AND L.ANO = 2023) GROUP BY L.ITEM) AS CONSUMO1,
--(SELECT SUM(L.QTDE) FROM GELANSAI L WHERE L.CONSOL = 'T' AND (L.MES = (EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE))) AND L.ANO = 2022) GROUP BY L.ITEM) AS CONSUMO2,
--(SELECT SUM(L.QTDE) FROM GELANSAI L WHERE L.CONSOL = 'T' AND (L.MES = (EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE))) AND L.ANO = 2022) GROUP BY L.ITEM) AS CONSUMO3,
C.MES,
C.ANO,
L.ITEM,
I.NOME,
I.UNI_CON,
I.CONVER,
I.UNI_COMP,
MAX(I.EST_MAX) EST_MAXIMO,
MAX(I.CUSTO) PRECO,
MAX(EST_MIN) EST_MINIMO,
MAX(I.ESTOQUE) ESTOQUE,
SUM(L.QTDE) QUANTIDADE
FROM GECADSAI C INNER JOIN GELANSAI L ON C.ANO = L.ANO AND C.MES = L.MES AND C.DOC = L.DOC
LEFT JOIN GEITENS I ON L.ITEM = I.COD
WHERE   (((C.MES = (EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE))) AND C.ANO = 2023 ))
    OR ((C.MES = (EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE)))) AND C.ANO = 2022)
    OR (C.MES = (EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE))) AND C.ANO = 2022))
AND C.CDC NOT BETWEEN 9901 AND 9999
AND I.REF = 1
AND L.CONSOL = 'T'
AND C.CONSOL = 'T'
GROUP BY L.ITEM, I.NOME, C.ANO, C.MES, I.UNI_CON, I.VLRMED, I.UNI_COMP, I.CONVER

Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Feb 17 '23 at 08:55
[Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) — philipxy, Feb 17 '23 at 08:56
Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad definition/query/function with the obligatory [mre], including why you think it should return something else or are unsure at the 1st subexpression that it doesn't give what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & ideally a [mre]. Then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. [ask] [Help] — philipxy, Feb 21 '23 at 11:19

score 1 · Accepted Answer · answered Feb 21 '23 at 13:42

It is not possible to create a pivot with dynamic column names that depend on a date. However you can pivot values. Under the assumption that your first query produces the correct results, but just not the correct layout, you can do something like this:

SELECT
  L.ITEM,
  I.NOME,
  I.UNI_CON,
  I.CONVER,
  I.UNI_COMP,
  MAX(I.EST_MAX) EST_MAXIMO,
  MAX(I.CUSTO) PRECO,
  MAX(EST_MIN) EST_MINIMO,
  MAX(I.ESTOQUE) ESTOQUE,
  -- first month (3 months ago)
  max(case when C.MES = EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE)) AND C.ANO = 2022 then C.MES end) M1, 
  sum(case when C.MES = EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE)) AND C.ANO = 2022 then L.QTDE end) QUANTIDADE_M1
  -- second month (2 months ago)
  max(case when C.MES = EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE)) AND C.ANO = 2022 then C.MES end) M2, 
  sum(case when C.MES = EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE)) AND C.ANO = 2022 then L.QTDE end) QUANTIDADE_M1
  -- third month (1 month ago)
  max(case when C.MES = EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE)) AND C.ANO = 2023 then C.MES end) M3, 
  sum(case when C.MES = EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE)) AND C.ANO = 2023 then L.QTDE end) QUANTIDADE_M3
FROM GECADSAI C 
INNER JOIN GELANSAI L ON C.ANO = L.ANO AND C.MES = L.MES AND C.DOC = L.DOC
LEFT JOIN GEITENS I ON L.ITEM = I.COD
WHERE (((C.MES = (EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE))) AND C.ANO = 2023 ))
    OR ((C.MES = (EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE)))) AND C.ANO = 2022)
    OR (C.MES = (EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE))) AND C.ANO = 2022))
AND C.CDC NOT BETWEEN 9901 AND 9999
AND I.REF = 1
AND L.CONSOL = 'T'
AND C.CONSOL = 'T'
GROUP BY L.ITEM, I.NOME, I.UNI_CON, I.VLRMED, I.UNI_COMP, I.CONVER

That is, you sum the values for 3 months ago, 2 months ago and 1 month ago, and add a column that identifies the month (which must also use an aggregate column) as you can't generate the column name dynamically. The alternative is to generate columns for all twelve months.

The C.MES and C.ANO columns are no longer part of the GROUP BY.

Creating a quarterly report in SQL

1 Answers1