We help IT Professionals succeed at work.

Query Using Current and Prior Records

I am having to pull data from two tables from an import process to populate a destination table. In doing so, I also need data from the current and 'prior' record. Here is the layout:

tbl1:
VCode: VARCHAR(4)
Group: CHAR(1)
StatrtDate: DATETIME

tbl2:
VCode: VARCHAR(4)
Group: CHAR(1)
ChangeDate: DATETIME
ChangeReason: CHAR(1)

Sample data:
Tbl1:
'0123', 'A', '12/10/2007'

Tbl2:
'0123', 'B', '11/10/2010'
'0123', 'C', '10/04/2009'

For the destination table, I need to load:
'0123', 'C', '12/10/2007'
'0123', 'B', '10/04/2009'
'0123', 'A', '11/10/2010'

Tbl1 represents the current data and tbl2 is history. History in tbl2 'begins' with the StartDate in tbl1. The history table, tbl2, shows that on 11/10/2009, the Group code changed, but the value stored for Group is what it changed from, not to, thus I always have to look ahead a row (based on date) to get the proper value of Group.

All non-RBAR suggestions welcome.
Comment
Watch Question

Doug BishopDatabase Developer

Author

Commented:
Using SQL Server 2008 R2.

Also, to explain better what I need, when there is history for a VCode, then, starting with the earliest record (first ChangeDate), the data that needs to go into the destination table is:
1. StartDate from tbl1 as EffectiveDate and Group code from the earliest record.
2. ChangeDate from the first (earliest) record and Group code from the next-to-the-earliest record.
3. ChangeDate from the next-to-the-earliest record and Group code from the next-to-the-next-to-the-earliest record.
n. ChangeDate from the latest record and Group code from tbl1.
Commented:
Hi dbbishop,

How about this:
;WITH r 
AS 
(
	SELECT VCode, StartDate, NULL AS [Group] FROM Tbl1 --There is no value before insert
	UNION
	SELECT VCode, GETDATE(), [Group] FROM Tbl1 --Change history
	UNION
	SELECT VCode, ChangeDate, [Group] FROM Tbl2 --Current value
)
SELECT r1.VCOde, r2.[Group], r1.StartDate
FROM r AS r1 
	INNER JOIN r AS r2 
		ON r1.VCode = r2.VCOde 
			AND r2.StartDate = (SELECT TOP 1 StartDate FROM r WHERE VCode = r1.VCode AND StartDate > r1.StartDate ORDER BY StartDate)

Open in new window

Doug BishopDatabase Developer

Author

Commented:
Rimvis:
This seems to work with one caveat; the supplied history table contains values of VCode where there are duplicate ChangeDates. For example, on 1/12/2010, for VCode 'AAAA' there can be two rows, one with a Group value of 'A' and one with a value of 'B'. I believe this to be an error in data entry and am going to have the client clean up this data.
Thank you for your solution.
Doug BishopDatabase Developer

Author

Commented:
Had current and history turned around in the comments, but I figured if I couldn't figure that out then I shouldn't be a programmer :-) Thanks again.

Commented:
>>Had current and history turned around in the comment
Yes, sorry about that