sql server - SQL Select Statement with DateDiff SUM and Math operation -

having table columns below

employee id startdate    enddate 1           2014-07-01   2014-07-10 1           2014-07-11   2014-07-15 1           2014-07-16   2014-07-17 2           2014-07-18   2014-07-30 

in above data, want sum of whole date period employee , take % of contribution of of date column

select  sum(datediff(day,startdate,enddate)),          datediff(day,startdate,enddate) / sum(datediff(day,startdate,enddate))  dbo.[abovetable]  employeedid=1 

here, total days employee id 1 17 (10 + 5 + 2). there 3 records under him. first record, current row days difference / todaydays should result. i.e.

1. 10/17
2. 5/17
3. 2/17

should result. combination work out group by?

the problem 1 of scope/ order of operation. aggregate sum not available @ time want % calculation. first have sum , can %.

this can accomplished common table expression (cte) or sub query.

here's cte example.

with cte (select employeeid, sum(datediff(day,startdate,enddate)) sumdates,  dbo.[abovetable]  group employeeid) select employeeid, sumdates, datediff(day,startdate,enddate) / sumdates employee inner join cte on e.employeeid = cte.employeeid employeedid=1 

and here's inline query (sub query) example

select employeeid, sumdates, datediff(day,startdate,enddate) / sumdates employee inner join (select employeeid, sum(datediff(day,startdate,enddate)) sumdates,              dbo.[abovetable]              group employeeid) cte   on e.employeeid = cte.employeeid employeedid=1 

in both cases sum of dates being determined before division occurring. in cte example, cte materialized data before math.

in case of inline query, order of operations inside out calculates sub table, executes query.