3

Is this possible in SQL? It seems like a simple concept. (I'm using SQL Server 2005)

Loop through all the rows in my SQL table compare the values in column m if one row =65 and the next equals 120 increase j, and print that value in another column (column q)

Sub InsertProductionCycle() 

Dim LR As Long 
Dim j As Integer 

j = 1 
LR = Range("G" & Rows.Count).End(xlUp).Row 

    For i = 1 To LR Step 1 

        Cells(i, "Q").Value = j 

        If Cells(i, "M").Value = 65 And Cells(i + 1, "M").Value = 190 Then 
            j = j + 1 
        End If 
    Next i 

End Sub

Any thoughts from the SQL experts? Can I do this in SQL? My thoughts about this is: Perhaps the loop is a bit of a stretch, (I can run that in a sql job), but can you compare the rows and insert the j loop value in another column. Thats where I'm stuck.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
akwarywo
  • 187
  • 1
  • 2
  • 11

1 Answers1

5

This was a fun one! There may be a more efficient way to do this, but you can do it in a single set-based statement without the need for loops or cursors.

Assuming a table that models your data that looks something like this (where i is basically your row number):

CREATE TABLE MyTable (i int, M int)

I used a self join to match i rows with i+1 rows and used a COUNT to figure out the Q column.

;WITH data AS (
    SELECT a.i, a.M, b.M as NextM
        , CASE WHEN a.M = 65 AND b.M = 190 THEN 1 ELSE 0 END AS shouldIncreaseQ
    FROM MyTable a
       LEFT OUTER JOIN MyTable b
          ON a.i + 1 = b.i
)
SELECT data.M, data.NextM
   , (SELECT COUNT(*) + 1 FROM data AS ref 
      WHERE ref.shouldIncreaseQ = 1 AND ref.i <= data.i) as Q
FROM data

If need be, you could use SELECT INTO to get the data into another table for future use.

Justin Swartsel
  • 3,451
  • 1
  • 20
  • 24
  • Thanks. This solution is brilliant, I like this way of thinking a lot. It certainly saved me a lot of time fooling around with loops. :D (I had trouble at first with it, but I broke it down and then it clicked for me.) – akwarywo Jul 11 '12 at 12:55
  • Glad it worked for you, akwarywo, and welcome to stackoverflow! If you're satisifed with the answer you can mark it as accepted to help anyone else that stumbles on this question. – Justin Swartsel Jul 11 '12 at 13:28
  • I'm having trouble with the select into portion. I just want select the count(*) + 1 tag (ie: Q). I've played with it for our an hour now and I keep getting the following error: The multi-part identifier "data.i" could not be bound. Any thoughts would be appreciated. I think the issue is probably due to joins. – akwarywo Jul 16 '12 at 15:59
  • Sorry I figured out my problem, I used another with statement to select just Q. Thanks again for your help. – akwarywo Jul 16 '12 at 17:39