One way to do this is to create a calendar table that has dates, start times and end times and use that as shown below. There are ways that would require fewer lines of code (which I am sure one will post), but this affords you the luxury of:a) taking care of holidays or vacation daysb) changing your start time and end time at will even on a day by day basisc) Taking into account fractional hours (like if someone started at 8:17 AMCREATE TABLE #Calendar(Dt DATE NOT NULL PRIMARY KEY, startTime TIME, endTime TIME);WITH cte(Dt) AS( SELECT CAST('20130321' AS DATE) UNION ALL SELECT DATEADD(dd,1,Dt) FROM cte WHERE Dt < '20130331')INSERT INTO #Calendar SELECT Dt, '08:00', '17:00' FROM cte OPTION (MAXRECURSION 0);DECLARE @startDate DATE = '20130321';DECLARE @endDate DATE = '20130331';SELECT c.Dt, CASE WHEN startTime < '12:00' THEN DATEDIFF(mi,startTime,CAST('12:00' AS TIME))/60.0 ELSE 0 END AS AM, CASE WHEN endTime > '12:00' THEN DATEDIFF(mi,CAST('12:00' AS TIME),endTime)/60.0 ELSE 0 END AS PMFROM #Calendar c DROP TABLE #Calendar