Workload calculation Problem - Printable Version

Workload calculation Problem - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Workload calculation Problem (/showthread.php?tid=61143)

Workload calculation Problem - El Forum - 09-30-2014

[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..

Workload calculation Problem - El Forum - 10-01-2014

[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