Запрос для агрегирования суммы по разным тарифным категориям в зависимости от использования

Сценарий: мне нужно рассчитать сумму суммы, применимую к каждому клиенту, на основе их почасовых ставок и времени (минут), в течение которых они предлагали/получали услуги в разных тарифных категориях.

Используя следующий запрос, я получаю следующие результаты:

SELECT pc.client_id, pr.hourly_rate, (SUM(tt.time_end) - SUM(tt.time_start)) as Difference
FROM  track_time as tt, project_track as pt, project as pr, project_clients as pc, clients as cl
WHERE tt.track_id = pt.track_id
AND pt.project_id = pr.project_id
AND pr.project_id = pc.project_id
AND pc.client_id = cl.client_id 
GROUP BY hourly_rate

РЕЗУЛЬТАТЫ

client_id   hourly_rate   Difference
1           50            6360
1           23            4080
1           43            5760
6           13            34680
7           60            32400
7           14            23340

Я пытаюсь выяснить, как СНАЧАЛА мне нужно преобразовать результат РАЗНИЦА в читаемые часы с помощью функции, ЗАТЕМ умножить преобразованный результат на соответствующий часовой_рейт, а затем получить СУММУ для каждого клиента.

Сначала я попробовал это (ниже), что действительно близко, но это не точно. Я думаю, что это не точно, потому что мне нужно сначала преобразовать столбец РАЗНИЦА.

SELECT SUM(Difference2) AS total
FROM (SELECT pc.client_id, pr.hourly_rate, (SUM(tt.time_end) - SUM(tt.time_start)) as Difference, ((SUM(tt.time_end) - SUM(tt.time_start)) * pr.hourly_rate) as Difference2
FROM  track_time as tt, project_track as pt, project as pr, project_clients as pc, clients as cl
WHERE tt.track_id = pt.track_id
AND pt.project_id = pr.project_id
AND pr.project_id = pc.project_id
AND pc.client_id = cl.client_id   
GROUP BY hourly_rate) AS table1

Любые идеи?


mysql php sum number-formatting multiplication
person jonthoughtit    schedule 01.07.2012    source источник
comment
Вы практически там, переносите умножение на почасовую ставку во внешний запрос, а вычисление разницы оставьте на подзапросе.   -  person Andrew    schedule 01.07.2012
comment
Вы заставили меня задуматься, поэтому я пришел к такому рабочему решению: SELECT SUM(T) as YES FROM ( SELECT pc.client_id, pr.hourly_rate, ROUND((( SUM(tt.time_end) - SUM(tt.time_start))/3600),2) as Difference, (ROUND((( SUM(tt.time_end) - SUM(tt.time_start))/3600),2) * hourly_rate) as T FROM track_time as tt, project_track as pt, project as pr, project_clients as pc, clients as cl WHERE tt.track_id = pt.track_id AND pt.project_id = pr.project_id AND pr.project_id = pc.project_id AND pc.client_id = cl.client_id AND cl.client_id = " . $db->prep($client_id) . " GROUP BY hourly_rate) as table1 Спасибо!   -  person jonthoughtit    schedule 02.07.2012

Ответы (1)


arrow_upward
1
arrow_downward

Это сработало:

SELECT SUM(T) as YES FROM (
SELECT pc.client_id, pr.hourly_rate, ROUND((( SUM(tt.time_end) - SUM(tt.time_start))/3600),2) as Difference, (ROUND((( SUM(tt.time_end) - SUM(tt.time_start))/3600),2) * hourly_rate) as T
FROM track_time as tt, project_track as pt, project as pr, project_clients as pc, clients as cl
WHERE tt.track_id = pt.track_id
AND pt.project_id = pr.project_id
AND pr.project_id = pc.project_id
AND pc.client_id = cl.client_id
AND cl.client_id = " . $db->prep($client_id) . " GROUP BY hourly_rate) as table1
person jonthoughtit    schedule 01.07.2012