hi how do i avoid using a cursor in this situation.
i want all the payment installment_nos to be ordered by their due dates.
only 'unpaid' payments can be ordered.
/*
drop table payment1
create TABLE payment1
(installment_id integer IDENTITY(1,1), amount float, installment_no int,
due_date datetime, payment_status_code varchar(100))
insert into payment1 (amount, installment_no, due_date,
payment_status_code)
values(1,1,'1-1-05', 'paid')
insert into payment1 (amount, installment_no, due_date,
payment_status_code)
values(1,1,'1-2-05', 'unpaid')
insert into payment1 (amount, installment_no, due_date,
payment_status_code)
values(1,1,'1-3-05', 'unpaid')
insert into payment1 (amount, installment_no, due_date,
payment_status_code)
values(1,1,'1-4-05', 'unpaid')
*/
DECLARE @.liPaymentId integer
DECLARE @.lipaymentCntr integer
/*REORDER installments*/
SET @.lipaymentCntr =1
SELECT @.lipaymentCntr = MAX(installment_no) +1 FROM payment1
WHERE payment_status_code = 'PAID'
IF @.lipaymentCntr IS NULL
SET @.lipaymentCntr =1
DECLARE UpdateCursor CURSOR FOR
SELECT installment_id
FROM payment1
WHERE payment_status_code = 'unpaid'
ORDER by due_date
open UpdateCursor
FETCH NEXT FROM UpdateCursor
INTO @.liPaymentId
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE payment1
SET installment_no = @.lipaymentCntr
WHERE installment_id = @.lipaymentId
SET @.lipaymentCntr = @.lipaymentCntr + 1
FETCH NEXT FROM UpdateCursor
INTO @.lipaymentId
END
CLOSE UpdateCursor
DEALLOCATE UpdateCursor
select * from payment1"ichor" <ichor@.hotmail.com> wrote in message
news:OFkwLY$4FHA.1028@.TK2MSFTNGP11.phx.gbl...
> hi how do i avoid using a cursor in this situation.
> i want all the payment installment_nos to be ordered by their due dates.
> only 'unpaid' payments can be ordered.
> /*
> drop table payment1
> create TABLE payment1
> (installment_id integer IDENTITY(1,1), amount float, installment_no int,
> due_date datetime, payment_status_code varchar(100))
> insert into payment1 (amount, installment_no, due_date,
> payment_status_code)
> values(1,1,'1-1-05', 'paid')
> insert into payment1 (amount, installment_no, due_date,
> payment_status_code)
> values(1,1,'1-2-05', 'unpaid')
> insert into payment1 (amount, installment_no, due_date,
> payment_status_code)
> values(1,1,'1-3-05', 'unpaid')
> insert into payment1 (amount, installment_no, due_date,
> payment_status_code)
> values(1,1,'1-4-05', 'unpaid')
> */
> DECLARE @.liPaymentId integer
> DECLARE @.lipaymentCntr integer
> /*REORDER installments*/
> SET @.lipaymentCntr =1
> SELECT @.lipaymentCntr = MAX(installment_no) +1 FROM payment1
> WHERE payment_status_code = 'PAID'
> IF @.lipaymentCntr IS NULL
> SET @.lipaymentCntr =1
> DECLARE UpdateCursor CURSOR FOR
> SELECT installment_id
> FROM payment1
> WHERE payment_status_code = 'unpaid'
> ORDER by due_date
> open UpdateCursor
> FETCH NEXT FROM UpdateCursor
> INTO @.liPaymentId
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> UPDATE payment1
> SET installment_no = @.lipaymentCntr
> WHERE installment_id = @.lipaymentId
> SET @.lipaymentCntr = @.lipaymentCntr + 1
> FETCH NEXT FROM UpdateCursor
> INTO @.lipaymentId
> END
> CLOSE UpdateCursor
> DEALLOCATE UpdateCursor
>
> select * from payment1
>
Try:
UPDATE payment1
SET installment_no =
(SELECT COUNT(*)
FROM payment1 AS P
WHERE P.due_date <= payment1.due_date
AND P.payment_status_code = 'unpaid')
WHERE payment_status_code = 'unpaid' ;
I'm assuming due_date is unique even though you didn't specify. If not, then
please explain what defines the sequence.
Even better perhaps, avoid putting the redundant installment_no column in
the table at all:
SELECT installment_id, amount,
(SELECT COUNT(*)
FROM payment1 AS P
WHERE P.due_date <=
payment1.due_date
AND P.payment_status_code = 'unpaid') AS installment_no,
due_date, payment_status_code
FROM payment1 ;
David Portas
SQL Server MVP
--|||this is what i actually want
thanks
update payment1
set installment_no =
(select installment_no from payment1 P
where payment_status_code = 'paid'
) +
(select count(*)
FROM payment1 AS P
WHERE P.due_date <= payment1.due_date
AND P.payment_status_code = 'unpaid')
From payment1
WHERE payment_status_code = 'unpaid'
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:xJudnT64sNDwcPLeRVnyvw@.giganews.com...
> "ichor" <ichor@.hotmail.com> wrote in message
> news:OFkwLY$4FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Try:
> UPDATE payment1
> SET installment_no =
> (SELECT COUNT(*)
> FROM payment1 AS P
> WHERE P.due_date <= payment1.due_date
> AND P.payment_status_code = 'unpaid')
> WHERE payment_status_code = 'unpaid' ;
> I'm assuming due_date is unique even though you didn't specify. If not,
> then please explain what defines the sequence.
> Even better perhaps, avoid putting the redundant installment_no column in
> the table at all:
> SELECT installment_id, amount,
> (SELECT COUNT(*)
> FROM payment1 AS P
> WHERE P.due_date <=
> payment1.due_date
> AND P.payment_status_code = 'unpaid') AS installment_no,
> due_date, payment_status_code
> FROM payment1 ;
> --
> David Portas
> SQL Server MVP
> --
>
