Need a query to return current row with value from a prior record

I have a Contracts table that looks like this:

LoanDate           BranchId            NamesId      DaysPastDue
1/1/2013           100                  1            10
4/5/2013           100                  1            15
8/2/2013           100                  1            3
2/1/2013          200                  2            22
5/6/2013           200                  2            12
9/7/2013           200                  2            6
3/1/2013           300                  3            4
6/1/2013           300                  3            31
9/20/2013           300                  3            25



I need a query to return a results set per branchid/namesid with the DaysPastDue for the previous record for that branchid/namesid  as "DaysPastDueAtRenewal".  
 
The results should look like this:

LoanDate           BranchId            NamesId      DaysPastDueAtRenewal
4/5/2013           100                  1            10
8/2/2013           100                  1            15
2/1/2013           200                  2            3
5/6/2013           200                  2            22
9/7/2013           200                  2            12
3/1/2013           300                  3            6
6/1/2013           300                  3            4
9/20/2013           300                  3            31
Delta7428Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
The simplest way to code this would involve an unique row ID field of some type, but this should work.  The assumption here is that the query requires at least two rows to output data for this report.  If it is OK to show rows where there has only been one late renewal, then the query will need to be tweaked to use the LEFT JOIN instead of the INNER JOIN.

This version uses a simple CTE.  You can also do this with a Self Join.

WITH Base AS (
	SELECT 
		LoanDate, 
		BranchID, 
		NamesID, 
		DaysPastDueAtRenewal, 
		ROW_NUMBER() OVER (PARTITION BY branchID, NamesID ORDER BY branchID, NamesID, LoanDate) AS rowID
	)

SELECT 
	b.LoanDate,
	b.BranchID,
	b.NamesID,
	bp.DaysPastDueAtRenewal
FROM Base AS b
INNER JOIN Base AS bp
	ON b.branchID = bp.BranchID
	AND b.NamesID = bp.NamesID
	AND bp.RowID = b.RowID - 1

Open in new window

0
Delta7428Author Commented:
I'll have to get back to this in the morning.  I did a quick run and getting error:

Incorrect syntax near the keyword 'WITH'.
Msg 195, Level 15, State 10, Line 7
'ROW_NUMBER' is not a recognized function name.
0
Brendt HessSenior DBACommented:
What version of SQL Server are you running on?  Also, try placing a semicolon before the word WITH:

; WITH Base AS ....

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PortletPaulfreelancerCommented:
hopefully the comment above clears the problem, if not:

WITH is associated to Common Table Expression (CTE) that was introduced in SQL Server 2005

row_number() was also introduced at SQL 2005 I believe.

are you using SQL 2000?
0
Delta7428Author Commented:
Well ... when I connect to SSMS, the splash screen display "Sql Server 2005".

I ran a SELECT @@VERSION query and it comes up:
Microsoft SQL Server  2000 - 8.00.2066 (Intel X86)   May 11 2012 18:41:14   Copyright (c) 1988-2003 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Apparently I am running Sql Server 2000 on SSMS 2005  A Google search tells me @@row_number() did not exist until Sql Server 2005.

Is there another way to do this?
0
PortletPaulfreelancerCommented:
Yes, there will be a way, but before embarking on that I'd like to confirm if the results given in the question are indeed the wanted results. For example; there are 9 source records and 8 result records. Why does branchid 100 have only 2 results but branchid's 200 & 300 have 3 result rows each?

To possibly help understand why I ask, here is the query supplied above, run against your sample data - it produces 6 result rows (2 per branchid):
**[Results]**:
    
    |   LOANDATE | BRANCHID | NAMESID | DAYSPASTDUEATRENEWAL |
    |------------|----------|---------|----------------------|
    | 2013-04-05 |      100 |       1 |                   10 |
    | 2013-08-02 |      100 |       1 |                   15 |
    | 2013-05-06 |      200 |       2 |                   22 |
    | 2013-09-07 |      200 |       2 |                   12 |
    | 2013-06-01 |      300 |       3 |                    4 |
    | 2013-09-20 |      300 |       3 |                   31 |


    CREATE TABLE Contracts
    	([LoanDate] datetime, [BranchId] int, [NamesId] int, [DaysPastDue] int)
    ;
    	
    INSERT INTO Contracts
    	([LoanDate], [BranchId], [NamesId], [DaysPastDue])
    VALUES
    	('2013-01-01 00:00:00', 100, 1, 10),
    	('2013-04-05 00:00:00', 100, 1, 15),
    	('2013-08-02 00:00:00', 100, 1, 3),
    	('2013-02-01 00:00:00', 200, 2, 22),
    	('2013-05-06 00:00:00', 200, 2, 12),
    	('2013-09-07 00:00:00', 200, 2, 6),
    	('2013-03-01 00:00:00', 300, 3, 4),
    	('2013-06-01 00:00:00', 300, 3, 31),
    	('2013-09-20 00:00:00', 300, 3, 25)
    ;

