Having Table columns as 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 the above data, I want to Sum of the whole date period for an employee and take the % of the contribution of the of the date as another column
SELECT SUM(DATEDIFF(day,StartDate,EndDate)),
DATEDIFF(day,StartDate,EndDate) / SUM(DATEDIFF(day,StartDate,EndDate))
FROM dbo.[abovetable]
WHERE EmployeedId=1
Here, total days for employee id 1 is 17 (10 + 5 + 2). there are 3 records under him. for the first record, current row days difference / todaydays should be the result. i.e.
Should be the result. Any combination will work out with group by?
The problem is one of scope / order of operation. The aggregate sum is not available at the time you want to do the % calculation. So you first have to generate the sum and then you can get the %.
This can be accomplished with a COMMON TABLE EXPRESSION (CTE) or a sub query.
Here's the CTE Example.
WITH CTE AS (SELECT EMPLOYEEID, SUM(DATEDIFF(day,StartDate,EndDate)) as SumDates,
FROM dbo.[abovetable]
GROUP BY EmployeeID)
SELECT EmployeeID, SumDates, DATEDIFF(day,StartDate,EndDate) / SumDates
FROM Employee
INNER JOIN CTE on E.EmployeeID = CTE.EmployeeID
WHERE EmployeedId=1
and here's an inline query (sub query) example
SELECT EmployeeID, SumDates, DATEDIFF(day,StartDate,EndDate) / SumDates
FROM Employee
INNER JOIN (SELECT EMPLOYEEID, SUM(DATEDIFF(day,StartDate,EndDate)) AS SumDates,
FROM dbo.[abovetable]
GROUP BY EmployeeID) CTE
ON E.EmployeeID = CTE.EmployeeID
WHERE EmployeedId=1
In both cases the sum of the dates is being determined before the division is occurring. In the CTE example, the CTE is materialized with data before the math.
In the case of the inline query, the order of operations is inside out so it calculates the sub table, then executes the query.