How do I iterate over nested rows using a condition in SQL?

Question

I would like to create the equivalent of a FOR loop. I need this in SQL. My goal is to create a new column called Difference which calculates the date difference in days between the end of each row and the beginning of the next row. In essence I need to know if there are any breaks in contracts for each personID. RowID from min to max defines a contract with a specific organisation, such as each new contract with one organisation starts with 1.

My table is:

The SQL code I wrote is:

select RowID, start_contract, end_contract from table

open the_cursor

fetch next from the_cursor into @id

while @@FETCH_STATUS = 0
begin 
    select
        DATEDIFF(DAY, (select end_contract from table where RowID = @id-1), 
                      (select start_contract from t where RowID = @id)) AS [Difference]

        if (select RowID from t) = 1
            break
        else 
            continue

    fetch next from the_cursor into @id
end

close the_cursor
deallocate the_cursor

But I get an error:

Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

Could anybody help me please?

Thank you so much.

This post shows how to run a FOR loop in SQL: stackoverflow.com/questions/13565093/… — pgSystemTester
– pgSystemTester, Commented Jun 29, 2018 at 16:32

DDS · Accepted Answer · 2018-06-29 16:11:14Z

1

Try this:

select DATEDIFF(DAY, T.end_contract T.start_contract) AS [Difference], T.personID
from table T join table TT on TT.personID=t.personID
where TT.rowID = T.rowID + 1

answered Jun 29, 2018 at 16:11

DDS

2,48019 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LMHull Over a year ago

Thanks, self-join works really well. I had to modify the code to add a join by TT.rowID=T.rowID+1 instead of where, but it did the trick.

Collectives™ on Stack Overflow

How do I iterate over nested rows using a condition in SQL?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related