Sql - Merge select - Create row if none exist

Navigation
0

I want to join two SELECT , but I'm unsuccessful.

It has the following tables: 

CREATE TABLE tb_producao
(
  cd_producao SERIAL,
  cd_setor integer,
  nr_quantidade numeric(12,2),
  ds_producao character varying(255)
);

CREATE TABLE tb_mapa_producao
(
  cd_mapa_producao SERIAL,
  cd_lote_producao_ordem_ticket integer,
  cd_setor integer,
  dt_inicio timestamp without time zone
);

CREATE TABLE tb_setor
(
  cd_setor SERIAL,
  ds_setor character varying(200) NOT NULL
);

CREATE TABLE tb_lote_producao_ordem_ticket
(
  cd_lote_producao_ordem_ticket SERIAL,
  cd_lote_producao_ordem integer,
  nr_quantidade numeric(12,0) DEFAULT 0
);

And the following SQL: 

SELECT
    S.cd_setor as "cd_setor", 
    SUM(COALESCE(LPOT.nr_quantidade,0)) as "nr_total_ticket",
    SUM(CASE MP.fl_desativado WHEN TRUE THEN LPOT.nr_quantidade ELSE 0 END) as "nr_total_concluido",
    (   
        SELECT  COALESCE((SUM(COALESCE(P.nr_quantidade,0)) / CASE WHEN count(P.cd_producao) = 0 THEN 1 ELSE count(P.cd_producao) END)::numeric(12,2),0) as "P.ds_media_producao"
        FROM    tb_producao P 
        WHERE   P.cd_setor = S.cd_setor
    ) as "nr_media_producao"
    ,date_trunc('day', CASE WHEN MP.dt_inicio IS NULL THEN now() ELSE MP.dt_inicio END) as "dt_producao"
FROM
    tb_setor S
    LEFT JOIN tb_mapa_producao MP ON S.cd_setor = MP.cd_setor
    LEFT JOIN tb_lote_producao_ordem_ticket LPOT ON LPOT.cd_lote_producao_ordem_ticket = MP.cd_lote_producao_ordem_ticket
WHERE
    (
        (date_trunc('day', MP.dt_inicio) = date_trunc('day', now()))
        OR MP.cd_mapa_producao IS NULL
    )
GROUP BY    1,5
UNION
    SELECT
        S.cd_setor as "cd_setor", 
        null,
        null,
        (   
            SELECT  COALESCE((SUM(COALESCE(P.nr_quantidade,0)) / CASE WHEN count(P.cd_producao) = 0 THEN 1 ELSE count(P.cd_producao) END)::numeric(12,2),0) as "P.ds_media_producao"
            FROM    tb_producao P 
            WHERE   P.cd_setor = S.cd_setor
        ) as "nr_media_producao",
        now()
    FROM
        tb_setor S
ORDER BY 
    1,5

Result: 

1   |   120 |   70  |   1000.00 |   "2015-10-14 00:00:00-03"
1   |       |       |   1000.00 |   "2015-10-14 11:02:52.183443-03"
2   |   120 |   70  |   870.00  |   "2015-10-14 00:00:00-03"
2   |       |       |   870.00  |   "2015-10-14 11:02:52.183443-03"
3   |   0   |   0   |   733.33  |   "2015-10-14 00:00:00-03"
3   |       |       |   733.33  |   "2015-10-14 11:02:52.183443-03"
4   |   0   |   0   |   850.00  |   "2015-10-14 00:00:00-03"
4   |       |       |   850.00  |   "2015-10-14 11:02:52.183443-03"
5   |   120 |   70  |   950.00  |   "2015-10-14 00:00:00-03"
5   |       |       |   950.00  |   "2015-10-14 11:02:52.183443-03"
6   |   120 |   110 |   900.00  |   "2015-10-14 00:00:00-03"
6   |       |       |   900.00  |   "2015-10-14 11:02:52.183443-03"
7   |       |       |   966.67  |   "2015-10-14 11:02:52.183443-03"
8   |   0   |   0   |   866.67  |   "2015-10-14 00:00:00-03"
8   |       |       |   866.67  |   "2015-10-14 11:02:52.183443-03"
9   |       |       |   690.00  |   "2015-10-14 11:02:52.183443-03"
10  |   0   |   0   |   600.00  |   "2015-10-14 00:00:00-03"
10  |       |       |   600.00  |   "2015-10-14 11:02:52.183443-03"

