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
Best Answer
Pretending this is your real table:
Then this is one approach to get the results you're after (you'll need to replace
#Employees
withdbo.YourRealTableName
of course, and this assumes you won't have anyone who has worked more months than the number of rows in your version ofsys.all_columns
- if you have aNumbers
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.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 anEndDate
; 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:
Edit for updates (please stop changing the requirements):
Query:
Results I get (there are decimals because your salary won't always be nicely divisible by the number of working days):
Again, clean up: