While Loop in t-SQL  SQL SERVER  2005

Hi All,
I am taking a chance on this.I am using SQL Server 2005 with t-sql syntax.
Now this is a bit of a guess that this would work as the other ways didn’t:

I have 2 databases.
I am using one table (CPETRACKING) from database A which will hold a modem number, the BTSid ( base station it touched ) and time-inserted(the time it hit the base station).
(I have pulled all this information into DB B so I can work of one database)
In Database B I have the Account Number of the customer, the modem id and the date the customer cancelled.
I want to find out the Last BTSid of an account that was cancelled.
The only way to join both tables is through Modem id.
The problem is that one modem can be used by more then one customer.
So if customer number 1 cancels the modem is passed onto Customer number 2, if this customer cancels also and I try to find the last BTSid hit by each account before the modem cancelled I am only getting back Customer Number 2 results.
I think if i used a While Loop this might work, I am just unsure how to script this and would appriciate much help.
I have tried using sub queries but that doesn’t work.
I tried bring back the max time_inserted but the problem there is that the modem can hit many different btsid so I will get a results for the max time inserted for each btsid hit.
I think if I used a While Loop this might work, I am just unsure how to script this.

What I want to do is:      
Find the Max row number from  CPETRACKING Table
If the cpetracking.time_inserted < =Account.Cancellation_date then pull the last btsid from
CPETRACKING Table
ELSE Go to the Max row number -1 from CPETRACKING table
If the cpetracking_Time_inserted <= cancellation date .........

 

To find the Row number on the CPETRACKING table I did the following:
SELECT modem,time_inserted, btsid, row_number() over (order by time_inserted) as rownum
from cpetracking  order by rownum desc;

This is the linking I use:
SELCT a.account_id, a.account_status_date,cpe.btsid,cpe.time_inserted,mac.value
FROM  BOSS_ACCOUNT a (account_status_date is the cancellation date when a.account_status_id = 2)
-- code to return ModemID associated with service just before it was cancelled
left join (select max(b.OrderedItemHIstoryID) as max_hist_id, b.AccountID
from BOSS_ORDERED_ITEM_HISTORY b
where b.DateAdded< CONVERT(DateTime, FLOOR( CONVERT( Float, GETDATE()))) -1
group by b.AccountID) eq_hist on eq_hist.AccountID = a.account_id
left join BOSS_ORDERED_ITEM_HISTORY eq_hist2 on eq_hist2.OrderedItemHistoryID = eq_hist.max_hist_id
left join dbo.BOSS_ORDERED_ITEM oi on oi.OrderedItemID = eq_hist2.OrderedItemID
left join BOSS_ORDERED_ITEM_TRACKING_VARIABLES_MAC mac on mac.ItemID = oi.OrderedItemID
 --Then I link CPETRACKING substring(eid,7,14) to mac.value (table above)to get the BTSid.
--Only bring back cancelled accounts
WHERE a.account_status_id = 2

 
Any suggestions would be greatly appriciated.
Regards,
MickyNinStart Free Trial