We get data about the hours people have worked at different places. I must give the total hours worked and the dates when a person has worked (at least) 10 hours and (at least) 30 hours.
I have created a table that stores all the data from all those different systems.
I can solve this using a cursor, but I would prefer to solve it without one.
Code:
create table hoursWorked(
person int not null,
hours int not null,
WorkDate datetime not null
)
INSERT INTO hoursWorked (person, hours, WorkDate)
select 1, 4, '2009-01-01' UNION ALL
select 1, 2, '2009-01-01' UNION ALL --6
select 1, 3, '2009-01-02' UNION ALL --9
select 1, 4, '2009-01-03' UNION ALL --13 <- 10 hrs
select 1, 5, '2009-01-10' UNION ALL --18
select 1, 1, '2009-01-11' UNION ALL
select 1, 2, '2009-01-11' UNION ALL --21
select 1, 3, '2009-01-12' UNION ALL
select 1, 5, '2009-01-12' UNION ALL --29
select 1, 12, '2009-01-13' UNION ALL --41 <-30 hrs
select 2, 1, '2009-01-01' UNION ALL
select 2, 12, '2009-01-01' UNION ALL --13 <-10 hrs
select 2, 1, '2009-01-02' UNION ALL --14 (never 30 hrs)
select 3, 2, '2009-01-01' -- 2
-- behind -- the cumulated hours a person has worked
DROP table hoursWorked
The result should be
Code:
person hours date_10hrs date_30hrs
1 41 2009-01-03 2009-01-13
2 14 2009-01-01
3 2
It is possible that a person has worked at different locations on the same day.
I was thinking of writing it in recursive SQL, but I don't seem to get it working.
dBforums
>
Can this be calculeted without a cursor?
06-30-09, 07:13
Linear Mode
