Get the previous record of a date

Question

Get the previous record of a date

Navigation

#1 by (1 votes)

3

I need to make an appointment, where I need to get the penultimate record, that is, the previous record and the date as well.

SELECT DISTINCT
prt_partner.razao_social,
prt_partner.end_cidade,
prt_partner.end_estado,
prt_partner_status.nome,
 (SELECT MAX(_data_registro) FROM prt_partner_historic_status
      WHERE prt_partner_historic_status.id_status = prt_partner.id_status) AS ultima_data,

(SELECT MAX(_data_registro) FROM prt_partner_historic_status 
    WHERE _data_registro NOT IN (SELECT MAX(_data_registro) FROM prt_partner_historic_status 
    WHERE prt_partner_historic_status.id_status = prt_partner.id_status)) AS data_anterior


from prt_partner
JOIN prt_partner_status ON (prt_partner.id_status = prt_partner_status.id_status)


WHERE prt_partner._ativo = 1;

I've been trying to do the select of the above form, but the "previous_date" returns me wrong

EDITINGTHEPOST

SELECTDISTINCTprt_partner.id_partnerasid_parceiro,prt_partner.razao_socialasnome_empresa,prt_partner.end_cidadeascidade,prt_partner.end_estadoasestado,prt_partner_status.nomeasstatus_atual,prt_partner_historic_status.dias_entre_statusasdias_entre_status,(SELECTMAX(_data_registro)FROMprt_partner_historic_statusWHEREprt_partner_historic_status.id_status=prt_partner.id_status)ASultima_data,(SELECT_data_registroFROMprt_partner_historic_statusWHEREprt_partner_historic_status.id_status=prt_partner.id_statusORDERBYid_historic_statusDESCLIMIT1OFFSET1)ASdata_anteriorFROMprt_partnerJOINprt_partner_statusON(prt_partner.id_status=prt_partner_status.id_status)JOINprt_partner_historic_statusON(prt_partner.id_partner=prt_partner_historic_status.id_partner)WHEREprt_partner._ativo=1ORDERBYid_parceiro;

Selectingthetopgivesmethefollowingreturn

mysql sql

asked by anonymous 29.03.2017 / 19:17

1 answer

Java Text Streams accessing class data in a global scope with typescript

score 1 · Answer 1

Solution for SQL Server

I used WITH to create a table of support with a new column generated by ROW_NUMBER . In the search, I searched for the record immediately preceding the maximum of existing records in the auxiliary table.

See if you can see the following code:

WITH TBL_DATAANTERIOR (_data_registro, numeracao)
AS
(
    SELECT _data_registro, ROW_NUMBER() OVER (ORDER BY _data_registro) numeracao FROM prt_partner_historic_status 
    WHERE _data_registro NOT IN 
            (SELECT MAX(_data_registro) FROM prt_partner_historic_status 
             WHERE prt_partner_historic_status.id_status = prt_partner.id_status)
)
SELECT DISTINCT
prt_partner.razao_social,
prt_partner.end_cidade,
prt_partner.end_estado,
prt_partner_status.nome,
 (SELECT MAX(_data_registro) FROM prt_partner_historic_status
      WHERE prt_partner_historic_status.id_status = prt_partner.id_status) AS ultima_data,

(SELECT _data_registro FROM TBL_DATAANTERIOR
 WHERE numeracao = (select MAX(numeracao)-1 from TBL_DATAANTERIOR) AS data_anterior

from prt_partner
JOIN prt_partner_status ON (prt_partner.id_status = prt_partner_status.id_status)
WHERE prt_partner._ativo = 1;

Solution for MySQL

Based on the question update

SELECT DISTINCT
    prt_partner.id_partner as id_parceiro,
    prt_partner.razao_social as nome_empresa,
    prt_partner.end_cidade as cidade,
    prt_partner.end_estado as estado,
    prt_partner_status.nome as status_atual,
    prt_partner_historic_status.dias_entre_status as dias_entre_status,

    (SELECT MAX(_data_registro) FROM prt_partner_historic_status
     WHERE prt_partner_historic_status.id_status = prt_partner.id_status) AS ultima_data,

    (SELECT _data_registro FROM
     (SELECT _data_registro, id_historic_status FROM prt_partner_historic_status 
      WHERE prt_partner_historic_status.id_status = prt_partner.id_status
      ORDER BY id_historic_status DESC LIMIT 2) TEMP
     ORDER BY id_historic_status ASC LIMIT 1) AS data_anterior

FROM prt_partner
JOIN prt_partner_status ON (prt_partner.id_status = prt_partner_status.id_status)
JOIN prt_partner_historic_status ON (prt_partner.id_partner = prt_partner_historic_status.id_partner)
WHERE prt_partner._ativo = 1 ORDER BY id_parceiro;