Можно ли обойтись одним запросом?

Question

[Альберт Табак]

Имееться таблица отпусков работников. В таблице отпусков храниться дата начала отпуска и дата окончания.
Поля таблицы: ид_отпуска, начало_отпуска (DATE), конец_отпуска (DATE), имя_сотрудника

Скажем
Ваня идёт с 7 по 10 число
Ира идёт с 8 по 12 число

Нужно получить статстику типа этой:

6 число - 0 сотрудников в отпуске
7 число - 1 сотрудник в отпуске
8 число - 2 сотрудника в отпуске
9 число - 2 сотрудника в отпуске
10 число - 2 сотрудника в отпуске
11 число - 1 сотрудника в отпуске
12 число - 1 сотрудника в отпуске
13 число - 0 сотрудников в отпуске

Не хотелось бы делать на каждый день отдельный запрос. Думаю можно извартиться както и получить то что нужно, но я пока не придумал как.
Буду благодарен за любые идеи ?

Answer 1

[Андрей Мохов]

нужна таблица с датами (каждая строка таблицы - новый день), если диапазон дат известен, то можно собрать путем SELECT .. UNION SELECT ... и т.д.

вот запрос, который скажет вам в какой день сколько сотрудников отсутствует:

SELECT c.date_time, COUNT(*) as cnt
FROM calendar c -- таблица о которой я написал выше
LEFT JOIN leave_people p ON c.date_time BETWEEN p.start_date AND IFNULL(p.end_date, c.date_time)
GROUP BY c.date_time

здесь IFNULL использовал для тех целей, когда окончание неизвестно, например человек на больничном (для случая, когда в таблице leave_people хранятся не только отпуска, но и больничные и т.д.)

Comments

[Альберт Табак]

Вариант, но хотелось бы оставить таблицу как есть :) в любом случае спасибо

[Андрей Мохов]

bighoc: вы какую таблицу имеете ввиду?

[Альберт Табак]

Андрей Мохов: мою, существующую

[Андрей Мохов]

bighoc: так и не меняйте, я же вас к этому не призываю... вы структуры не сообщили, потому только гадать приходится...
потребуется только новая таблица, в моем примере - calendar, где каждая строка - новая дата, либо заменить можно динамически сформировав запрос:

SELECT DATE('2015-04-01') as leave_date
UNION SELECT DATE('2015-04-02') as leave_date
UNION SELECT DATE('2015-04-03') as leave_date
UNION SELECT DATE('2015-04-04') as leave_date
UNION SELECT DATE('2015-04-05') as leave_date
UNION SELECT DATE('2015-04-06') as leave_date
UNION SELECT DATE('2015-04-07') as leave_date

[velnick]

bighoc: Андрей Мохов вам дело говорит. Вам нужна таблица с датами, чтобы иметь "срез" по которому агрегировать ваши данные. Кстати скрипты для таблицы-календаря легко найти, например: www.codeproject.com/Articles/647950/Create-and-Pop... . Если вам такая сложная таблица не нужна, то можно взять только те столбцы и период дат, который интересует.

Answer 2

[Дмитрий Энтелис]

Мне кажется наиболее рациональное решение - выбрать все данные из Вашей таблицы за нужный период, и за 1 проход цикла в любом ЯП составить требуемую табличку.

Answer 3

[Evgeniy Zavyalov]

табличка с данными

CREATE TABLE `t_otp` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`dt_start` DATE NULL DEFAULT NULL,
	`dt_end` DATE NULL DEFAULT NULL,
	`fio` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

INSERT INTO `t_otp` (`id`, `dt_start`, `dt_end`, `fio`) VALUES
	(1, '2015-03-07', '2015-03-10', 'Ваня'),
	(2, '2015-03-08', '2015-03-12', 'Ира');

запрос

select concat(tab.date,' число - ', 
       (select count(1) from t_otp where tab.date between dt_start and dt_end)
    , ' сотрудника в отпуске ') abb
  from (
SELECT '2015-03-06' + INTERVAL(CAST(CONCAT(CAST(a.num AS CHAR), CAST(b.num AS CHAR)) AS UNSIGNED)) DAY `date`
 FROM
(SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a,
(SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
WHERE '2015-03-06' + INTERVAL(CAST(CONCAT(CAST(a.num AS CHAR), CAST(b.num AS CHAR)) AS UNSIGNED)) DAY <= '2015-03-13'
ORDER BY 1
) tab

результат

2015-03-06 число - 0 сотрудника в отпуске
2015-03-07 число - 1 сотрудника в отпуске
2015-03-08 число - 2 сотрудника в отпуске
2015-03-09 число - 2 сотрудника в отпуске
2015-03-10 число - 2 сотрудника в отпуске
2015-03-11 число - 1 сотрудника в отпуске
2015-03-12 число - 1 сотрудника в отпуске
2015-03-13 число - 0 сотрудника в отпуске

оптимизируйте дальше сами...

Answer 4

[Оптимус Пьян]

Если в БД хранится именно так в 1 или 2 полях "Ваня идёт с 7 по 10 число" т.е. интервалом то нельзя. То это какая-то странная организация БД. Если там хотя бы 2 числа в двух полях + 1 на имя, например Ваня 7 и 10 то можно запросить все сотрудников где число 1 больше 8 и число 2 меньше 10 получим всех кто в отпуске 9го. О месяце тут ещё у вас нет информации. А если отпуск рваный двумя интервалами?

Comments

[Альберт Табак]

В бд конечно же поля "начало отпуска", "конец отпуска", "имя сотрудника"

[Оптимус Пьян]

bighoc: тогда BETWEN, OR

Answer 5

[Centrino]

Можно обойтись 1 запросом (если данные в колонки Базы хранятся именно та как вы написали), но вам нужно будет перебрать все комбинации и объединить их с помощью OR, делать обрезание строки на кол-во символов после нахождения с и по, присваивать этим значениям аргументы и по этим аргументам делать Group By. Но это ЖОПА, легче сделать нормальную базу!!

Comments

[Альберт Табак]

как по вашему должна выглядеть номральная база ?

[Centrino]

d, name, firstname startVacation, endVacation (int, varchar, date, date) Хотя бы. И будет
1 Ваня Демидов 2015-04-01 2015-04-10
2 Вася Пупкин 2015-04-21 2015-05-01
вы будете знать в каком месяце и какого числа и какого года кто в отпуке, а то будет что 1 числа ( а єто 1 января, 1 декабря, 1 июля) 3 сотрудника в отпуске., если брать только ваши данные "Ваня идёт с 7 по 10 число"

[Альберт Табак]

Centrino: Вы меня не правильно понял, именна такая у меня таблица и есть, но как из неё вытянуть информацию так как я хочу ? По чему делать груп бай ?