Fetch last recorded date in sql cpf_cnpj

Asked

Viewed 77 times

-1

I have a basis that I need one of the columns to be the search for the lowest date recorded on that CPF/CNPJ in the base.

In the example would create the column data_anterior:

cpf_cnpj  | aplicado| data_aplic | data_anterior
-------------------------------------------------    
123456789 | sim     | 22/03/2018 | missing    
123456789   não     | missing    | 22/03/2018

When the date is the same as data_aplic the information must not appear.

Can you help me program this function in SQL?

sql sql-server
  • 2

    Could you explain what you need better? Questions: (1) which column you refer to in "I need one of the columns"? (2) on "the search for the shortest date recorded", in which column the earliest date? (3) on "the information must not appear", row should be deleted or column?

1 answer

0

I suggest you use the structure below:

SELECT
  cpf_cnpj,
  aplicado,
  data_aplic,
  IS_NULL((SELECT MIN(data_aplic)
   FROM tabela T2 
   WHERE T1.cpf_cnpj = T2.cpf_cnpj
     AND T1.data_aplicada <> T2.data_aplic), 'missing') AS data_anterior
FROM tabela T1

data_anterior will be filled through subconsultation, by checking the earliest date of cpf_cnpj consulted; in addition, the ISNULL is used to display the text you want (in case, "Missing").

Browser other questions tagged sql sql-server

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