Bring the first line based on an SQL date

Question

One more challenge stopped in my hands. I need help in my query.

What I need:

I want to know who are the people who have had account releases on a given day, where I need to show the value and situation of that day.

Being so, I have two tables, one that has the lasso of the person and the other that I have the values and the situation.

Table and data

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

Histpessoa Table

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

Table Lancamento

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 remarks: The Histpessoa table keeps a hostile all the time valor or situação is changed. Through the release date I need to inform the situation and the person’s value on the day of launch.

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

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

HistPessoa has stored long-standing 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 it picks the last date inserted in the table HistPessoa.

Extra I’ve assembled something more complete based on my real solution. Where I leave by Lancamento in search of the other data. Follow the link in the most complete SQL Fiddle. HERE

Any tips? Or possible solution?

Maybe with CTE or Gaps And Island?

Answer 1

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;

Upshot:

Explanation:

• Use ROW_NUMBER to list your lines
• Segment through the column LacamentoId.
• Order downward the column DataInsert.
• Take the first line as it will be the previous line you want!

I arrived there this result using as a basis this other answer my: Select first record within a segmentation in SQL Server