VBScript and SQL Server 2005 compare current row to previous row

I am dealing with some legacy tech at my new job. Everything is either VBS or VBA and SQL Server 2005 is still running for at least 1 server, and 2008 on another. I can't change the tech I have to work with, so please don't respond with "Just use PowerShell" ... which is all I got from the Microsoft forums.

I need to modify an existing VBScript to iterate through the records of a table and give each page of a document a page number. The page number starts at 1 and resets to 1 every time the location folder changes, as defined by the row's value in the column Path. So I need to compare the current row's Path to the previous row's Path to see if there is a change.

From the specs request:

Use the following structure:

• Outermost folder = Box field

  • Sub-folder = File Folder field
    • Docs within will each begin at "0000001"
      • Sub-folder = File Folder field
        • Docs within will each begin at "0000001"

  • Sub-folder = File Folder field

    • Docs within will each begin at "0000001"

Here is a very simple but effective Excel script that works, unfortunately I have to use VBScript so a third-party software can run the script:

=IF  "Folder field in this row" = "Folder field in row above"
True = "above column" (beg doc of previous doc) + "pgcount of previous doc"
False = "1"

Here is the existing SQL statement from within the VBScript (Sorry for the naming convention - it is not my choice):

UPDATE tblpage 
SET tblpage.UID = newtable.keyid 
FROM tblpage inner join (SELECT PKEY, '" & prefix & "' + REPLICATE(0, " & padnum & " -
LEN(convert(varchar(" & padnum & "),(" & startnum & " + rank() OVER (ORDER
BY TBLDOC." & sortfield & ") + Page - 2)))) + CONVERT(nvarchar(" & padnum &
"), convert(varchar(" & padnum & "),(" & startnum & " + rank() 
OVER (ORDER BY TBLDOC." & sortfield & ") + Page - 2))) AS keyid 
FROM tblpage 
inner join tbldoc on tblpage.id = tbldoc.id) as newtable ON newtable.pkey = tblpage.pkey;

variables taken from user input in VBScript - explanation

prefix - optional text prefix to the iterative number ie "SET" for "SET0000001"

padnum - number of 0s before iteration begins ie "000000" for "SET0000001"

startnum - starting number for the iteration ie "1" in "SET0000001"

sortfield - which table column to sort by ie a table's UID.

The above SQL does the iteration correctly but doesn't reset on new folder. I have been looking into http://ift.tt/19vQWo7 but I am still quite confused.

My thoughts are that I join just the Path to the newtable based on the row's UID - 1, and then a case statement to compare them. If different, reset the page number. If the same, continue as normal.

Path is found in tblDoc. Look at UID in tblPage for the page numbering structure

Thanks!!