sql server - Calculate SQL values into new insert -

i don't have enough points post images, here link table screen shot: my query screen shot

i have sql server table sick , leave time taken employees. our code did not enter forward balance 2016-08-01 (that has been fixed). need take forward balance 2016-07-01 (19.32) , add earned 2016-06-30 (6.66) , subtract taken between 2016-07-01 2016-07-31 (3, 6 , 1.5) , insert new record similar row 3 , 10 forward balance should 13.48.

how write sql statement employeeid? tried use sum didn't work out right.

select value   employeevacations  vacation_kind = 'sl'   , vacation_type = 'forward'    , creationdate = '2016-07-01'    , employeeid = 1775 

plus:

select value   employeevacations  vacation_kind = 'sl'    , vacation_type = 'earned'    , creationdate = '2016-06-30'    , employeeid = 1775 

minus:

select sum(value) totaltaken   employeevacations  vacation_kind = 'sl'    , vacation_type = 'taken'    , creationdate >= '2016-07-01'    , creationdate <= '2016-07-31'    , employeeid = 1775 

and take value these 3 , insert new record like

insert employeevacations ([employeeid], [vacation_kind], [vacation_type], [creationdate], [value])  values (1775, 'sl', 'forward', '2016-08-01', 13.48) 

i didn't reproduce data couldn't test this, think should go approach this:

insert employeevacations ([employeeid], [vacation_kind], [vacation_type], [creationdate], [value]) select employeeid, 'sl', 'forward', '2016-08-01', sum(value) (     select employeeid, value       employeevacations      vacation_kind = 'sl'     , vacation_type = 'forward'      , creationdate = '2016-07-01'       union      select employeeid, value       employeevacations      vacation_kind = 'sl'      , vacation_type = 'earned'      , creationdate = '2016-06-30'       union      select employeeid, -value      employeevacations      vacation_kind = 'sl'      , vacation_type = 'taken'      , creationdate >= '2016-07-01'      , creationdate <= '2016-07-31'  ) subq group employeeid 

as can see, i'm using subquery info need , nothing else, , group employee.

of course, should first run without insert line make sure getting need.