IF OBJECT_ID('ta') IS NOT NULL 
    DROP TABLE ta
Go
CREATE TABLE ta(Family NVARCHAR(4),Member INT,activity NVARCHAR(8),starttime varchar(10),endtime varchar(10))
Go
INSERT INTO ta
 SELECT 'A-01',1,'sleeping','0:00','6:30' UNION ALL
 SELECT 'A-01',1,'shower','6:30','6:45' UNION ALL
 SELECT 'A-01',1,'work','8:00','12:00' UNION ALL
 SELECT 'A-01',1,'read','12:00','23:59' UNION ALL
 SELECT 'A-01',2,'sleeping','0:00','6:30' UNION ALL
 SELECT 'A-01',2,'shower','6:30','6:45' UNION ALL
 SELECT 'A-01',2,'work','8:00','12:00' UNION ALL
 SELECT 'A-01',2,'eat','12:00','13:00' UNION ALL
 SELECT 'A-01',2,'work','13:00','20:00' UNION ALL
 SELECT 'A-02',1,'sleeping','0:00','6:30' UNION ALL
 SELECT 'A-02',1,'shower','6:30','6:45' UNION ALL
 SELECT 'A-02',1,'work','8:00','12:00' 
GO
--Start
SELECT ltrim(rowid)+'.00' as [hour],family,member,
(select top 1 activity from ta where family = a.family and   cast( '2008-01-01 '+ltrim(rowid)+':01' as datetime) between  dateadd(mi,1,cast('2008-01-01 '+starttime as datetime))  and
 cast('2008-01-01 '+endtime as datetime))  as activity
FROM (select distinct family ,member from ta) a
cross join (select rowid = row_number() over (order by object_id) 
            from sys.objects
            ) b
where rowid between 6 and 23 
order by family,member