Re: Need help in avoiding cursors

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ross Presser (rpresser_at_imtek.com)
Date: 12/21/04 

Date: Tue, 21 Dec 2004 12:39:29 -0500

On Mon, 20 Dec 2004 23:29:29 GMT, Bryan Bullard wrote:

> Hi,
>
> I have this query that summarizes curtain types of "transaction" records in
> a table called "transactions". The summary table called "resultset"
> contains foreign key column in transactions called "transaction_id" and a
> column called "gift_number" which represent the chronological order of the
> "transaction". So the one with the lowest date would have a 1, the next a
> 2, and so on for each "account_code". This query runs *very* slow and I
> would like to do this operation without a cursor.
>
> Here is the structure of the table "transactions":
>
> transactions
> account_code char(9)
> transaction_amount money
> transaction_date datetime
>
> Here is the query:
>
>
> declare @account_code varchar (32)
>
> create table resultset (
> transaction_id int,
> gift_number int
> )
>
> declare account_cursor cursor fast_forward
> for
> select distinct account_code
> from transactions
>
>
> open account_cursor
>
> fetch next
> from account_cursor
> into @account_code
>
> while @@FETCH_STATUS = 0
> begin
>
> select transaction_id,
> IDENTITY(int,1,1) as gift_number
> into #import
> from transactions
> where account_code = @account_code
> and transaction_amount > 0
> order by transaction_date asc
>
> insert into resultset (
> transaction_id,
> gift_number
> ) select transaction_id,
> gift_number
> from #import
>
> drop table #import
>
> fetch next
> from account_cursor
> into @account_code
>
> end
>
> close account_cursor
> deallocate account_cursor
>
>
> Note: I have to use the temp table "import" because you cannot use the
> IDENTITY function with and "into".
>
> Thank you,
> Bryan

You can get the effect you need by using a self-join rather than an
identity column.

CREATE TABLE resultset (
    transaction_id INT,
    gift_number INT
)

INSERT INTO resultset
  ( transaction_id, gift_number )
SELECT A.Transaction_ID,
 (SELECT COUNT(*) FROM transactions B
    WHERE B.account_code = A.account_code
      AND B.transaction_date <= A.transaction_date
      AND B.transaction_amount > 0) AS gift_number
FROM transactions
WHERE transaction_amount > 0
ORDER BY account_code, transaction_date

Relevant Pages