**Query 1**:

    /* requires SQL 2005+ */
    
    WITH Base AS (
    	SELECT 
    		LoanDate, 
    		BranchID, 
    		NamesID, 
    		DaysPastDue, 
    		ROW_NUMBER() OVER (PARTITION BY branchID, NamesID 
                               ORDER BY branchID, NamesID, LoanDate) AS rowID
         FROM Contracts --<< missing
    	)
    
    SELECT 
    	convert(varchar(10),b.LoanDate, 121) as LoanDate,
    	b.BranchID,
    	b.NamesID,
    	bp.DaysPastDue as DaysPastDueAtRenewal --<< change
    FROM Base AS b
    INNER JOIN Base AS bp
    	ON b.branchID = bp.BranchID
    	AND b.NamesID = bp.NamesID
    	AND bp.RowID = b.RowID - 1
    order by 
            b.BranchID,
            b.LoanDate
    


  [1]: http://sqlfiddle.com/#!3/44c84/8

Open in new window

0
Delta7428Author Commented:
The results in my post are correct according to the data in my datatable.

In the sample data table, I could not show a previous record for the first record.  This is why branchid 100 has only 2 records in the results.  

For clarity in the example, I probably should have shown a 3rd record in the results set for branchid100 with a null in DaysPastDueAtRenewal.

In reality my query will always pull a previous record.


Results with no previous record for record 1 would look like this:

LoanDate      BranchID      NamesId      DaysPastDue                  
4/5/2013      100      1      null
4/5/2013      100      1      10
8/2/2013      100      1      15
2/1/2013      200      2      3
5/6/2013      200      2      22
9/7/2013      200      2      12
3/1/2013      300      3      6
6/1/2013      300      3      4
9/20/2013 300      3      31
0
Delta7428Author Commented:
Correction: Logically my query could not pull a previous record for record 1.
0
PortletPaulfreelancerCommented:
originally:
LoanDate           BranchId            NamesId      DaysPastDue
1/1/2013           100                  1            10

is now:
LoanDate      BranchID      NamesId      DaysPastDue                  
4/5/2013      100      1     null
is this date correct?
(It is no longer "the oldest")


and the expected results wanted are as per your question
0
Delta7428Author Commented:
I don't think you understand.  The value in DaysPastDueAtRenewal in the results will be the previous record's DaysPastDue from the DataTable.  

I am showing the results accurately according to that.

 I did not reshow the datatable rows in my last post.  Just the results showing the first record having a null value because there is no previous record in my sample table.  Record 1 cannot have a previous record.

The first record will show a null in DaysPastDue at renewal in the result set, as there is no previous record.

DataTable:
LoanDate           BranchId            NamesId      DaysPastDue
1/1/2013           100                  1            10
4/5/2013           100                  1            15
8/2/2013           100                  1            3
2/1/2013          200                  2            22
5/6/2013           200                  2            12
9/7/2013           200                  2            6
3/1/2013           300                  3            4
6/1/2013           300                  3            31
9/20/2013           300                  3            25

Results:
LoanDate      BranchID      NamesId      DaysPastDue AtRenewal                
4/5/2013             100              1              null
4/5/2013             100              1              10
8/2/2013             100              1              15
2/1/2013             200              2               3
5/6/2013             200              2              22
9/7/2013             200              2              12
3/1/2013            300              3               6
6/1/2013            300              3               4
9/20/2013        300              3               31
0
PortletPaulfreelancerCommented:
thanks - clarified, much appreciated.
0
Brendt HessSenior DBACommented:
Since you are working on SQL 2000, you don't have the CTE or RowID that I made use of.  Well, se la vie!  Here's the method I would use in SQL 2000 to achieve the same results.

Since SQL 2000 did not have the concept of a Common Table Entity, the simplest way to do this would be with an actual temp table, although you could use the Contracts table alone (with an index on BranchID, NameID and LoanDate).  I will show both methods.

Here is the temp table version:

CREATE TABLE #Base (
	LoadDate datetime NOT NULL,
	BranchID int NOT NULL,
	NamesID int NOT NULL,
	DaysPastDueAtRenewal int,
	RowID int IDENTITY
	)
CREATE INDEX ix1 ON #Base BranchID, NamesID, RowID

INSERT INTO #Base (
	LoadDate,
	BranchID,
	NamesID,
	DaysPastDueAtRenewal,
	RowID
	)
SELECT 
	LoanDate, 
	BranchID, 
	NamesID, 
	DaysPastDueAtRenewal
FROM Contracts 

SELECT 
	b.LoanDate,
	b.BranchID,
	b.NamesID,
	bp.DaysPastDueAtRenewal
FROM #Base AS b
INNER JOIN #Base AS bp
	ON b.branchID = bp.BranchID
	AND b.NamesID = bp.NamesID
	AND bp.RowID = b.RowID - 1

Open in new window

And here is a version that works with the Contracts table directly:


SELECT 
	b.LoanDate,
	b.BranchID,
	b.NamesID,
	bp.DaysPastDueAtRenewal
FROM Contracts AS b
INNER JOIN Contracts AS bp
	ON b.BranchID = bp.branchID
	AND b.NamesID = bp.NamesID
	AND bp.LoanDate = (
		SELECT MAX(LoanDate)
		FROM Contracts AS c
		WHERE c.BranchID = b.BranchID
			AND c.NamesID = b.NamesID
			AND c.LoanDate < b.LoanDate
		)
	

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.