Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mk1matt
Starting Member
7 Posts |
 Posted - 2009-12-04 : 09:50:24
|
| Hi guys, I've actually already solved this, but I'm not sure I've done it in the right way. I've got a data set of loans that looks something like this:loan,cust_id,drawdown_date,maturity_date1,1,20020502,200706072,1,20030403,200802023,1,20080501,299912314,2,20031031,29991231The loan number is the PK on the table. Each row shows the date the loan started and redeemed.What I'm trying to do is identify periods of overlap of loans for the same customer (cust_id). For example loan 2 started before loan 1 ended, so I'd like to show it as one period of lending, from 2nd May 2002 to 2nd Feb 2008. There is a gap between loan 2 ending and loan 3 beginning, so despite it being for the same customer, I'd like to show it as a new record.The output needs to look like:cust_id,drawdown_date,maturity_date1,20020502,200802021,20080501,299912312,20031031,29991231So far I'm found a couple of ways of doing it. I used a cursor to scan through each record and compare against the previous (after sorting by cust_id, drawdown_date. I also tried looping through with a WHILE (using multiple selects on an identity column) and got the same kind of results. Neither of which seem ideal.Can anyone think of a better way to do this? I need to process 44,000 loans, so as quick as possible would be ideal! I'm happy to clarify any points if necessary.Thanks in advance,Matt |
|
|
mk1matt
Starting Member
7 Posts |
Posted - 2009-12-08 : 09:42:21
|
| Hi guys, I found the solution in the end. It came with the help from this link:http://msdn.microsoft.com/en-us/library/aa175934(SQL.80).aspxProcessing time for the query batch has halved! |
 |
|
|
|
|
|
|
|
Identifying overlapping records