# Thread: Can this be calculeted without a cursor?

1. Registered User
Join Date
Nov 2004
Posts
1,428

## Unanswered: Can this be calculeted without a cursor?

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.

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Until SQL Server gets the full ISO implementation of the windows functions (OVER clause), cursors are actually more efficient than set based methods for cumulative aggregates. I have a doc authored by Itzik Ben Gan bookmarked somewhere if you want more details...

3. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Oh yeah - just reread the title. Yes it can, but inefficiently

4. Registered User
Join Date
Nov 2004
Posts
1,428
Thank you for your response. The cursor solution is fast.

I gave the recursive SQL a thought, and I couldn't come up with an elegant (efficient) algorithm to summarise only the hours of the next day (greater than the highest day of the previously calculated cumulative day-hours and the smallest day of the not yet calculated days).
I have a doc authored by Itzik Ben Gan bookmarked somewhere if you want more details...

5. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Dunno about recursive SQL, but you can do it with a theta join.

Hmm. This looks like the article, but the one I meant is years old. Maybe a rehash?
http://www.sqlmag.com/Article/Articl...er_101736.html

6. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Ah - this is the one I meant:
OVER()

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•