Expected result 

1   |   120 |   70  |   1000.00 |   "2015-10-14 00:00:00-03"
2   |   120 |   70  |   870.00  |   "2015-10-14 00:00:00-03"
3   |   0   |   0   |   733.33  |   "2015-10-14 00:00:00-03"
4   |   0   |   0   |   850.00  |   "2015-10-14 00:00:00-03"
5   |   120 |   70  |   950.00  |   "2015-10-14 00:00:00-03"
6   |   120 |   110 |   900.00  |   "2015-10-14 00:00:00-03"
7   |   0   |   0   |   966.67  |   "2015-10-14 11:02:52.183443-03"
8   |   0   |   0   |   866.67  |   "2015-10-14 00:00:00-03"
9   |   0   |   0   |   690.00  |   "2015-10-14 11:02:52.183443-03"
10  |   0   |   0   |   600.00  |   "2015-10-14 00:00:00-03"

Objective

I want to calculate the productivity of the current day, but if there is no production on the day, you should only calculate the% of sector% and keep the other fields with nr_media_producao or 0 .

Situation

With now() I was able to always calculate the UNION , but when there is production in the day it generates duplicate records, ie I would like a nr_media_producao that preferably for the merge that has production on the day .

OBS

I would like to use query to demonstrate the data, but it generates error when I try to compile. due to many lines.

    
asked by anonymous 14.10.2015 / 16:31

1 answer

2

Explanation

You can use your select as the basis of a derived table, making it possible to use a filter to keep other fields with 0 or now() .

We use the ROW_NUMBER() function to count how many rows are repeated (partitioned by ID , since it repeats), and ordered that columns that are not NULL have "priority" in ordering, in this case , we use a derived table as the basis for another derived table.

In the end, it was only necessary to select the columns separately so that the row count was not displayed.

Query 

 SELECT 
      CD_SETOR, 
      NR_TOTAL_TICKET, 
      NR_MEDIA_PRODUCAO,
      NR_TOTAL_CONCLUIDO
 FROM (
         SELECT *, ROW_NUMBER() OVER (PARTITION BY cd_setor ORDER BY NR_TOTAL_TICKET ASC) RN FROM (   
                SELECT
                    S.cd_setor as "cd_setor", 
                    SUM(COALESCE(LPOT.nr_quantidade,0)) as "nr_total_ticket",
                    SUM(CASE MP.fl_desativado WHEN TRUE THEN LPOT.nr_quantidade ELSE 0 END) as "nr_total_concluido",
                    (   
                        SELECT  COALESCE((SUM(COALESCE(P.nr_quantidade,0)) / CASE WHEN count(P.cd_producao) = 0 THEN 1 ELSE count(P.cd_producao) END)::numeric(12,2),0) as "P.ds_media_producao"
                        FROM    tb_producao P 
                        WHERE   P.cd_setor = S.cd_setor
                    ) as "nr_media_producao"
                    ,date_trunc('day', CASE WHEN MP.dt_inicio IS NULL THEN now() ELSE MP.dt_inicio END) as "dt_producao"
                FROM
                    tb_setor S
                    LEFT JOIN tb_mapa_producao MP ON S.cd_setor = MP.cd_setor
                    LEFT JOIN tb_lote_producao_ordem_ticket LPOT ON LPOT.cd_lote_producao_ordem_ticket = MP.cd_lote_producao_ordem_ticket
                WHERE
                    (
                        (date_trunc('day', MP.dt_inicio) = date_trunc('day', now()))
                        OR MP.cd_mapa_producao IS NULL
                    )
                GROUP BY    1,5
                UNION
                    SELECT
                        S.cd_setor as "cd_setor", 
                        null,
                        null,
                        (   
                            SELECT  COALESCE((SUM(COALESCE(P.nr_quantidade,0)) / CASE WHEN count(P.cd_producao) = 0 THEN 1 ELSE count(P.cd_producao) END)::numeric(12,2),0) as "P.ds_media_producao"
                            FROM    tb_producao P 
                            WHERE   P.cd_setor = S.cd_setor
                        ) as "nr_media_producao",
                        now()
                    FROM
                        tb_setor S
                ORDER BY 
                    1,5 ) A ) B WHERE B.RN = 1
    
14.10.2015 / 18:00
Find content according to two Combobox ImageView Error can not be converted to Byte []