Need Help Programming SQL to Run Cursor, Compare, and Loop

[binyamin]

I need to write a program in SQL that will compare the ID field of a table and then if the ID matches will compare the dates an account was opened and when it was closed to see if a customer with multiple accounts under the same ID has overlapping accounts or if the accounts were opened and closed consecutively. Any thoughts on the best way to code this?

[rmiao]

Do you have sample table schema, data and expected results? Want to compare between tables or something else?

[binyamin]

sample table schema:

ID decimal 9(10,0)
Account decimal 9(10,0)
Open_date datetime 8
Close_date datetime 8

I need to return a unique number of weeks an account was open without double counting weeks where two or more accounts were open per customer. So for instance let's say you have the following scenario:

Accounts for customer ID 1234

Account Open_date Close_date
1 2007-01-01 2007-02-23
2 2007-01-08 2007-02-09
3 2007-02-12 NULL

The customer would have 9 open weeks as of 2007-03-03. I need to write a cursor loop that first identifies if the ID from one record to the next matches and then how many weeks should be counted for each account without double counting overlapping weeks.

[MAK]

Please provide us a table with the actual data and the expected result.