SQL Loops - Calculating number of days between records in table

[Zukkster]

I have a table that has records in it for customers and the days they shopped. The fields are

Customer
DayShopped
Spend

I want to sort the table by customer, then by date and loop through the data adding a field that is the number of days between shopping trips for each customer. Obviously for the first shopping trip of each customer there's no calcualtion to do.

I've done a similar thing in Access using VBA and loops and recordsets, but I want to do it with a stored procedure in SQL Server. Am I correct in assuming that it would be a similar sort of process to;

1) Create a record set with the required sorts applied
2) Loop through the data comparing each record to the last and carrying out the calculataion
3) Insert each record into a new table

Any help with the code would be great. Pointers to web sites covering the right topics would be enough. I've tried searching in google for sql server loops, but there's a lot of junk to wade through.

Thanks.

 

[sneki]

Try with CURSOR:

declare @var1 as type1, @var2 as type2, ...
declare cusrorName cursor for
your_select_statement
open cursorName
fetch next from cusorName into @var1, @var2...
while @@fetch_status = 0
begin
do_what_you_want_with_your_record...

fetch next from cusorName into @var1, @var2...
end
close cursorName
deallocate cursorName

 

[moonshadow]

Although I personally hate them, you could also do this with a correlated subquery. Something like:
Update tbldata
set DaysBetweenTrips=
datediff(D, DayShopped, (select max(Dayshopped) from tbldata b where b.customer=a.customer and b.dayshopped<a.dayshopped))
from tbldata A

The syntax probably isn't quite right (like I said - I hate the things). The other way to do it, if you've got SQL 2000 is to right a function (taking dayshopped & customer as parameters), and using that in the update.

 

[bdreed35]

If you could post your Access VBA code for us to see, I think that we could give you a real solution.

~Brian