Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
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.
Post #795517
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse