Query between Interval Dates

Question

I have the following table.

I am trying to get the records that intersects with any date, for example If I need the percent between 2002-12-29 and 2002-12-30 I should to get the first record with percent 28.60, I write a query but sometimes I get somes records, but sometimes I don't get anything. Any help would be great.

Here is the query

SELECT *
FROM myTable
WHERE ((fec_inicio < '2002-12-29'
AND fec_fin        > '2002-12-29')
OR (fec_inicio     < '2002-12-30'
AND fec_fin        > '2002-12-30'))

Answer 1

It sounds like you're looking for the SQL overlaps operator, which I think MySQL doesn't support. The overlaps operator is defined as

row_constructor overlaps  row_constructor

and the row constructors have the form

(Ls, Le) OVERLAPS (Rs, Re)

where Ls means left-hand start, Le means left-hand end, etc.

The overlaps operator is defined to be semantically equivalent to this expression.

(Ls > Rs and (Ls < Re or Le < Re) ) or
(Rs > Ls and (Rs < Le or Re < Le) ) or
(Ls = Rs and Le is not null and Re is not null)

Substituting your values gives me something like this. (Not tested)

select *
from myTable
where ( '2002-12-29' > fec_inicio and ('2002-12-29' < fec_fin or '2002-12-30' < fec_fin) ) or
      ( fec_inicio > '2002-12-29' and (fec_inicio < '2002-12-30' or fec_fin < '2002-12-30') ) or
      ( '2002-12-29' = fec_inicio and '2002-12-30' is not null and fec_fin is not null);

I hope I got those substitutions right. (And we know that hope doesn't scale well.)

It's not clear from your question what you need to do with ranges that overlap multiple rows. But, for example, it should be simple enough to select the minimum date from the query above, and inner join on that date to get the full row.

Having said all that, your logic in selecting percentages seems odd. Give it some thought.