Блог

У меня есть таблица db «записи» с журналами учета рабочего времени сотрудников.

 employee_id    |    hours    |    task_id    |    logged_at
---------------------------------------------------------------------
1              |    .5       |    1          |    1546342200 (jan 19)
2              |    .8       |    2          |    1547548200 (jan 19)
1              |    .2       |    3          |    1549022400 (feb 19)
1              |    .5       |    3          |    1549022400 (feb 19)
2              |    .9       |    1          |    1551625200 (mar 19)
  

Моя цель — получить отчет, в котором будут указаны оплачиваемые и неоплачиваемые часы за каждый месяц года. Оплачиваемый или неоплачиваемый определяется идентификатором задачи. У меня есть список идентификаторов задач, которые оплачиваются, и список, которые не оплачиваются.

Цель для конечного результата

 month    |    year    |    total   |    billable    |    nonbillable
-------------------------------------------------------------------
1        |    2019    |    1.3     |    .8          |    .5
2        |    2019    |    .7      |    .7          |    0
3        |    2019    |    .9      |    0           |    .9
  

Это основано на том, что задачи 2 и 3 оплачиваются, а задача 1 не оплачивается.

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

Я смог реализовать общую идею, но застрял на разделении оплачиваемых часов и часов, не подлежащих оплате, на основе task_id.

Это все, что я получил:

 SELECT 
  MONTH(FROM_UNIXTIME(logged_at)), 
  YEAR(FROM_UNIXTIME(logged_at)), 
  SUM(hours)
FROM dpd_harvest_entries
GROUP BY 
  MONTH(FROM_UNIXTIME(logged_at)), 
  YEAR(FROM_UNIXTIME(logged_at))
  

Я предполагаю, что у вас есть tasks таблица со столбцом billable , который содержит 1 для оплачиваемых задач и 0 для не оплачиваемых задач. тогда ваш запрос мог бы выглядеть как:

 SELECT 
  MONTH(FROM_UNIXTIME(logged_at)), 
  YEAR(FROM_UNIXTIME(logged_at)),
  SUM(hours) total,
  SUM(IF(t.billable,hours,0)) billable,
  SUM(IF(t.billable,0,hours)) nonbillable
FROM dpd_harvest_entries e
INNER JOIN tasks t
ON e.task_id = t.task_id
GROUP BY 
  MONTH(FROM_UNIXTIME(logged_at)), 
  YEAR(FROM_UNIXTIME(logged_at))
  

Если вы просто разделили их запятой ids , вы можете преобразовать этот запрос в:

 SELECT 
  MONTH(FROM_UNIXTIME(logged_at)), 
  YEAR(FROM_UNIXTIME(logged_at)),
  SUM(hours) total,
  SUM(IF(task_id IN ({your id list}),hours,0)) billable,
  SUM(IF(task_id IN ({your id list}),0,hours)) nonbillable
FROM dpd_harvest_entries e
GROUP BY 
  MONTH(FROM_UNIXTIME(logged_at)), 
  YEAR(FROM_UNIXTIME(logged_at))
  

Где {your id list} следует заменить на ex: IN (1, 4, 55, 47)

И для будущего использования я бы настоятельно рекомендовал, ALTER TABLE dpd_harvest_entries MODIFY logged_at DATETIME что упростит многие запросы и позволит избавиться от FROM_UNIXTIME вызовов повсюду.

Это должно сработать..

 select a.month, a.year, (billable   non_billable) total, billable, non_billable
from (select month, year, sum(hours) billable
      from dpd_harvest_entries
      where task_id in (2, 3)
      group by month, year) a,
     (select month, year, sum(hours) non_billable
      from dpd_harvest_entries
      where task_id in (1))
      group by month, year) b
where a.month = b.month
and a.year = b.year
  

Я предполагаю, что вы можете преобразовать временные метки в месяц и год

Вам понадобятся 2 подзапроса (оплачиваемый и не оплачиваемый), в обоих из них есть столбец «год-месяц», а затем объедините их в своем столбце «год-месяц», чтобы объединить их, что-то вроде этого:

 SELECT 
billable.year_month as year_month,
billable.sum_hours as billable_hours,
non_billable.sum_hours as non_billable_hours,
(billable.sum_hours   non_billable.sum_hours) as total_hours

FROM (SELECT 
DATE_FORMAT(FROM_UNIXTIME(logged_at), '%Y-%m') as year_month,
SUM(hours) as sum_hours
FROM dpd_harvest_entries
WHERE task_id IN (1,2,3)
GROUP BY
1) as billable,

(SELECT 
DATE_FORMAT(FROM_UNIXTIME(logged_at), '%Y-%m') as year_month,
SUM(hours) as sum_hours
FROM dpd_harvest_entries
WHERE task_id IN (4,5,6)
GROUP BY
1) as non_billable

LEFT JOIN billable on billable.year_month = non_billable.year_month
  

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