Does this need a cursor? 2

[MaffewW]

hello all,

I have a table and want to add sale reference number (srn) starting at say 1000 incrementing by one each time an outcome is a sale , eg

Row customer outcome SRN
1 cust1 no answer null
2 cust2 do not call null
3 cust3 sale 1000
4 cust4 no answer null
5 cust5 sale 1001
6 cust6 sale 1002

The number will be assigned at the end of each day. Does this need a cursor, and would it be a fairly straightforward one to learn on

 

[cheyney]

I'm not sure if this *needs* a cursor. However, if its performed at the end of each day when the server load is minimal, I would probably just write it using a cursor because its alot easier.

Do something like this (put this in a sproc and have a scheduled job call it at the end of the day):

DECLARE @LastSRN int
SELECT @LastSRN = ISNULL(max(SRN), 999)
FROM tblSales

DECLARE @CurrentRow int
DECLARE curAssignSRN CURSOR FOR
SELECT Row
FROM tblSales
WHERE SRN IS NULL AND
Outcome = 'sale'
ORDER BY Row ASC
OPEN curAssignSRN
FETCH NEXT FROM curAssignSRN INTO @CurrentRow
WHILE @@FETCH_STATUS = 0
BEGIN
SET @LastSRN = @LastSRN + 1

UPDATE tblSales
SET SRN = @LastSRN
WHERE Row = @CurrentRow

FETCH NEXT FROM AssignSRN INTO @CurrentRow
END
CLOSE curAssignSRN
DEALLOCATE curAssignSRN

Hope this helps,
Cheyney

 

[MaffewW]

Wow, thanks Cheyney, never expected such a thorough example. I started to have a go myself and have got something vaguely similar (never tried cursors before). I'm going to have a play with my code and yours (not today its almost 5.00pm in UK and the pub beckons after a hard day) and I'll come back on Monday and let you know how I get on.

 

[swampBoogie]

update salesTable st
set srn = (select 1000 + count(*)
from salesTable
where row < st.row
and outcome = 'sale' )
where srn is null
and outcome = 'sale'

 

[ajc123]

I don't think that SwampBoogie's answer will work... isn't count(*) static throughout the query result set?

This might work in an update trigger, so long as you used transaction controls properly.

Too bad the sequences mechanisim for Identity fields isn't exposed. (For example, Oracle has CREATE SEQUENCE.) That would be really handy.

 

[swampBoogie]

There is a corelation in the scalar subquery.

If you wan't a sql server specific solution you could do

declare @s int
set @s = coalesce((select min(srn) from t where outcome = 'sale'),999)

update salesTable set @s = srn = @s + 1
where outcome = 'sale' and srn is null

 

[MaffewW]

thanks both for the assistance, I got both to work but used swampboogie solution as I understand it better and it doesnt use a cursor