Cursor solution

• robertfolkerts

  SSC Veteran

  Points: 271

  More actions

  September 29, 2009 at 9:35 pm

  #385466

  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.