Retrieve longer and shorter time-bound dates

Navigation
4

Within a table containing the data_inicio , data_fim , hora_inicio , hora_fim columns, you need to identify the following occurrences within a recordset: the lower start date, the higher end date, the start time lower and the end time greater.

The image below lists a set of records in which I want to check the smaller start_date, larger_del_fim, shorter start_time, longer_file:

Basedontherecordsshownintheimageabove,theshorteststartdateissimpletoachieve,simplybyusingtheMINoperator.Thesamegoesforgettingthehighestenddate.IjustneedtousetheMAXoperatortogetit.TocalculatethehighestinitialtimeandthelowestendtimeIcannotusethesameapproachascanbeseeninthesqlresultbelow.

SQL:

selectmin(data_inicio),max(data_fim),min(hora_inicio),max(hora_fim)fromminha_tabelawherecodigo=2

Result:

To calculate, both the smallest start time and the highest end time need to take more than just the column itself into account. Simply resorting to the MIN and MAX operator will not resolve it. I need to take into account also the date columns. Looking at the first image we can realize that the smallest start time is' 1000 'because the smallest date is '04 / 05'. The highest end time is' 1900 ', because the highest end date is '20 / 05'.

Is there a way to implement this type of query simply through SQL? If so, how?

This example is in the SQL Fiddle SQL Fiddle .

    
asked by anonymous 06.05.2015 / 22:30

3 answers

3

First you must concatenate the date and time columns to transform the string into date: 

to_date(data_inicio || ' ' || hora_inicio,'dd-mm-yy hh24mi'))

After that, just get the shortest and longest date: 

min(to_date(data_inicio || ' ' || hora_inicio,'dd-mm-yy hh24mi')),
max(to_date(data_fim || ' ' || hora_fim,'dd-mm-yy hh24mi'))

And to sort again in just date and / or time: 

to_char(min_inicio,'dd/mm/yyyy') menor_data,
to_char(min_inicio,'hh24:mi:ss') menor_hora,
to_char(max_fim,'dd/mm/yyyy') maior_data,
to_char(max_fim,'hh24:mi:ss') maior_hora

See example working in SQL Fiddle .

    
08.05.2015 / 00:33
1

If I understand the problem, only grouping by code can resolve: 

select min(data_inicio), max(data_fim), min(hora_inicio), max(hora_fim)
from minha_tabela
where codigo = 2
group by codigo
    
06.05.2015 / 23:30
1

Another, more verbose, alternative to this problem is the use of a temporary table. Initially we retrieved this table only with the information of minimum start date and maximum final date. 

with limites as (
   select min(data_inicio) data_minima, max(data_fim) data_maxima
   from minha_tabela
   where codigo = 2
)

Next we merge this table with the searched table to retrieve the minimum time and the maximum time. This is necessary because of the conditionality of the time fields to those of date as already described in the question. This join is necessary because we can have more than one date equal to both the minimum value and the maximum value. As we can see in the image below, we have three values for May 3 and two for May 30:

Fortheminimumstarttimewewouldhave:

(selectmin(hora_inicio)fromminha_tabelainnerjoinlimitesonlimites.data_minima=data_iniciowherecodigo=2)ashora_minima

Forthemaximumendtimewewouldhave:

(selectmax(hora_fim)fromminha_tabelainnerjoinlimitesonlimites.data_maxima=data_fimwherecodigo=2)ashora_maxima

SQLwiththe complete solution would look like this: 

with limites as (
    select min(data_inicio) data_minima, max(data_fim) data_maxima
    from minha_tabela
    where codigo = 2
)
select (
  select data_minima from limites
) as data_minima,
(
  select data_maxima from limites
) as data_maxima,
(
  select min(hora_inicio) from minha_tabela
  inner join limites on limites.data_minima = data_inicio
  where codigo = 2
) as hora_minima,
(
  select max(hora_fim) from minha_tabela
  inner join limites on limites.data_maxima = data_fim
  where codigo = 2
) as hora_maxima
from dual

Result:

    
08.05.2015 / 15:34
Change color of selected item - select option How to make an image rotate