Sql - Select first and last record

Asked

Viewed 3,372 times

8

I have the following tables:

CREATE TABLE tb_lote
(
  id_lote integer NOT NULL DEFAULT nextval('tb_lote_seq'::regclass),
  ds_lote character varying(255),
  CONSTRAINT pktb_lote PRIMARY KEY (id_lote)
);

CREATE TABLE tb_lote_ticket
(
  id_lote_ticket integer NOT NULL DEFAULT nextval('tb_lote_ticket_seq'::regclass),
  id_lote integer,
  nr_quantidade numeric(12,2) DEFAULT 0,
  CONSTRAINT pktb_lote_ticket PRIMARY KEY (id_lote_ticket),
  CONSTRAINT fk_tb_lote_ticket_tb_lote FOREIGN KEY (id_lote)
      REFERENCES tb_lote (id_lote) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
);

CREATE TABLE tb_processo
(
  id_processo integer NOT NULL DEFAULT nextval('tb_processo_seq'::regclass),
  id_lote_ticket integer,
  id_setor integer,
  dt_inicio timestamp without time zone,
  CONSTRAINT pktb_processo PRIMARY KEY (id_processo),
  CONSTRAINT fk_tb_processo_tb_lote_ticket FOREIGN KEY (id_lote_ticket)
      REFERENCES tb_lote_ticket (id_lote_ticket) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_tb_processo_tb_setor FOREIGN KEY (id_setor)
      REFERENCES tb_setor (id_setor) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

And the relationship is, Each batch has N ticket and each ticket has 1 process.

- Lote    1..N   Ticket
- Ticket  1..1   Processo

I would like to know how to perform an efficient SQL to know the dt_inicio of each lot in each Setor and at the same time to "dt_final".
To dt_final would be the dt_inicio of the last process.

Sql Atual

SELECT 
    Lote.id_lote as "Lote.id_lote", 
    Setor.ds_setor as "Setor.ds_setor", 
    Setor.id_setor as "Setor.id_setor",
    (
        SELECT
            ProcesseTmp.dt_inicio
        FROM
            tb_processo as ProcesseTmp  
            LEFT JOIN   tb_setor as SetorTmp ON SetorTmp.id_setor = ProcesseTmp.id_setor   
            LEFT JOIN   tb_lote_ticket as LoteTicketTmp ON LoteTicketTmp.id_lote_ticket = ProcesseTmp.id_lote_ticket   
            LEFT JOIN   tb_lote as LoteTmp ON LoteTmp.id_lote = LoteOrdemTmp.id_lote  
        WHERE
            LoteTmp.id_lote = Lote.id_lote AND 
            SetorTmp.id_setor = Setor.id_setor
        ORDER BY 
            ProcesseTmp.dt_inicio
        LIMIT 1
    ) as "Processe.dt_inicio",
    (
        SELECT
            ProcesseTmp.dt_inicio
        FROM
            tb_processo as ProcesseTmp  
            LEFT JOIN   tb_setor as SetorTmp ON SetorTmp.id_setor = ProcesseTmp.id_setor   
            LEFT JOIN   tb_lote_ticket as LoteTicketTmp ON LoteTicketTmp.id_lote_ticket = ProcesseTmp.id_lote_ticket   
            LEFT JOIN   tb_lote as LoteTmp ON LoteTmp.id_lote = LoteOrdemTmp.id_lote  
        WHERE
            LoteTmp.id_lote = Lote.id_lote AND 
            SetorTmp.id_setor = Setor.id_setor
        ORDER BY 
            ProcesseTmp.dt_inicio DESC
        LIMIT 1
    ) as "Processe.dt_final",
FROM 
    tb_mapa_producao as Processe  
    LEFT JOIN tb_setor as Setor ON Setor.id_setor = Processe.id_setor   
    LEFT JOIN tb_lote_producao_ordem_ticket as LoteTicket ON LoteTicket.id_lote_ticket = Processe.id_lote_ticket   
    LEFT JOIN tb_lote_producao as Lote ON Lote.id_lote = Lote.id_lote  
WHERE 
    Processe.dt_inicio  BETWEEN '09/09/2015 00:00:00' AND '14/10/2015 23:59:59'
    and 1=1
    and ( Processe.fl_desativado is false OR Processe.fl_desativado is null )
GROUP BY 
    Lote.id_lote, 
    Setor.ds_setor, 
    Setor.id_setor, 
ORDER BY 
    Lote.id_lote, 
    Setor.ds_setor, 
    Setor.id_setor,

Test data

tb_lote
    id_lote     |   ds_lote
    1           |   teste1
    2           |   teste2

tb_lote_ticket
    id_lote_ticket  |   id_lote     |   nr_quantidade
    1               |   1           |   10
    2               |   1           |   10
    3               |   1           |   10
    4               |   1           |   10
    5               |   1           |   10
    6               |   1           |   10
    7               |   2           |   10
    8               |   2           |   10
    9               |   2           |   10
    10              |   2           |   10

tb_processo
    id_processo     |   id_lote_ticket  |   id_setor    |   dt_inicio
    1               |   1               |   3           |   2015-10-01 15:00:00
    2               |   2               |   3           |   2015-10-01 15:12:00
    3               |   3               |   3           |   2015-10-01 15:24:00
    4               |   4               |   4           |   2015-10-01 15:36:00
    5               |   5               |   4           |   2015-10-01 15:48:00
    6               |   6               |   4           |   2015-10-01 16:00:00
    7               |   7               |   6           |   2015-10-01 15:12:00
    8               |   8               |   6           |   2015-10-01 15:24:00
    9               |   9               |   6           |   2015-10-01 15:36:00
    10              |   10              |   6           |   2015-10-01 15:48:00

Expected Result

id_lote     |   id_setor    |   dt_inicio               |   dt_final
1           |   3           |   2015-10-01 15:00:00     |   2015-10-01 15:24:00
1           |   4           |   2015-10-01 15:36:00     |   2015-10-01 16:00:00
2           |   6           |   2015-10-01 15:12:00     |   2015-10-01 15:48:00

That is to say: of each batch, get the start and end date of each sector, and the start date corresponds to the first process of the sector and the end date corresponds to the last process of the sector.

sql postgresql select

  • It’s hard to understand the problem. It will help to get good answers from you showing examples of data in all tables and the expected result according to the sample data. Another point is that perhaps a simpler query is more inefficient than your original one in your actual scenario. It will be excessively laborious, boring and possibly ungrateful to consider this aspect in the answers. If you don’t even know how inefficient your query is because efficiency is not a problem - I would take that aspect out of the question.

  • We can say that of each batch, you want the start date and end date of each sector, and the start date corresponds to the first sector process and the end date corresponds to the last sector process?

  • @Caffé Exactly this.

  • @Guilhermelautert for us to help you, I recommend putting the creation of tables and data on this site http://sqlfiddle.com/, and pass the url to us. We can help you, because in your example above the table tb_mapa_producao and tb_setor.

1 answer

11


There you go:

select 
    lote_ticket.id_lote, processo.id_setor, 
    min(processo.dt_inicio) as dt_inicio, max(processo.dt_inicio) as dt_final
from
    tb_processo as processo join tb_lote_ticket as lote_ticket 
        on lote_ticket.id_lote_ticket = processo.id_lote_ticket
group by
    lote_ticket.id_lote, processo.id_setor
order by 
    lote_ticket.id_lote, processo.id_setor

See function on SQL Fiddle.

Explanation

You have made two Queries that serve only to search for the start date and the end date.

Your first subquery brings the start date by taking the first record. The second subquery is almost identical and brings the end date by taking the first record of the same query only ordered inversely.

Now, what you did was reproduce the aggregation functions Min and Max.

My query became simpler because I used these internal functions of the database instead of playing its behavior with Subqueries.

Browser other questions tagged sql postgresql select

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