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

Popular posts from this blog

monitor web browser programmatically in Android? -

Shrink a YouTube video to responsive width -

wpf - PdfWriter.GetInstance throws System.NullReferenceException -