Как удалить записи начиная с определенной даты до даты, где тип данных иной?

Question

[Fyodor]

У меня таблица mysql id(int), date_work(datetime), date_type(int), manager_id(int) - сюда заносятся данные о рабочих днях и типах этого дня(рабочий ли день или отпуск или праздник).

Получается если отпуск 7 дней подряд - то будет 7 записей с датой следующей друг за другом. 8 дней(т.е. следующий день после отпуска) будет рабочим и date_type у него будет другой, либо же последняя запись в таблице будет принадлежать последнему дню отпуска, т.к. дальше еще не назначали график.

Задача такая - допустим я хочу "удалить" отпуск из базы, но заранее не зная сколько записей он занимает.
Т.е. нужно что-то вроде - выбрать записи начиная с определенной даты и до той даты, где date_type(тип даты) будет другой. Не могу чего-то придумать как это сделать.

Можно конечно сделать предварительный запрос получив записи и пройдясь по ним циклом, определить "крайнее" число и после ясно будет что удалять, но хотелось бы понять можно ли это одним запросом решить.

Answer 1

[CHolfield]

delete from tablename 
where (date_work => startdate 
and date_work < (select top 1 date_work from tablename 
where date_work > startdate and date_type <> otpusktype order by date_work))

Замените startdate на дату первого дня в формате datetime, otpusktype на значение соответствующее отпуску. Прошу прощения не сразу нормальный запрос отослался, айпад выключился внезапно.

Comments

[Fyodor]

Хорошая идея. Но получается эта часть не отработает если после удаляемого "промежутка" больше нет никаких записей, т.к. после "отпуска" в базе должна быть любая другая дата записана, а этого может и не быть. Например отпуск с 5.01 по 9.01 и после в базе записей нет.

select top 1 date_work from tablename 
where date_work > startdate and date_type <> otpusktype order by date_work

[CHolfield]

вы выдвигаете дополнительные условия. ОК. Замените в коде ниже datetime_max_value на 99999999, я хз какое значение в мускуле для даты максимальное.

delete from tablename 
where (date_work => startdate 
and date_work < (select top 1 IFNULL(date_work, datetime_max_value) as date_work from tablename 
where date_work > startdate and date_type <> otpusktype order by date_work))

[Fyodor]

CHolfield, почему, вот об этом писал в вопросе

либо же последняя запись в таблице будет принадлежать последнему дню отпуска, т.к. дальше еще не назначали график.

Так тоже не пойдет...решил переделать и к первой дате начала отпуска добавлять переменную количества дней при добавлении записи в таблицу. так и вычислять сколько строк удалить

[CHolfield]

Федор, обоснуйте, почему "так тоже не пойдет"?

[Fyodor]

CHolfield, как уже писал, в базе не весь календарь хранится сразу, таким образом возможна ситуация, что "отпуск" с 5 по 9 января и потом с 20 по 25 января. между этими событиями записей не будет, таким образом по условию > 5 января и date_type != 7(отпуск) вполне подходит под условие выборка до 25 января включительно, а это по сути разные отдельные события. Я уже переделал с занесением в базу сразу колчиества дней, так даже удобнее стало. Т.е. добавляется в базу "отпуск" на 5 января и записывается 5 дней. дальше все гораздо проще, так как можно точно посчитать дату окончания события.

[CHolfield]

Опять дополнительные условия)) ну ок

delete from
  tablename
where
  (
    date_work >= startdate
    and date_work < (
      select
        IFNULL(date_work, 999999) as tmpdw
      from
        tablename
      where
        date_work > startdate
        and date_type <> otpusktype
        and ROUND(
          DATEDIFF(
            tmpdw,
            (
              select
                date_work
              from
                tablename
              where
                date_work < tmpdw
              order by
                date_work DESC
              LIMIT
                1
            )
          )
        ) > 1
      order by
        date_work
      LIMIT
        1
    )
  )

[Fyodor]

CHolfield, так выдает ошибку Unknown column 'tmpdw' in 'where clause' плюс я думаю mysql будет ругаться на Table is specified twice, both as a target for 'DELETE' and as a separate source for data, это тоже можно будет победить, но слишком уж сложно выходит. Решил что гораздо проще сделать следующим образом - добавить колонку date_start - где хранить дату начала события. т.е. у отпуска с 5 по 9 января 5 записей будет в базе с датами с 5 по 9 и у всех date_start будет 5 января. Таким образом можно удалить весь отрезок безошибочно просто зная дату начала. Кстати, а зачем тут ROUND для DATEDIFF - оно разве не целое количество дней возвращает?

Спасибо за помощь в любом случае, но пришлось пойти другим путем.

[CHolfield]

Федор, да, запрос нормализовать нужно, проверял только синтаксис. Обращайтесь.