Start Date / End Date - need to separate out by month (days?)
I have a table that has EmpName, StartDate, EndDate, Hours
I need to be able to get total Hours by month by employee (Start/end dates can include 2 months, is usually no more than 5 days, could be 1 day)
Is it possible to do this in Report Builder or in SQL?
February 17th, 2011 10:09am
if the end date is in the next month, how would you determine how the hours of that row will be split between the months?
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2011 1:22pm
Split evenly amongst the days - if 4 days, divide total hours by 4
February 17th, 2011 2:17pm
declare @StartDate datetime = dateadd(day,-3,getdate())
declare @EndDate datetime = dateadd(hour,+4,getdate())
select datediff(hour,@StartDate,@EndDate)
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2011 2:22pm
I hate to admit that I'm not totally sure what to do with the response.
This is a basic query that gets the data I need to split up:
SELECT dbo.Employee.emp_lastName, dbo.Schedule.sch_fromDateTime, dbo.Schedule.sch_toDateTime, dbo.Schedule.sch_hours
FROM dbo.Employee INNER JOIN
dbo.Schedule ON dbo.Employee.emp_ID = dbo.Schedule.sch_employeeID
WHERE (dbo.Schedule.sch_fromDateTime >= CONVERT(DATETIME, '2011-01-01 00:00:00', 102)) AND (dbo.Schedule.sch_toDateTime <= CONVERT(DATETIME,
'2011-04-30 00:00:00', 102)) AND (dbo.Employee.emp_locationID = '11624116') AND (dbo.Employee.emp_deptID = 22484)
February 17th, 2011 2:32pm
ok if im understanding your requirements correctly here is one way of doing it:
aggregate sum the hours from a union selection that covers all 3 possibilities:
1)just use the hours value when the start and end date are in the same month ex: 5 hours worked from jan 2 to jan 4.. log 5 hours for january
2)find hours for first month when start and end date are in different months: use ratio of days in first month (start date to end of start month) to the total days for that record and multiply by the hours
ex: 30 hours worked from jan 30 to feb 1. 2 out of 3 days were in january so 2days/3days*30= 20 hours for january
3)find hours for the second month when start and end date are in different months: use ratio of days in second month(beginning of end month to end date) to the total days for that record and multiply by the hours
ex: 30 hours worked from jan 30 to feb 1. 1 out of 3 days were in february so 1days/3days*30= 10 hours for february
and then the aggregation is simply summing and grouping by month
and here is some untested and likely bugged sql that will hopefully get you an idea of how to implement this:
select mm, sum(hrs) from (
--hours when startdate and enddate in same month
select month(start_date) mm, hours hrs from temp
where month(start_date) = month(end_date)
union all
--hours in first month when startdate and enddate in diff month
select month(start_date) mm, (datediff(day, start_date, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,enddate)+1,0))))/(datediff(day, start_date, end_date))*hours hrs from temp
where month(start_date) <> month(end_date)
union all
--hours in last month when startdate and enddate in diff mont
select month(end_date) mm, (datediff(day, DateAdd(Day, 1, end_date - Day(end_date) + 1) -1, end_date))/(datediff(day, start_date, end_date))*hours hrs from temp
where month(start_date) <> month(end_date)
)
group by mm
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2011 3:13pm
Thank you very much! This was extremely helpful. The first part works perfect.
The 2nd part is calculating significantly more hours than it should
--hours in first month when startdate and enddate in diff month
select month(start_date) mm, (datediff(day, start_date,
DATEADD(s,-1,DATEADD(mm,
DATEDIFF(m,0,enddate)+1,0))))/(datediff(day, start_date, end_date))*hours hrs
from temp
where month(start_date) <>
month(end_date)
The 3rd part is returning 0 hours
--hours in last month when startdate and enddate in diff mont
select month(end_date) mm, (datediff(day,
DateAdd(Day, 1, end_date -
Day(end_date) + 1) -1, end_date))/(datediff(day, start_date, end_date))*hours hrs
from temp
where month(start_date) <>
month(end_date)
February 18th, 2011 4:58pm