sql - Need help for join and some calculations on a MySql insert -

i'll try provide context can understand i'm trying achieve here. company uses open source software manage employees leaves (jorani, feel free google :) ).

there different types of leave (holidays, sick leave, etc.) , want calculate days "not used" holidays of 2016 , "copy" them type of leave called "remaining holidays 2016".

the important tables are:

entitleddays (here specify how many days of each type give employee)

id      employee    startdate   enddate     type    days    description 661     3           2016-01-01  2017-02-28  1       14.00   holidays 2016 1296    3           2016-01-01  2016-12-31  4       18.00   sick leave 2016 

leaves (this table has information leaves taken employees)

id      startdate   enddate     status  employee    cause               duration    type 2436    2016-08-01  2016-08-01  3       78          ok managers    1.00        1 2766    2016-09-05  2016-09-12  3       63          holidays            6.00        1 

so have:

entitled leaves: data stored in entitleddays table shown above. in our example let's have 14 days 2016 holidays.

taken leaves: leaves taken user, stored in table called leaves shown above. our example let's took day off first of august , 6 days on september.

available leaves: available days calculated: entitled days minus "taken leaves". examplee, 14 entitled days - 7 = 7 days. still have 7 days available holidays :d

so goal insert these 7 days user entitled days new type: "remaining days 2016" , every user. solution comes mind every user:

insert entitleddays (employee, startdate, enddate, type, days, description)      select id, '2017-01-01', '2017-02-31', '8', (entitled holidays 2016 minus taken leaves of type), 'remaining holidays 2016'     users 

where 8 new type of leave want copy days (remaining holidays 2016).

for example can taken holidays 2016 specific user doing this:

select sum(duration) leaves employee=3 , status=3 , type=1 

note: type 1 type of leave "holidays 2016" , status 3 means leave request accepted.

i can achieve of in single sql instruction can split in more if simpler or easiest manage/understand.

many in advance.

this how can handle calculation:

• sum entitleddays in subquery grouping datasets in table per employee
  • maybe group year? in case filtered 2016 via where-clause
• sum taken holidays in subquery, again grouping per employee
  • group year or filter directly 1 need
• join subquery onto other resultset of other query
• calculate (entitled days - taken leaves) in outer query

query:

select     entitled.employee,     '2017-01-01',     '2017-02-31',     '8' type,     entitled.days - takendays.days,     'remaining holidays 2016'     (         select             employee,             sum(days) days                     entitleddays                     startdate >= '2016-01-01'         , type = 1         group             employee     ) entitled left join (     select         employee,         sum(duration) days             `leaves`             startdate >= '2016-01-01'     , type = 1     group         employee ) takendays on takendays.employee = entitled.employee 

i not sure if how want calculate sums days of entitleddays , taken days. query checks if startdate >= '2016-01-01'.

also mentioned table users in attempt didn't provide details table, left out. guess use basis otherwise. in current query grouped result of entitleddays basis.

for insert

insert entitleddays (employee, startdate, enddate, type, days, description)      select         entitled.employee,         '2017-01-01',         '2017-02-31',         '8' type,         entitled.days - takendays.days,         'remaining holidays 2016'             (             select                 employee,                 sum(days) days                             entitleddays                             startdate >= '2016-01-01'             , type = 1             group                 employee         ) entitled     left join (         select             employee,             sum(duration) days                     `leaves`                     startdate >= '2016-01-01'         , type = 1         group             employee     ) takendays on takendays.employee = entitled.employee