Stuck on sql logic

[EPNICO]

Hello:

I recently got into SQL at work, working on different projects and there is one that I'm stuck on and I'm spinning my wheels and I need some ideas.
Here is the problem:
I have a #temp file in this temp file there are policy records, my fields are policy_no,claimaint_name, draft_date,org_reserve,incurred_loss,expenses_paid.
if the policy numbers are equal with the same draft date I need to add org_reserves for both records and create one record with the fields above. If the draft_date is different I need to take the lowest draft date and create one record with the fields above. The result a table with just one occurance of the the policy and the fields mentioned above. Should I use cursor control? any ideas are welcome.

Thanks.

Nic

 

[rac2]

I think you might be able to use a correlated subquery for this problem. A correlated subquery depends on the outer query for its values. It is executed repeatedly, once for each row that is selected by the outer query. In this way it is like using a cursor in a loop.

The subquery finds the lowest (oldest?) draft_date for a policy.

The draft_date from this subquery can be used in a HAVING clause to exclude the rows with later draft_dates in a GROUP BY result.

I assume that org_reserve is a number such as a dollar amount and that you want to add up the org_reserve amounts if there are multiple rows for a policy and draft_date. So

SELECT t1.policy_no, t1.draft_date, SUM(t1.org_reserve)
FROM policies t1
GROUP BY t1.policy_no, t1.draft_date

HAVING t1.draft_date = 
( SELECT MIN(t2.draft_date) FROM policies t2 WHERE t1.policy_no = t2.policy_no )

This SELECT statement can then be used to insert rows into the temporary table.

INSERT INTO #policies_temp ( column list)
SELECT etc

I don't know that this will work but it might be an alternative to writing a procedure.