query with ranges of dates and values of a month

Navigation
2

In my app I have a table like this:

INACTIVITY: 

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?

    
asked by Jakala 27.06.2017 в 13:55
source

3 answers

1

With making an OR between $ home and $ end, I'd have to use it. 

SELECT * FROM Inactivity i 
WHERE $inicio BETWEEN start AND end 
OR $fin BETWEEN start AND end

With this you bring the records if the 1 / month / year is between the start and end or the end / month / year is between the start and end

    
answered by 27.06.2017 в 14:11
0

One way to get the data by month and / or year is with date_format , here I leave a simple example, also prodías use between for the condition of the month. 

SELECT * FROM inactivity i
WHERE DATE_FORMAT(start,'%Y') = 2017
AND DATE_FORMAT(start,'%m') <= '04'
AND DATE_FORMAT(end,'%m') >= '04'
    
answered by 27.06.2017 в 14:04
0

Good morning,

in the end I have opted more for the solution of Lithorell. I had to change something that I do not like, but I can not complete.

There has been another case that in principle the consultation should take it without problems, but it does not. It is the following: 

id      user_id         start         end          comment
471      18           24-04-2017    1-05-2017     Vacaciones

That is, the case that the end date of the incident is the 1st day of the month we are looking for.

In the end, the query has stayed like this: 

SELECT * FROM Inactivity i 
WHERE (i.start BETWEEN $inicio AND $find) 
   OR (end BETWEEN $inicio AND $fin) 
   OR (start >= $inicio AND end <= $fin )
   OR (end = $diauno )

The last OR (end = $ diauno) refers to that 1 of the current month. I do not understand why, but if the end date is 1 / month / year, the query DOES NOT take this incident.

I know it's a bad girl, but as I say, if I do not put it on, I do not take the courage. I have reviewed the < and & gt ;, not for missing the case of

answered by 29.06.2017 в 08:23
How do I order an pipes in Ionic 2? Doubt with list of initializers in C ++