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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics