In my app I have a table like this:
id user_id start end comment 265 2 29-04-2017 05-05-2017 Vacaciones 356 2 07-04-2017 10-04-2017 Baja Enfermedad 455 15 24-04-2017 24-08-2017 Maternidad
These are incidents that workers have that do not allow them to work. To generate a part of absences, I need to remove the inactivities of the MONTH and YEAR that they indicate to me.
To simplify, I have set an example of the months of May, June, July and August.
- if you indicate month 3, there are no incidents
- if they indicate month 4, I have three incidents, one from user 15 and others from user 2
- If you indicate month 5, I have two incidents (265 and 455, 365 is only in month 4).
- If you tell me the month 6, 7 or 8, you should leave only issue 15.
Currently, what I do is:
- I take the value of year and month,
- I calculate the days of that month.
- I get $ start and $ end (with $ start = 1 / month / year and $ end = days_month / month / year)
With that, I execute the following query:
Select * from Inactivity i where i.start > = $ home AND i.end < = $ end
With this query I only get those that are within the month, but not those that started in the previous month and continue in the month, or those that start in the month and continue in the next month. Okay. I have to change the query.
I tried changing the query to
select * from Inactivity i where (i.start >=$inicio AND i.end <= $fin) OR ( ($inicio between i.start and i.end) AND ($fin between i.start and i.end) )
That is, those inactivities that are in the range of dates, or those that the start date is between start and end of the incident and the end date between start and end of incidence. But the results are the same.
I do not see how to make this query. Does anyone have any ideas?