Get the invoiced amount from the previous month

Asked

Viewed 378 times

0

Good evening staff. I am a student beginner in database and would like to take a question for study purposes.

Suppose I have the following structure:

MES           QTDUSUARIOS       MUNICIPIO       VALORFATURADO

2018-01       136               SERRA           2000
2018-01       108               CAMBURI         1000
2018-02       500               SERRA           1000
2018-02       400               CAMBURI         1500
2018-03       200               SERRA           500
2018-03       120               CAMBURI         500

And create a new column containing the amount invoiced in the previous month, example:

MES           QTDUSUARIOS       MUNICIPIO       VALORFATURADO  VALOR_MES_ANTERIOR

2018-02       500               SERRA           1000           2000
2018-02       400               CAMBURI         1500           1000
2018-03       200               SERRA           500            1000
2018-03       120               CAMBURI         500            1500

How would I do it? Currently, I perform the consultation in this way:

SELECT      TO_CHAR(DATA, 'YYYY-MM') AS MES, 
            SUM(QTDUSUARIOS) AS QTDUSUARIOS, 
            MUNICIPIO, 
            SUM(VALORFATURADO) AS VALORFATURADO 
FROM        UBER 
GROUP BY    TO_CHAR(DATA, 'YYYY-MM'),
            MUNICIPIO 
ORDER BY    1;

Script of table creation:

CREATE TABLE    UBER (
                ID NUMBER, 
                DATA DATE, 
                QTDUSUARIOS NUMBER(6),
                MUNICIPIO VARCHAR2(50),
                VALORFATURADO NUMBER(8,2) 
)

Example of data entry:

Insert into UBER (ID,DATA,QTDUSUARIOS,MUNICIPIO,VALORFATURADO) values ('1',to_date('10/01/2018 00:00:00','DD/MM/YYYY HH24:MI:SS'),'136','SERRA','2000');
sql oracle pl-sql

  • Make a subselect as a column.

  • Good evening Motta. Would you have examples to complement your answer? Links, tips, anything. Thank you!

  • Good note @Codeman would be very useful if instead of simple examples you put the script for creating this table and inserting the data, so it would be much easier to reproduce what you have and send you a more complete answer

  • Yes, sorry. I updated the posting description, thanks for the notice!

2 answers

3


One solution to your problem is to make a column with the sub-select, with the where and the data you need.

The full SQL is in http://sqlfiddle.com/#! 4/610c68/13

Table creation

CREATE TABLE    UBER (
                ID NUMBER, 
                DATA DATE, 
                QTDUSUARIOS NUMBER(6),
                MUNICIPIO VARCHAR2(50),
                VALORFATURADO NUMBER(8,2) 
);

Inserts

Insert into UBER (ID,DATA,QTDUSUARIOS,MUNICIPIO,VALORFATURADO) values ('1',to_date('10/01/2018 00:00:00','DD/MM/YYYY HH24:MI:SS'),'136','SERRA','2000');
Insert into UBER (ID,DATA,QTDUSUARIOS,MUNICIPIO,VALORFATURADO) values ('1',to_date('10/01/2018 00:00:00','DD/MM/YYYY HH24:MI:SS'),'108','CAMBURI','1000');
Insert into UBER (ID,DATA,QTDUSUARIOS,MUNICIPIO,VALORFATURADO) values ('1',to_date('10/02/2018 00:00:00','DD/MM/YYYY HH24:MI:SS'),'500','SERRA','1000');
Insert into UBER (ID,DATA,QTDUSUARIOS,MUNICIPIO,VALORFATURADO) values ('1',to_date('10/02/2018 00:00:00','DD/MM/YYYY HH24:MI:SS'),'400','CAMBURI','1500');
Insert into UBER (ID,DATA,QTDUSUARIOS,MUNICIPIO,VALORFATURADO) values ('1',to_date('10/03/2018 00:00:00','DD/MM/YYYY HH24:MI:SS'),'200','SERRA','500');
Insert into UBER (ID,DATA,QTDUSUARIOS,MUNICIPIO,VALORFATURADO) values ('1',to_date('10/03/2018 00:00:00','DD/MM/YYYY HH24:MI:SS'),'400','CAMBURI','500');

SELECT

SELECT      TO_CHAR(DATA, 'YYYY-MM') AS MES, 
            SUM(QTDUSUARIOS) AS QTDUSUARIOS, 
            MUNICIPIO, 
            SUM(VALORFATURADO) AS VALORFATURADO,
            (
                SELECT 
                  SUM(a.VALORFATURADO) 
                FROM 
                  UBER a
                WHERE 
                   TO_CHAR(a.DATA, 'YYYY-MM') = TO_CHAR(ADD_MONTHS(b.DATA, -1), 'YYYY-MM')
                  AND a.MUNICIPIO = b.MUNICIPIO) as VALOR_MES_ANTERIOR
FROM        UBER b
GROUP BY    TO_CHAR(DATA, 'YYYY-MM'),
            MUNICIPIO ,
            TO_CHAR(ADD_MONTHS(DATA, -1), 'YYYY-MM')
ORDER BY    1;

Upshot

|     MES | QTDUSUARIOS | MUNICIPIO | VALORFATURADO | VALOR_MES_ANTERIOR |
|---------|-------------|-----------|---------------|--------------------|
| 2018-01 |         108 |   CAMBURI |          1000 |             (null) |
| 2018-01 |         136 |     SERRA |          2000 |             (null) |
| 2018-02 |         400 |   CAMBURI |          1500 |               1000 |
| 2018-02 |         500 |     SERRA |          1000 |               2000 |
| 2018-03 |         400 |   CAMBURI |           500 |               1500 |
| 2018-03 |         200 |     SERRA |           500 |               1000 |

  • Sensational, it worked like a glove. Grateful for the guidance given Tiedt.

1

This way you can have the amount billed from the previous month but also the amount of users and the previous month, if necessary:

SELECT      U.MUNICIPIO
        ,   U1.MES
        ,   U1.QTDUSUARIOS
        ,   U1.VALORFATURADO
        ,   U2.MES              AS MES_ANTERIOR
        ,   U2.QTDUSUARIOS      AS QTDUSUARIOS_MES_ANTERIOR
        ,   U2.VALORFATURADO    AS VALOR_MES_ANTERIOR
FROM        UBER    U
LEFT JOIN   (
                SELECT      TO_CHAR(DATA, 'YYYY-MM') AS MES
                        ,   SUM(QTDUSUARIOS)    AS QTDUSUARIOS
                        ,   MUNICIPIO
                        ,   SUM(VALORFATURADO)  AS VALORFATURADO 
                FROM        UBER 
                GROUP BY    TO_CHAR(DATA, 'YYYY-MM')
                        ,   MUNICIPIO 
            )       U1 ON U1.M = U.MUNICIPIO
LEFT JOIN   (
                SELECT      TO_CHAR(ADD_MONTHS(DATA, -1), 'YYYY-MM') AS MES
                        ,   SUM(QTDUSUARIOS)    AS QTDUSUARIOS
                        ,   MUNICIPIO
                        ,   SUM(VALORFATURADO)  AS VALORFATURADO 
                FROM        UBER 
                GROUP BY    TO_CHAR(ADD_MONTHS(DATA, -1), 'YYYY-MM')
                        ,   MUNICIPIO 
            )       U2 ON U2.M = U.MUNICIPIO
ORDER BY    U1.MES;

Browser other questions tagged sql oracle pl-sql

You are not signed in. Login or sign up in order to post.