Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Cursor solution Expand / Collapse
Posted Tuesday, September 29, 2009 9:35 PM


Group: General Forum Members
Last Login: Thursday, March 28, 2013 2:47 PM
Points: 11, Visits: 90
declare c cursor fast_forward for
select customer_id, order_date, order_amt
from dbo.DailySalesTotals order by customer_id, order_date desc;

open c;
declare @customer char(10), @date datetime, @amt decimal(8,2);
declare @prev_cust char(10) -- used to detect new clients
, @last_amt decimal(8,2) -- compared with previous date
, @last_date datetime; -- needed to print
select @prev_cust = '', @last_amt = -1;
loop: fetch next from c into @customer, @date, @amt;
while @@fetch_status = 0 begin
if @prev_cust != @customer
select @last_amt = @amt, @prev_cust = @customer, @last_date = @date;
else if @prev_cust = @customer and @last_amt < @amt
select @customer, ' dropped purchase amount on ', @last_date;
goto loop
close c;
deallocate c;

There is something unclean about procedural programming in SQL. But this will return data, even in SQL 2000. This is aggregate free, but it will have to walk every record. I'm curious to know how the performance will compare.
Post #795517
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse