Calculate salary of tutor based on distinct sittings using mysql -
i have following table denoting tutor teaching pupils in small groups. each pupil has entry database. pupil may alone or in group. wish calculate tutors "salary" such: payment based on time spent - means each sitting (with 1 or more pupils) 1 sitting calculated - distinct sittings! start , end times unix times.
<pre> start end attendance 1359882000 1359882090 1 1359867600 1359867690 0 1359867600 1359867690 1 1359867600 1359867690 0 1360472400 1360477800 1 1360472400 1360477800 1 1359867600 1359867690 1 1359914400 1359919800 1 1360000800 1360006200 1 1360000800 1360006200 0 1360000800 1360006200 1 </pre>
this tried: no success - can't right duration (number of hours distinct sittings)
select year(from_unixtime(start)) year, monthname(str_to_date(month(from_unixtime(start)), '%m')) month, count(distinct start) sittings, sum(truncate((end-start)/3600, 1)) duration schedules group year(from_unixtime(start)), month(from_unixtime(start))
thanks proposals / support!
edit: required results
rate = 25 year month sittings duration bounty 2013 february 2 2.2 2.2*25 2013 april 4 12.0 12.0*25
you subqueries, i've had play sql fiddle, how you. link sql fiddle : http://sqlfiddle.com/#!2/50718c/3
select year(d.date) year, month(d.date) month, count(*) sittings, sum(d.duration) duration_mins ( select date(from_unixtime(s.start)) date, s.attendance, end-start duration schedules s ) d group year, month
i couldn't see attendance comes @ present, didn't specify. inner query responsible taking schedules, extracting start date, , duration (in seconds).
the outer query uses these derived values groups them sums. elaborate here i.e. maybe want select attendance > 0, or maybe want multiply attendance.
in next example have done this, calculating duration in hours instead, , calculating applicable duration sessions have >1 attendance along appropriate bounty assuming bounty == hours * rate : http://sqlfiddle.com/#!2/50718c/21
select year(d.date) year, month(d.date) month, count(*) sittings, sum(d.duration) duration, sum( if(d.attendance>0,1,0) ) sittingsworthbounty, sum( if(d.attendance>0,d.duration,0) ) durationforbounty, sum( if(d.attendance>0,d.bounty,0) ) bounty ( select date(from_unixtime(s.start)) date, s.attendance, (end-start)/3600 duration, (end-start)/3600 * @rate bounty schedules s, (select @rate := 25) v ) d group year, month
the key point here, in subquery calculation per-row. main query responsible grouping results , getting totals. if statements in outer query moved subquery instead, example. included them see values came from.
Comments
Post a Comment