Aadam Aadam - 5 months ago 17
SQL Question

oracle sum limited value, extra to other variable

Query:

select sum((out_time+0) - (in_time+0))*24 man_hours
from emp a,time_sheet b
where a.SUPERVISOR='43561'
and a.EMP_ID=b.EMP_ID;


Sample data in table

emp_id in_time out_time
40716 08-07-2016 09:00 08-07-2016 18:00
40716 07-07-2016 09:00 07-07-2016 18:00
40716 06-07-2016 09:00 06-07-2016 18:00
60383 06-07-2016 09:00 06-07-2016 18:00
60383 07-07-2016 09:00 07-07-2016 18:00
41223 07-07-2016 09:00 07-07-2016 18:00
41223 08-07-2016 09:00 08-07-2016 18:00


Result: Sum of differences from above query is 45

difference between time in each row is 9 hours.

Requirement : I want only <=8 hours to sum up. >8 hours should be as other value.

Current 9*5= 45, required 8*5 = 40 and extra 5

I tried with decode, I am getting some weird results, actually I am not getting any idea in mind. Pointing out in right way would be helpful.
Thanks

Answer

This is your basic query. There is one row for every employee for every day, time spent regular and time spent overtime (if there was any).

WITH MY_TABLE AS  -- Dummy data, leave this out on your enviroment
(
SELECT 40716 AS ID, TO_DATE('08-07-2016 09:00', 'DD-MM-YYYY HH24:MI') AS TIME_START, TO_DATE('08-07-2016 18:00', 'DD-MM-YYYY HH24:MI') AS TIME_END FROM DUAL UNION
SELECT 40716, TO_DATE('07-07-2016 09:00', 'DD-MM-YYYY HH24:MI'), TO_DATE('07-07-2016 18:00', 'DD-MM-YYYY HH24:MI') FROM DUAL UNION
SELECT 40716, TO_DATE('06-07-2016 09:00', 'DD-MM-YYYY HH24:MI'), TO_DATE('06-07-2016 18:00', 'DD-MM-YYYY HH24:MI') FROM DUAL UNION
SELECT 60383, TO_DATE('06-07-2016 09:00', 'DD-MM-YYYY HH24:MI'), TO_DATE('06-07-2016 18:00', 'DD-MM-YYYY HH24:MI') FROM DUAL UNION
SELECT 60383, TO_DATE('07-07-2016 09:00', 'DD-MM-YYYY HH24:MI'), TO_DATE('07-07-2016 18:00', 'DD-MM-YYYY HH24:MI') FROM DUAL UNION
SELECT 41223, TO_DATE('07-07-2016 09:00', 'DD-MM-YYYY HH24:MI'), TO_DATE('07-07-2016 18:00', 'DD-MM-YYYY HH24:MI') FROM DUAL UNION
SELECT 41223, TO_DATE('08-07-2016 09:00', 'DD-MM-YYYY HH24:MI'), TO_DATE('08-07-2016 18:00', 'DD-MM-YYYY HH24:MI') FROM DUAL)
SELECT -- Actual query
  ID,
  LEAST (8, TOTAL_TIME) AS REGULAR_TIME, -- MIN of actual time and 8 hours
  CASE                                   -- If  he worked less than 8 hours,
      -- OT is 0, otherwise actual-8
    WHEN TOTAL_TIME > 8
    THEN TOTAL_TIME - 8
    ELSE 0
  END AS OVER_TIME
FROM
  (
    SELECT
      ID,
      (TIME_END - TIME_START)*24 AS TOTAL_TIME -- Oracle date returns days,
      -- multiply by 24 to have hourse
    FROM
      MY_TABLE
  );

Result looks following

ID      REGULAR_TIME    OVER_TIME
40716   8               1
40716   8               1
40716   8               1
41223   8               1
41223   8               1
60383   8               1
60383   8               1

You can nest this query under another one and perform whatever you like, for example, group by id so you get total regular and over time in given time frame per empoyee.

Or sum it up, without any grouping to fulfil your original requirement.

SELECT
  SUM(REGULAR_TIME),
  SUM(OVER_TIME)
FROM(
-- nest previous select
);