SQL Question

Datediff can cause i can't execute my statement in SQL?

The example data :

Table Employee:

EMPL_KODE | EMPL_NAME |SEX |SECTION
001 | Michel |Male |HR
002 | Clara |Female |GA
003 | Rafael |Male |HR


Table Attendance:

EMPL_KODE | EMPL_NAME |DATE_IN |TIME_IN |TIME_OUT
001 | Michel |25.04.2016 |06:50 |15:40
002 | Clara |25.04.2016 |06:15 |15:43
003 | Rafael |25.04.2016 |06:25 |15:45
001 | Michel |26.04.2016 |06:23 |15:42
002 | Clara |26.04.2016 |06:10 |15:41
003 | Rafael |26.04.2016 |06:30 |15:42
001 | Michel |27.04.2016 |06:33 |15:42
002 | Clara |27.04.2016 |06:54 |15:44
003 | Rafael |27.04.2016 |07:00 |15:45


From both of those table, I want to :


  1. Join the
    employee
    and
    attendance
    tables

  2. Show total minutes or hours for every
    Empl_Kode



I want the output like this :

EMPL_KODE | EMPL_NAME | TIME_IN | TIME_OUT | TOTAL_MINUTES | TOTAL_HOURS


SQL code:

SELECT
EMPLOYEE.EMPL_KODE, EMPLOYEE.EMPL_NAME,
CAST(ATTANDANCE.DATE_IN + ATTANDANCE.TIME_IN AS TIMESTAMP) AS "TIME_IN",
CAST(ATTENDANCE.DATE_IN + ATTENDANCE.TIME_OUT AS TIMESTAMP) AS "TIME_OUT",
SUM(DATEDIFF(MINUTE, ATTENDANCE.TIME_IN, ATTENDANCE.TIME_OUT)) AS "TOTAL_MINUTES",
SUM(DATEDIFF(HOUR, ATTENDANCE.TIME_IN, ATTENDANCE.TIME_OUT)) AS "TOTAL_HOURS"
FROM
EMPLOYEE
JOIN
ATTENDANCE ON EMPLOYEE.EMPL_KODE = ATTENDANCE.EMPL_KODE
GROUP BY
EMPLOYEE.EMPL_KODE, EMPLOYEE.EMPL_NAME;


I get an error message:


Invalid expression in the select list (not contained in either an
aggregate function or the GROUP BY clause)


What's wrong with my code ? Can you help me please ?

Answer

Try this:

SELECT 
   EMPLOYEE.EMPL_KODE, 
   EMPLOYEE.EMPL_NAME,
   MIN(CAST(ATTANDANCE.DATE_IN + ATTANDANCE.TIME_IN AS TIMESTAMP)) AS "TIME_IN", 
   MAX(CAST(ATTANDANCE.DATE_IN + ATTANDANCE.TIME_OUT AS TIMESTAMP)) AS "TIME_OUT",
   SUM(DATEDIFF(MINUTE,ATTANDANCE.TIME_IN,ATTANDANCE.TIME_OUT)) AS "TOTAL_MINUTES",
   SUM(DATEDIFF(HOUR,ATTANDANCE.TIME_IN,ATTANDANCE.TIME_OUT)) AS "TOTAL_HOURS"
FROM EMPLOYEE
JOIN ATTANDANCE
     ON EMPLOYEE.EMPL_KODE=ATTANDANCE.EMPL_KODE
GROUP BY EMPLOYEE.EMPL_KODE, EMPLOYEE.EMPL_KODE;