September 29, 2009 at 9:35 pm
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
end
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.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply