• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Workload calculation Problem

#1
[eluser]Bigil Michael[/eluser]
In a school management system, I want to calculate the workload of each teacher in that school. I have created 3 tables, given below

1. staff table

stf_id stf_firstname
1 A
2 B
3 C

2. taff_subjects table

id stf_id subjects

1 1 1
2 1 2
3 1 3
4 2 1
5 2 3

3. timetable table

in this table i am saving only subjects

id period monday tuesday wednesday thursday friday saturday

1 2 1 .....
2 1 2 ......
3 3 ....
4 2
5 1

I want the result like this

staff name Monday Tuesday wednesday thursday friday saturday total

A 3 periods 2prds 5prds ..................... 25 prds

B 1 prds 6 prds ........................... 22 prds


written the query for monday only

Code:
$this->db->select('S.stf_firstname as teacher, count(T.monay) as monday_count');  
  $this->db->from('staffs as S');  
  $this->db->join('staff_subjects as SS','SS.stf_id = S.stf_id','LEFT');
  $this->db->join('timetable as T','T.monday = SS.subjects OR T.tuesday = SS.subjects OR T.wednesday = SS.subjects OR T.thursday = SS.subjects OR T.friday = SS.subjects OR T.saturday = SS.subjects ','LEFT');
  $this->db->group_by('S.stf_id');  
  $result_courses = $this->db->get();  
  return $result_courses->result_array();

but it did not print the correct count.

can any one solve this query or suggest an alternative method to solve this issue..

#2
[eluser]Bigil Michael[/eluser]
I have modified the table like this

and instead of subjects teachers id has been saved.

id Monday Tuesday wednesday thursday friday saturday

1 2 3 1 5 6
2 1 4 3 2 5


Now I want to take count of each days

written query like this

Code:
SELECT `S`.`stf_firstname` as teacher, count(T.monday) as monday_prd  FROM (`staffs` as S)
LEFT JOIN `timetable` as T ON `T`.`monday` = `S`.`stf_id`
GROUP BY `S`.`stf_id`
This code running fine and prints count of monday

Code:
SELECT `S`.`stf_firstname` as teacher, count(T.monday) as monday_prd,count(T1.tuesday) as tue_period  FROM (`staffs` as S)
LEFT JOIN `timetable` as T ON `T`.`monday` = `S`.`stf_id`
LEFT JOIN `timetable` as T1 ON `T1`.`tuesday` = `S`.`stf_id`
GROUP BY `S`.`stf_id`
It prints wrong count

can any one help to print multiple counts from same table with single left join



Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.