I have a scheduling application where I need to build a html table report. I need to get data from my users, attandance, week, and shift tables. I also have an array that tells me how many attendance codes they have and what the descriptions are.
For the sake of the demo: http://sqlfiddle.com/#!2/8f8a6f
table users: table attendance: userid | name userid | code test | Test user test | 2 table week: table shift week | status userid | week | shift | status 1207 | 1 test | 1207 | tue | 1 1214 | 1 test | 1207 | wed | 1 1221 | 0
I need to get:
- userid :
users.userid - name :
users.name - in a loop of the attendance array
- sum of attendance codes : sum of
attendance.code where attendance.userid=users.userid AND code=1, sum of attendance.code 2, etc
- sum of attendance codes : sum of
- in a loop of
week where status != 0- count of shifts worked : count
shift where shift.userid = users.userid AND shift.week = week.week AND status != 0
- count of shifts worked : count
So I get something like this:
User ID | Name | Attendance | Att2 | Week of | Week of
| | code 1 (late)|(unexcused)| 12/07 | 12/14
test | Test user | 0 | 1 | 2 | 3
I currently have a ton of loops. I get the users table into an array, then loop that array for each user, then loop my attendance array querying the attendance table. then loop the users array again querying the shifts table for each week. I was looking to see if there's a way to do it in SQL.
