Good day.

Thanks in advance to everyone who will be able to help :) I have been working on a task for 2 weeks already. I would be grateful for any thought :) There is a table with records of the form

day id_work count 20130326 1 2 20130327 1 4 20130328 1 2 20130329 1 0 20130330 1 2 20130331 1 3 20130401 1 2 20130402 1 2 20130403 1 1

There are a lot of such records. Example task: you need to find options for 4-day ranges in which count is above 0, i.e. You need a query that would output from the data in this table:

  day1 day2 id_work 20130330 20130402 1 20130331 20130403 1

And with a 3-day range, these options are:

  day1 day2 id_work 20130326 20130328 1 20130330 20130401 1 20130331 20130402 1 20130401 20130403 1

The principle of the request. Say need 3-day ranges.

Takes the first record 20130326, looks - count is greater than 0, this is 1 day of the range. Then it takes the next entry 20130327, it looks - count is greater than 0. This is the 2 day range. Then it takes the next entry 20130328, it looks - count is greater than 0. This is the 3 day range. 3 days to eat, then output

 20130326 20130327 1

Then we begin with the entry 20130327. It takes the first entry 20130327, it looks - count is greater than 0, this is 1 day of the range. Then it takes the next entry 20130328, it looks - count is greater than 0. This is the 2 day range. Next, it takes the next entry 20130329, it looks - aha, the count is 0. All this range no longer needs to be output.

Then we begin with the entry 20130328. It takes the first entry 20130328, it looks - count is greater than 0, this is 1 day of the range. Next, it takes the next entry 20130329, it looks - aha, the count is 0. This range does not need to be output.

And so on. Here without variables and nested queries in any way. Already the whole brain broke itself)) Is it possible to organize a request that would draw the necessary conclusions? Thank.

  • And the dates you have stored in exactly this format yyyymmdd? And what kind of field, if so? - Deonis
  • IamS, it would be so easy))) The principle of the request is specified. Deonis, yes this format, the type of the field is int. You can of course use the date type, but will it be useful when making a request? - nerik
  • one
    > Will it be useful when making a request? And what do you think? )) If there are dates of March 30 and April 2 , presented in the table as int : 20130330 and 20130402 , will it be convenient to calculate the range between these dates? - Deonis
  • Well, let's say the date data type)) - nerik
  • one
    @nerik, segment grouping is not so easy to do. If there is free time a bit later, I will try to help with such a request, but if I were you, I would do such things with PHP. - Deonis

1 answer 1

The day field must be a date of course. On MS SQL, the request will be as follows - for 4 day ranges:

 SELECT t1.day, MAX(t2.day), t1.id_work FROM table t1 JOIN table t2 ON t2.day < (t1.day+4) AND t2.day >= t1.day AND t2.count > 0 GROUP BY t1.day, t1.id_work HAVING COUNT(*) = 4 ORDER BY t1.day
  • Here it turns out 2 tables t1 and t2. But the table is one t1) Can you join the table itself? I'll try to implement the idea, the idea is not bad. If it works , thanks)) - nerik
  • @renegator; Yes, it seems to be true . Only in MySQL you need to make one small amendment: SELECT t1. day , MAX (t2. day ), t1. id_work FROM tbl t1 JOIN tbl t2 ON t2. day <(t1. day + INTERVAL 4 DAY) AND t2. day > = t1. day AND t2. count > 0 GROUP BY t1. day , t1. id_work HAVING COUNT (*) = 4 ORDER BY t1. day - Deonis
  • Thanks here express acceptance of the answer - renegator
  • I’m filling the base for now and I’ll try the option) Somehow I don’t want to be scattered about accepting an answer without checking them out. As soon as there are results, I will immediately write here and of course I will accept the answer. - nerik
  • The results were not very successful , but thanks for the direction) - nerik