sql - SUM in an ascending order, breaking by limit of sum -

i'm trying sum hours sql, can't sum it. sum needs in ascending order, , when gets limit of boundary, continues summing in column or row.

the data have this:

employee_id |    date     | overtime_day | overtime_night 48          |  05/03/2014 |   3 hours    |   4 hours 48          |  05/04/2014 |   9 hours    |   1 hours 48          |  05/10/2014 |   1 hours    |   1 hours 48          |  05/20/2014 |   9 hours    |   4 hours             |             |= 22 hours    | = 10 hours 

the boundaries sum this:

overtime (day+night) 50%: 0 15 overtime (day+night) 100%: > 15 

but need know if it's day hour or night hour.

overtime 50% hours 0 15, no matter if it's day or night: 3 hours [05/03/2014] [day] 4 hours [05/03/2014] [night] 8 hours [05/04/2014] [day] (2 hours left day, left hours , next hours 100%) overtime 100% hours above 15: 1 hours [05/04/2014] [day] 1 hours [05/04/2014] [night] 1 hours [05/10/2014] [day] 1 hours [05/10/2014] [night] 9 hours [05/20/2014] [day] 4 hours [05/20/2014] [night] 

then have:

overtime 50% day: 11 hrs overtime 50% night: 4 hrs overtime 100% day: 11 hrs overtime 100% night: 6 hrs 

result want

employee_id | overtime_day_50% | overtime_night_50% | overtime_day_100% | overtime_night_100%  48          |   11 hours       |   4 hours          |   11 hrs          |    6 hrs 

or

employee_id |       type           |   hours     48          |  overtime_day_50%    |   11 hours 48          |  overtime_night_50%  |   4 hours 48          |  overtime_day_100%   |   11 hours 48          |  overtime_night_100% |   6 hours 

