if object_id('tempdb.dbo.#hours') > 0 drop table #hours
if object_id('tempdb.dbo.#holidays') > 0 drop table #holidays

create table #hours(person varchar(10), startTime datetime, endTime datetime)
insert #hours 
select 'theo', '2012-07-10 8:15:00.00', '2012-07-10 16:45:00.00'
union all select 'theo', '2012-07-11 8:01:00.00', '2012-07-11 17:45:00.00'
union all select 'theo', '2012-07-09 8:15:00.00', '2012-07-09 16:46:00.00'
union all select 'Jade', '2012-07-02 7:00:00.00', '2012-07-11 18:00:00.00'


select CAST('20120101' AS datetime) AS [DATE],'New Years' AS HOLIDAYS
into #holidays
union all select '20120220', 'Family Day'
union all select '20120406', 'Good Friday'
union all select '20120409', 'Easter Monday'
union all select '20120525', 'Victoria Day'
union all select '20120701', 'Canada Day'
union all select '20120806', 'Heritage Day'
union all select '20120903', 'Labour Day'
union all select '20121008', 'ThanksGiving Day'
union all select '20121111', 'Remember Day'
union all select '20121225', 'Christmas Day'
union all select '20121226', 'Boxing Day'


;with 
       a as (select convert(bigint,0) as n union all select 0),
       b as (select 0 as n from a as a cross join a as b),
       c as (select 0 as n from b as a cross join b as b),
       d as (select 0 as n from c as a cross join c as b),
       nums as (select row_number() over (order by (select 1)) as num from d as a cross join d as b),
       hrs as (select dateadd(hour, -num, dateadd(day, datediff(day, -1, getdate()),0)) as hr from nums),
       calendar as (
		select hr
			,isBus = case 
				when datepart(weekday,hr) between 2 and 6 --mon-fri assuming @@datefirst = 7
					and datepart(hour, hr) between 8 and 16 --8:00-9:00AM through 4:00-5:00PM
				then 1 
				else 0 
				end
		from	hrs)

select person,
	convert(decimal(6,2), sum(
		case
		when c.hr < h.startTime then datediff(minute, c.hr, h.startTime) 
		when h.endTime between c.hr and dateadd(hour, 1, c.hr) then datediff(minute, c.hr, h.endTime) 
		else 60
		end
		) / 60.0) BusinessHours
from calendar c
join	#hours h 
	on (hr > starttime or datediff(minute, hr, starttime) < 60)
	and (h.endTime > hr or datediff(minute, endtime, hr) < 60)
	and isBus = 1
left outer join #holidays hol
	on datediff(day, hol.[date], c.hr) = 0
where	hol.[date] is null
group by person
order by person 


OUTPUT:
person     BusinessHours
---------- ---------------------------------------
Jade       72.00
theo       24.03

select convert(datetime, null) as crdate
	,id
from sysobjects
UNION ALL
select crdate
	,convert(int, null) as id
from sysobjects
order by id

quote:

---

Originally posted by TG

You can always add bogus columns to each statement to make them similar.

select convert(datetime, null) as crdate
	,id
from sysobjects
UNION ALL
select crdate
	,convert(int, null) as id
from sysobjects
order by id

EDIT:
That would be if you want statement1 in different rows then statement2. If you need to combine the values across the same row then you would need some common value in both statements. Like perhaps [assignedTO]. If that is in both statements then you could probably get rid of the UNION ALL and add [task] table to the one I provided JOINed by assignedTO.

Be One with the Optimizer
TG

---