SELECT on a date in the range of ONE entered date and the date of the next record

Question

SELECT on a date in the range of ONE entered date and the date of the next record

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

How to generate a select that brings the date between an informed date and the date of the next record? In the Cargos table I only have a date, not a data_inicio and a data_fim , and I need to know the date between the entered date and the next record date.

For example, if I enter the date 01/10/2015 :

From:

|NOME     |CARGO                   |DATA_CARGO         |
|FULANO   |AUXILIAR ADMINISTRATIVO |29/05/2006 00:00:00|
|FULANO   |SUPERVISOR              |01/07/2013 00:00:00|
|FULANO   |SUPERVISOR FINANCEIRO   |01/09/2015 00:00:00|
|FULANO   |ADMINISTRADOR GERAL     |01/02/2016 00:00:00|

I would just like to report:

|NOME     |CARGO                   |DATA_CARGO         |
|FULANO   |SUPERVISOR FINANCEIRO   |01/09/2015 00:00:00|

If I put the code where data >= data_informada as you can see, it does not have the desired record.

ps: The database is Firebird.

sql select firebird firebird-2.5

asked by anonymous 31.05.2017 / 15:13

2 answers

Update table after payment completed PHP How do the date () function format a date in Portuguese?

score 0 · Answer 1

Although a bit confusing, the "date between the date entered and the date of the next record," I believe you really do need to know the 'So-and-so' end date for a given post. Anything, improve the issue to help you better.

I use a subselect that has the shortest date that is greater than the current record date, for the same name. See if you can help:

Select 
Nome, 
Cargo, 
Data_Cargo,
(Select 
 min(x.Data_Cargo) 
 from Cargos x 
 where x.nome=c.nome 
 and x.data > c.data ) as Data_Saida
from Cargos c

score 0 · Answer 2

I was able to find an answer to the question by making two subselects .

select e.nome,
       (select first 1 c2.nome 
               from cargos c2
               where c2.cod_empregado = e.cod_empregado
               and c2.data = (select first 1 c3.data
                                            from cargos c3
                                            where c3.cod_empregado = e.cod_empregado)
                                            and c3.data <= :data
                                            order by c3.data desc)) as cargo
from empregados e
inner join cargos c on (c.cod_empregado = e.cod_empregado)
...

Thanks to everyone who tried to help, I'm sorry I could not ask better. Att.