(it doesn't matter row-way or column-way)

sorry if didn't make myself clear , don't know if it's possible accomplish calculation sql.

any ideas appreciated. thanks!

---

edit

i have select can overtime 50% , overtime 100%, can't separate day , night hour.

with data (            select 48 employee_id, '05/03/2014' "date", 3 overtime_day, 4 overtime_night dual union select 48 employee_id, '05/04/2014' "date", 9 overtime_day, 1 overtime_night dual union select 48 employee_id, '05/10/2014' "date", 1 overtime_day, 1 overtime_night dual union select 48 employee_id, '05/20/2014' "date", 9 overtime_day, 4 overtime_night dual ) select     employee_id,     case when sum(overtime_day + overtime_night) >= 15       15     else       sum(overtime_day + overtime_night)     end overtime_50,      greatest(0,sum(overtime_day + overtime_night) - 15) overtime_100  data group employee_id 

result:

employeed_id   |   overtime_50    |   overtime_100     48         |        15        |         17 

working data in same format of original, i.e. adding new columns instead of new rows, can get

with d (   select employee_id, "date", overtime_day, overtime_night        , sum(overtime_day + overtime_night)           on (order "date") - overtime_night total_day        , sum(overtime_day + overtime_night)           on (order "date") total_night     data ) select employee_id      , sum(case when total_day < 15 overtime_day                 when total_day < 15 + overtime_day                    overtime_day - (total_day - 15)                 else 0             end) overtime_day_50      , sum(case when total_night < 15 overtime_night                  when total_night < 15 + overtime_night                    overtime_night - (total_night - 15)                 else 0             end) overtime_night_50      , sum(case when total_day < 15 0             when total_day < 15 + overtime_day total_day - 15             when total_day > 15 overtime_day        end) overtime_day_100      , sum(case when total_night < 15 0             when total_night < 15 + overtime_night total_night - 15             when total_night > 15 overtime_night        end) overtime_night_100   d group employee_id 

sqlfiddle demo

in cte it's calculated rolling total overtime day , night, in formula total overtime day subtracted overtime_night sum of previous overtime , overtime_day of current row.
in main query case use 'simple' algorithm calculate values:

• if total hour less 15, current hour
• if total hour more 15 within buffer of current hour part of current hour valid
• otherwise it's not @ 50%

the part 100% work opposite

• if total hour less 15, not @ 100%
• if total hour more 15 within buffer of current hour part exceed 15
• otherwise it's @ 100%

---

to have more generic case, in comment op it's possible create other groups

     , sum(case when total_day < %bstart% 0                 when total_day < %bstart% + overtime_day total_day - %bstart%                 when total_day < %bend% , total_day > %bstart% overtime_day                 when total_day < %bend% + overtime_day overtime_day - (total_day - %bend%)                 else 0             end) overtime_day_60      , sum(case when total_night < %bstart% 0                 when total_night < %bstart% + overtime_night total_night - %bstart%                 when total_night < %bend% , total_night > %bstart% overtime_night                 when total_night < %bend% + overtime_night overtime_night - (total_night - %bstart%)                 else 0             end) overtime_night_60 

with %bstart% , %bend% start , end values of blocks, not flexible, more 2 block different approach better, , allow remove magic numbers query move them in table

with d (   select employee_id, "date", overtime_day, overtime_night        , sum(overtime_day + overtime_night) on (order "date")         - (overtime_day + overtime_night) total_begin        , sum(overtime_day + overtime_night) on (order "date")        - overtime_night total_day        , sum(overtime_day + overtime_night) on (order "date") total_night     data ), block (   select 1 id, 'overtime_50' name, 0 bstart, 15 bend dual   union   select 2, 'overtime_60', 15, 20 dual   union    select 3, 'overtime_100', 20, 999 dual )   select employee_id        , block.name        , sum(case when total_begin < bstart , total_day > bstart total_day - bstart                   when total_day < bstart 0                   when total_day <= bend overtime_day                   when total_begin < bend , total_day > bend bend - total_begin              end) overtime_day        , sum(case when total_day >= bstart , total_night < bend overtime_night                   when total_begin < bend , total_day > bend 0                   when total_day > bstart , total_night > bend bend - total_day                   when total_day < bstart , total_night > bstart total_night - bstart              end) overtime_night     d          inner join block on                (total_begin < bstart , total_night > bstart)             or (total_begin >= bstart , total_begin < bend) group employee_id, block.name, block.id order block.id 

sqlfiddle demo data changed have block change @ both day , night.

all condition inequalities check boundaries.

the output format

employee_id | name         | overtime_day | overtime_night          48 | overtime_50  |           11 |              4          48 | overtime_60  |            2 |              3          48 | overtime_100 |            9 |              5 

a little different original op output request, here getting not difficult, if necessary.

if want in 1 row & multiple column format, use pivot operator.

    d (   select employee_id, "date", overtime_day, overtime_night        , sum(overtime_day + overtime_night) on (order "date")         - (overtime_day + overtime_night) total_begin        , sum(overtime_day + overtime_night) on (order "date")        - overtime_night total_day        , sum(overtime_day + overtime_night) on (order "date") total_night     data ), block (   select 1 id, 'overtime_50' name, 0 bstart, 15 bend dual   union   select 2, 'overtime_60', 15, 20 dual   union    select 3, 'overtime_100', 20, 999 dual ) select * (     select employee_id        , block.name        , sum(case when total_begin < bstart , total_day > bstart total_day - bstart                   when total_day < bstart 0                   when total_day <= bend overtime_day                   when total_begin < bend , total_day > bend bend - total_begin              end) overtime_day        , sum(case when total_day >= bstart , total_night < bend overtime_night                   when total_begin < bend , total_day > bend 0                   when total_day > bstart , total_night > bend bend - total_day                   when total_day < bstart , total_night > bstart total_night - bstart              end) overtime_night       d          inner join block on                (total_begin < bstart , total_night > bstart)             or (total_begin >= bstart , total_begin < bend)     group employee_id, block.name, block.id     order block.id )pivot (sum(overtime_day) day, sum(overtime_night) night (name) in ('overtime_50' overtime_50,'overtime_60' overtime_60,'overtime_100' overtime_100)) 

the output format is

employee_id | overtime_50_day | overtime_50_night | overtime_60_day | overtime_60_night | overtime_100_day | overtime_100_night          48 |              11 |                4  |               2 |                 3 |                9 |                  5