I'm learning how to use conditional clauses in sqlite language. On SO I found that link: http://ift.tt/1Lk7HFK, in an answer/comment (I didn't mark the question, I closed my browser too fast, sorry for that).
I write (in a more readable way. With sqlite I miss the {}
so do not hesitate (if you want it) to rewrite it with {}
just to help me to clearly see the blocks) here the part wich interest me :
SELECT
tbl_hours.week_number 'Week #'
, tbl_employee.employee_id 'Id Number'
, tbl_employee.first_name||' ' ||tbl_employee.last_name 'Name'
, hours /*1*/
, CASE WHEN (hours/*2*/>40)
THEN hours-40 ELSE 0 END
AS overtime
FROM tbl_employee, tbl_Hours
WHERE tbl_employee.employee_id = tbl_hours.employee_id
ORDER BY tbl_hours.week_number,tbl_employee.employee_id;
there is hours
but actualy I don't know what is it. That seems to be a variable, but how sqlite considere /*1*/
and /*2*/
for each employee ?
I add the result (still from the same link)
WEEK # ID NUMBER NAME HOURS OVERTIME
------- ------------ ----------------- ------------ ---------
1 50 JONATHAN SMITH 37.5 0
1 60 GERALD MARSHAL 40.25 0.25
2 50 JONATHAN SMITH 28.5 0
2 60 GERALD MARSHAL 40.25 0.25
3 50 JONATHAN SMITH 41.5 1.5
3 60 GERALD MARSHAL 41.5 1.5
4 50 JONATHAN SMITH 40 0
4 60 GERALD MARSHAL 47.75 7.75
5 50 JONATHAN SMITH 40 0
5 60 GERALD MARSHAL 40 0
Aucun commentaire:
Enregistrer un commentaire