Bring the first row based on a date in SQL

Question

Bring the first row based on a date in SQL

Navigation

#1 by (3 votes)

1

Another challenge stopped in my hands. I need help with my query.

What I need:

I want to know who the people who had account postings on a given day, where I need to display the value and status of that day.

So, I have two tables, one that I have the tie of the person and another that I have the values and the situation.

Table and data

This table is a history that stores information according to modifications, does not necessarily occur every day.

TableHistory

PessoaId | Situacao | DataInsert | Valor
  999    |   Ativo  | 2018-01-10 | 1111
  999    |   Ativo  | 2018-01-12 | 2222
  999    |   Ativo  | 2018-01-13 | 3333
  999    |   Inativo| 2018-01-18 | 4444
  999    |   Ativo  | 2018-01-20 | 5555
  999    |   Inativo| 2018-01-22 | 5555
  999    |   Ativo  | 2018-01-25 | 6666

Launching Table

PessoaId | LacamentoId | DataLancamento | Descrocao
   999   |    0001     |  2018-01-17    | Lacamento 1
   999   |    0002     |  2018-01-25    | Lacamento 2
   999   |    0003     |  2018-01-24    | Em divida com o sistema, liberação negada
   999   |    0002     |  2018-01-25    | Lacamento 2 estornado

General comments: The table HistPessoa maintains a hostórico every time that some valor or situação is changed. Through the release date I need to inform the situation and value of the person on the day of release.

Example : On January 17, 2018 person 999 was in active status with 3333 value. And on January 25, 2018, the person 999 was in a Active situation with a value of 6666

At launch 0001 the DataLancamento (2018-01-17) does not exist in the HistPessoa table, in that case I should get the DataInsert previous of the DataLacamento closest to the date 2018-01-13

HistPessoa has stored long history, it is a very large table. It would be interesting to have an optimization.

Expected result

PessoaId | LacamentoId | DataLacamento | Situacao | Valor
   999   |   0001      |  2018-01-17   |  Ativo   | 3333
   999   |   0002      |  2018-01-25   |  Ativo   | 6666

What I've done so far

SELECT TOP 100 PERCENT
         ROW_NUMBER() OVER (PARTITION BY PessoaId ORDER BY DataInsert DESC) AS NUM,
         PessoaId,
         valor,
         Situacao
         FROM HistPessoa
         ORDER BY DataInsert DESC

This query sorts in descending order, but does not filter by date, so get the last date inserted in the HistPessoa table.

Extra I've built something more complete based on my real solution. Where do I go from Lancamento in search of other data. Follow the link in SQL Fiddle more complete. HERE

Any tips? Or possible solution?

Maybe with CTE or Gaps And Island?

sql sql-server

asked by anonymous 28.02.2018 / 19:46

1 answer

MySQL Query with recursion in N levels Lock the mouse scroll in JavaScript

score 3 · Accepted Answer

CREATE TABLE HISTPESSOA(
 PessoaId int,
 Situacao varchar(10),
 DataInsert date,
 Valor int
);

INSERT INTO HISTPESSOA(PessoaId,Situacao,DataInsert,Valor) values
(999,'Ativo','2018-01-10',1111),
(999,'Ativo','2018-01-12',2222),
(999,'Ativo','2018-01-13',3333),
(999,'Inativo','2018-01-18',4444),
(999,'Ativo','2018-01-20',5555),
(999,'Inativo','2018-01-22',5555),
(999,'Ativo','2018-01-25',6666);


CREATE TABLE LANCAMENTO(
 PessoaId int,
 LacamentoId int,
 DataLancamento date
);
INSERT INTO LANCAMENTO(PessoaId,LacamentoId,DataLancamento) values
(999,0001,'2018-01-17'),
(999,0002,'2018-01-25');

Query:

SELECT l.*
    FROM
      (SELECT HP.PessoaId,
              L.LacamentoId,
              L.DataLancamento,
              HP.Valor,
              ROW_NUMBER() OVER (PARTITION BY L.LacamentoId
                                 ORDER BY DataInsert DESC) AS linha
       FROM HISTPESSOA HP
       INNER JOIN LANCAMENTO L ON HP.PessoaId = L.PessoaId
       WHERE HP.DataInsert <= L.DataLancamento) l
    WHERE l.linha = 1;

Result:

Explanation:

UseROW_NUMBERtoenumerateyourlines
TargetbycolumnLacamentoId.
SortstheDataInsertcolumndown.
Takethefirstlineasitwillbethepreviouslineyouwant!

Iarrivedthereisthisresultbasedonthisotheranswerofmine:aa"> Select first record within a segmentation in SQL Server