I have a script that the only logical method I can use to resolve is a cursor but have been told to NEVER use a cursor. I need to compare one row of data to the previous one and mark the row as OK or not depending on various criteria. Can someone please point me in the right direction as to where to look for a resolution that isn't a cursor.
I have a table of visits, the fields are:
select startdate, owner, org, customer, customerid, dateapproved, reasonnotpaid from visits
I have another table called customers and in here I have a field called NoVisits, what I have to do is get the script to go through the table of visits and update the number of visits in the customers table and update the dateapproved field in the visits table, when it hits the maximum (held in the customers table as SessionsEligible) it should then update the reasonnotpaid field in the visits table to "maximum sessions reached".
This is the tables used:
How about a self join on the table using a rowid = rowid-1 criteria?
Can you post some table definition(s) or what you have so far so that we can get into more detail?
OK, with the extra detail I think you might make better use of a Common Table Expression and the Row_Number() function to find rows where the visit number is over the eligible count and then you can mark that as required, if you have some sort of row ID in the table that you have missed out(?).
Very basically the T-SQL would be like: With VisitStats as (Select RowID, ColA, ColB, Row_Number() OVER(Partition by CustomerID ORDER BY StartDate) as r_n, c.SessionsEligible FROM Visits as v INNER JOIN Customers as c on v.CustID = v.CustID )
UPDATE Visits set ReasonNotPaid = 'Max Sessions' FROM VisitStats as vs INNER JOIN Visits as v on (v.RowID = vs.RowID and r_n > SessionsEligible)