Блог

Чувствую, что это должно быть что-то простое, чего мне не хватает. У меня есть таблица с несколькими временными метками в течение дня и другая таблица с диапазонами дат. Я хочу выбирать из первой таблицы всякий раз, когда ее временная метка находится в любом из диапазонов дат в другой таблице, для определенного дня… У кого-нибудь есть какие-либо подсказки, как это может работать и возможно ли это вообще?

Редактировать

 SELECT count(*) FROM Completed /* Returns 59 */

SELECT count(u.user_id) AS Total FROM Completed c
LEFT JOIN users u ON c.user_id = u.user_id
LEFT JOIN logins l ON c.user_id = l.user_id
WHERE c.start BETWEEN l.signIn AND l.signOut
AND c.users_id = 1400
GROUP BY c.type, c.user_id
ORDER BY c.type, Total /* Returns 57 Total */
  

Итак, мои три таблицы:

• Пользователь. идентификатор пользователя int, полное имя varchar.
• Завершено. user_id int, введите varchar, начальная дата-время
• Логины. идентификатор пользователя int, дата входа в систему, дата выхода из системы

По сути, у меня есть несколько строк в полной таблице, и я хочу знать, сколько сделал пользователь, в сравнении с тем, сколько у них была возможность сделать. «Наличие возможности» — это просто вход в систему, поэтому я пытаюсь получить разбивку общего количества отработанных пользователей по сравнению с доступной для любой работы, которая была выполнена во время входа в систему. Дело в том, что у них будет несколько периодов входа в течение одного дня. Я думал, что был близок выше, но нет сигары.

Редактировать 2

Хорошо, вот как могут выглядеть таблицы. В приведенном ниже примере я хотел бы видеть, что у Джона была возможность поработать с 4 и он отработал 2 из них. Я бы не хотел считать complete_id 4, потому что он еще не был на работе. Мой идеальный результат выглядел бы примерно так:

 name | type | available | worked | sign in             | sign out
Jack | SUV  | 1         |      1 | 2011-04-08 12:30:00 | 2011-04-08 13:00:00 
Jack | SUV  | 3         |      0 | 2011-04-08 13:30:00 | 2011-04-08 14:00:00 // Had An Opportunity to work, but didn't work any
Jack | SUV  | 5         |      2 | 2011-04-08 14:30:00 | 2011-04-08 15:00:00  // Worked two 
  

таблица пользователей

 user_id | fullname
1400    | Jack
1401    | John
  

полная таблица

 complete_id | user_id | type | start
1           | 1400    | Car  | 2011-04-08 18:36:22
2           | 1400    | Boat | 2011-04-08 12:31:42
3           | 1401    | SUV  | 2011-04-08 11:14:12
4           | 1400    | Car  | 2011-04-08 1:56:52
5           | 1401    | Car  | 2011-04-08 16:11:23
  

таблица логинов

 login_id | user_id | signIn             | signOut 
1        | 1400    | 2011-04-08 1:00:00 | 2011-04-08 21:00:00 
1        | 1401    | 2011-04-08 11:00:00 | 2011-04-08 21:00:00 
  

 select a.*
from tbl a
where exists (
    select *
    from tblother b
    where b.startdate <= a.datecolumn
      and b.enddate   >= a.datecolumn)
  

Следующий SQL должен сработать для вас, чтобы получить искомые значения. Единственное, чего не хватает, это типа выполненной работы. Только наличие этого в заполненных таблицах немного усложняет ситуацию, но я уверен, что это выполнимо, если немного больше подумать.

Основная идея здесь заключается в том, что мы создаем полный набор данных значений и объединяем нужные нам показатели. Я часто использую этот метод при работе с диапазонами дат. Обычно это тоже довольно эффективно.

 SELECT Users.Name, Users.SignIn, Users.SignOut,
SUM(CASE WHEN Complete.Start BETWEEN Users.SignIn AND Users.SignOut THEN 1 Else 0) as Available,
SUM(CASE WHEN Complete.Start BETWEEN Users.SignIn AND Users.SignOut AND Users.User_Id = Complete.User_Id THEN 1 ELSE 0) as Worked
FROM Users inner join logins on users.user_id = logins.user_id, Complete
GROUP BY Users.Name, Users.SignIn, Users.SignOut
  

Возможно, вы захотите добавить оператор WHERE, чтобы ограничить диапазон дат (и количество строк) запроса, включая таблицы complete и logins.