Saturday, 26 November 2011

Understanding Someone Elses MYSQL

Working through someone elses lovely Legacy code I've come to this MySQL statement that seems a bit odd.  So this blog is about trying to understand it.  Which by the way ultimately I don't.  If you do and can explain it to me better than these notes then let me know :)  After this blog I will be going away and trying prove it is wrong - so please check future posts.

SELECT pay_rate, LEFT(TIMEDIFF('24:00:00',TIMEDIFF(start_time, end_time)),5) AS hours, hours_only FROM shifts WHERE (employee_id = '160' AND shift_date BETWEEN '2010-11-12' AND '2010-11-18') OR (employee_id = '160' AND postdate_staff_invoice = '2')

SELECT pay_rate,  // we only looking to return 1 field

LEFT     // check here for 'LEFT' explained  -  -

take this example  mysql> SELECT left ('GirishTewari',6)as LeftString;

this means that in this example we're taking the first charcters of this result

TIMEDIFF('24:00:00',TIMEDIFF(start_time, end_time))


So what does

TIMEDIFF('24:00:00',TIMEDIFF(start_time, end_time))

mean.  Well

TIMEDIFF() returns expr1 – expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.

* in this example the second expression will be calculated from the row from the database we're in.

AS   - I normally use this after calling FROM as a form of shorthand ie
FROM tableOne AS t1


WHERE (employee_id = '160' AND shift_date BETWEEN '2010-11-12' AND '2010-11-18') OR (employee_id = '160' AND postdate_staff_invoice = '2')

Well at least this makes more sense - except for what's after the 'OR'

To be honest this mysql looks all wrong.  I think the next stage is to find out what result we're expecting.  IS IT JUST TO FIND OUT THE PAY RATE !!


Here's some searches I've made in looking for answers.


No comments: