Find the days difference between two dates per month

I have a table 'data' in which there are two columns i.e. Startdate and Enddate.

Startdate is the date when an employee joined working in a company and Enddate is the date when he left. I need to find the working days of employee per month.

If the Startdate is '2015-06-21' and the Enddate is '2015-08-21' then the working days will be:

``````June 10days
July 31days
August 21days
``````

I have the query for the total days for the work:

``````Select datediff(dd,startdate,enddate)
from tablename
``````

but I need to take the days as above (per month).

Update

I actually have three columns, 'Startdate', 'Enddate' and 'Salary'. The full query needs to divide the salary over the calendar months. If salary is 620 then for the above dates sample the result is:

``````June-for 10 days = 100
July = 310
August = 210
``````

Pretending this is your real table:

``````CREATE TABLE #Employees
(
EmployeeID INT PRIMARY KEY,
StartDate  SMALLDATETIME,
EndDate    SMALLDATETIME
);

INSERT #Employees(EmployeeID, StartDate, EndDate)
VALUES(1,'20150621','20150821');
``````

Then this is one approach to get the results you're after (you'll need to replace `#Employees` with `dbo.YourRealTableName` of course, and this assumes you won't have anyone who has worked more months than the number of rows in your version of `sys.all_columns` - if you have a `Numbers` table that is a better approach). I added year to the output because, hopefully, you have employees that last longer than 12 months and probably some of those will last past January 1st - so being able to tell which June is which might be important.

``````;WITH x AS
(
SELECT rn1 = ROW_NUMBER() OVER (ORDER BY [object_id])-1
FROM sys.all_columns
),
y AS
(
SELECT
e.EmployeeID,
x.rn1,
rn2 = ROW_NUMBER() OVER (PARTITION BY e.EmployeeID ORDER BY x.rn1 DESC),
e.StartDate,
FROM x INNER JOIN #Employees AS e
ON x.rn1 <= DATEDIFF(MONTH, e.StartDate, COALESCE(e.EndDate, GETDATE()))
),
z AS
(
SELECT
EmployeeID,
s = CASE rn2 WHEN 1 THEN StartDate
e = CASE rn1 WHEN 0 THEN DATEADD(DAY, 1, EndDate)
FROM y
)
SELECT
EmployeeID,
[Month] = DATENAME(MONTH, s),
[Year] = YEAR(s),
WorkingDays = DATEDIFF(DAY, s, e)
FROM z
ORDER BY EmployeeID, s;
GO
``````

This works for all employees, but you can easily add a filter inside `y` to limit it to a single employee or a subset. You might also want to filter for only those employees with an `EndDate`; it wasn't clear to me what you wanted to do with employees who still work there, so I assumed you would want to count working days up until today.

Don't forget to clean up:

``````DROP TABLE #Employees
``````

``````CREATE TABLE #Employees
(
EmployeeID INT PRIMARY KEY,
StartDate  SMALLDATETIME,
EndDate    SMALLDATETIME,
Salary     INT
);

INSERT #Employees(EmployeeID, StartDate, EndDate, Salary)
VALUES(1,'20150621','20150821',620);
``````

Query:

``````;WITH x AS
(
SELECT rn1 = ROW_NUMBER() OVER (ORDER BY [object_id])-1
FROM sys.all_columns
),
y AS
(
SELECT
e.EmployeeID, e.Salary, x.rn1,
rn2 = ROW_NUMBER() OVER (PARTITION BY e.EmployeeID ORDER BY x.rn1 DESC),
e.StartDate,
FROM x INNER JOIN #Employees AS e
ON x.rn1 <= DATEDIFF(MONTH, e.StartDate, COALESCE(e.EndDate, GETDATE()))
),
z AS
(
SELECT
EmployeeID, Salary,
StartDate, EndDate,
s = CASE rn2 WHEN 1 THEN StartDate
e = CASE rn1 WHEN 0 THEN DATEADD(DAY, 1, EndDate)
FROM y
)
SELECT
EmployeeID,
[Month] = DATENAME(MONTH, s),
[Year] = YEAR(s),
WorkingDays = DATEDIFF(DAY, s, e),
Portion = Salary * 1.0 * DATEDIFF(DAY, s, e) / (1+DATEDIFF(DAY, StartDate, EndDate))
FROM z
ORDER BY EmployeeID, s;
``````

Results I get (there are decimals because your salary won't always be nicely divisible by the number of working days):

``````EmployeeID  Month   Year  WorkingDays  Portion
----------  -----   ----  -----------  ----------------
1           June    2015  10           100.000000000000
1           July    2015  31           310.000000000000
1           August  2015  21           210.000000000000
``````

Again, clean up:

``````DROP TABLE #Employees
``````

An alternative solution to the updated problem, using a recursive Common Table Expression:

``````CREATE TABLE #Employees
(
EmployeeID integer PRIMARY KEY,
StartDate  smalldatetime NOT NULL,
EndDate    smalldatetime NOT NULL,
Salary     smallmoney NOT NULL
);

INSERT #Employees
(EmployeeID, StartDate, EndDate, Salary)
VALUES
(1,'20150621','20150821', \$620);
``````

Solution:

``````WITH R AS
(
-- Anchor
SELECT
E.EmployeeID,
E.StartDate,
E.EndDate,
DaysDiff = 1 +
DATEDIFF(DAY, E.StartDate,
CASE
WHEN E.EndDate > CA2.MonthEnd THEN CA2.MonthEnd
ELSE E.EndDate
END),
SalaryPerDay = E.Salary / (DATEDIFF(DAY, E.StartDate, E.EndDate) + 1)
FROM #Employees AS E
CROSS APPLY
(
VALUES
(
DATEADD(DAY, 1 - DATEPART(DAY, E.StartDate), E.StartDate)
)
) AS CA (MonthStart)
CROSS APPLY
(
VALUES
(
)
) AS CA2 (MonthEnd)
WHERE
E.EndDate > E.StartDate

UNION ALL

-- Recursive
SELECT
R.EmployeeID,
CA.MonthStart,
R.EndDate,
1 + DATEDIFF(DAY, CA.MonthStart,
CASE
WHEN R.EndDate > CA2.MonthEnd THEN CA2.MonthEnd
ELSE R.EndDate
END),
R.SalaryPerDay
FROM R
CROSS APPLY
(
VALUES
(
DATEADD(DAY, 1 - DATEPART(DAY, R.StartDate), R.StartDate))
)
) AS CA (MonthStart)
CROSS APPLY
(
VALUES
(
)
) AS CA2 (MonthEnd)
WHERE
R.EndDate > CA.MonthStart
)
SELECT
R.EmployeeID,
TheMonth = DATENAME(MONTH, R.StartDate),
R.DaysDiff,
Portion = R.SalaryPerDay * R.DaysDiff
FROM R
ORDER BY
R.EmployeeID,
R.StartDate
OPTION (MAXRECURSION 0);
``````

Results:

Execution plan: