Zapytania informacyjne

SET @WORKER = 2; -- ID pracownika
-- przedział czasowy podsumowania  [ START_DATE, END_DATE )
SET @START_DATE = '2000-06-01';
SET @END_DATE = '2000-06-06';
SELECT
  `name`,
  `surname`,
  YEAR(`date_start`) AS year,
  MONTH(`date_start`) AS month,
  WEEK(`date_start`) AS week,
  DAY(`date_start`) AS day,
  SEC_TO_TIME( SUM( TIME_TO_SEC( timediff( date_add(`date_stop`, INTERVAL `time_stop` hour_second), date_add(`date_start`, INTERVAL `time_start` hour_second) ) ) )) AS work_time
FROM `user_has_operation` LEFT JOIN `users` ON `user_has_operation`.`user_id` = `users`.`id`
WHERE `user_id` = @WORKER AND `date_start` >= @START_DATE AND `date_stop` < @END_DATE
GROUP BY DAY(`date_start`); -- podsumowanie dzienne

SET @START_DATE = '2000-01-01';
SET @END_DATE = '2010-01-01';
SET @WORKER = 2;
 
SELECT
  `operations`.`name`,
  count(*) AS operation_count
FROM `user_has_operation` JOIN operations ON user_has_operation.operation_id = operations.id
WHERE `date_start` >= @START_DATE AND `date_stop` < @END_DATE AND user_id = @WORKER
GROUP BY operation_id
ORDER BY operation_count DESC -- Najczęściej wykonywane operacje
-- ORDER BY operation_count -- Najrzadziej wykonywane operacje
LIMIT 10