|
|
Calculating difference in time from 2 date and 2 time columns
Date: 10/28/06
(MySQL Communtiy) Keywords: no keywords
In my table I have 4 columns: item_date, start_time, item_date2, end_date
ie item_date, item_date = yyyy-mm-dd
and start_time, end_date = hh:mm:ss
I am trying to calculate the value of line items
My old query used to work fine but now we have added an end date.
Here's where I am at, but it doesnt work. I think I need to merge (item_date2 end_time) and (item_date start_time)
SELECT invoice_number,
sum( (( (TIME_TO_SEC(item_date2)+ TIME_TO_SEC(end_time)) - (TIME_TO_SEC(item_date) + TIME_TO_SEC(start_time)) )/3600) * rate) AS subtotal
FROM invoice_items
WHERE office_id='300' AND service_type!='225'
GROUP BY invoice_number
ORDER BY invoice_number
Taking row by row approach on one invoice to check totals...
SOLVED
I have some extra columns just so I can see if the subparts are calulating correctly
SELECT invoice_number, itemid, CONCAT_WS(' ',item_date2, end_time) AS time2, CONCAT_WS(' ',item_date, start_time) AS time1, TIMEDIFF(CONCAT_WS(' ',item_date2, end_time),CONCAT_WS(' ',item_date, start_time)) AS hours,
sum( TIMEDIFF(CONCAT_WS(' ',item_date2, end_time),CONCAT_WS(' ',item_date, start_time) ) * rate) AS rowtotal
FROM invoice_items
WHERE office_id='300' AND service_type!='225' AND invoice_number='8'
GROUP BY itemid
ORDER BY invoice_number
So the final query would be:
SELECT invoice_number,
sum( TIMEDIFF(CONCAT_WS(' ',item_date2, end_time),CONCAT_WS(' ',item_date, start_time) * rate) AS subtotal
FROM invoice_items
WHERE office_id='300' AND service_type!='225'
GROUP BY invoice_number
ORDER BY invoice_number
Source: http://community.livejournal.com/mysql/105248.html
|