Get Most Recent One Table; JOIN and WHERE On Other Table
-
(update: DDL is now posted lower in the thread.)
I apologize for the formatting, I've looked on the site to try to figure out how to post a table on the forums properly (If someone could give me a pointer in that direction, I'd appreciate it.)
I have a couple of tables and need to join them, but with a twist.
Table A has Student IDs, the Effective Date of the grade change, and the Grade they were
changed to ON that date:
IDEffDateGrade
6784432007-01-24 14:40:27.070Kindergarten - Half Day
6784432007-07-04 23:09:47.710Grade 1
6784432008-07-02 23:35:57.993Grade 2
6784432009-07-02 23:30:43.557Grade 3
7184662009-08-27 23:18:48.673Pre-Kindergarten
7184662010-07-09 23:18:52.717Kindergarten - Half Day
7184662010-08-02 23:14:53.147Pre-Kindergarten
7184662009-05-18 23:50:23.973Pre-Kindergarten
7184662009-07-02 23:27:47.673Kindergarten - Half Day
Table B has IDs and EventDates that occurred on a certain date:
IDEventDate
7184662010-11-16 00:00:00.000
7184662009-05-20 00:00:00.000
6784432007-12-07 00:00:00.000
What I need is the ID and EventDate from Table B, and the Grade from Table A, joining on ID, where the the EffDate on Table A is the most recent date but is not PAST the EventDate on Table B.
In other words, students get their grade changed on a yearly basis. When I look at an EventDate, I need to know what grade they were in at the time of that event -- so it will be the most recent grade change that occurred prior to the EventDate. Students may have multiple events, and we can assume all will have at least one Table A entry with a EffDate prior to any Table B EventDate entry.
In the case of the above tables, it would be something like:
IDEventDateGrade
6784432007-07-04Grade 1
7184662010-08-02Pre-Kindergarten
7184662009-05-18Pre-Kindergarten
I've spent a great deal of time on this, and sometimes almost seem to have it, but can't quite get it to work.
Thank you -- as a newbie, I certainly appreciate your help!
-
daniel.potter (1/27/2011)
I apologize for the formatting, I've looked on the site to try to figure out how to post a table on the forums properly (If someone could give me a pointer in that direction, I'd appreciate it.)Regarding this question I can certainly help 🙂
Look at the link in my signature on how to post questions. It will explain in detail how to post code on this site.
Good luck!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Allright, I took a try at your problem.
I haven't tested it yet, but when you post the DDL statements and the INSERTS statements for sample data, the statement will be easy to test.
SELECT
ID= temp2.ID
,EventDate= temp2.EventDate
,Grade= temp2.Grade
FROM
(SELECT
temp.ID
,temp.EventDate
,temp.Grade
,ROW_NUMBER() OVER (PARTITION BY temp.ID ORDER BY temp.EventDate DESC) rid
FROM
(SELECT B.ID, B.EventDate, A.Grade
FROMTable_A A
INNER JOIN
Table_B B
ONA.ID = B.ID
ANDA.EventDate <= B.EventDate
) temp
) temp2
WHERE temp2.rid = 1
The most inner query (temp) performs an inner join between the two tables and it rules out rows from table A who are more recent than the EventDate from table B.
The second inner query takes those results and adds a row_number for each ID, starting with the most recent date. (look up the row_number() function for more information). Finally, I only select the rows with row ID = 1, ergo the most recent date.
I don't know if it is the most elegant solution, but I think it will get the job done.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Hi, thank you for your help!
Here's the DDL, with some changes to (hopefully) make the data more intuitive:
/* If the test table already exists, drop it */
IF OBJECT_ID('TempDB..#GradeChange','U') IS NOT NULL
DROP TABLE #GradeChange;
GO
IF OBJECT_ID('TempDB..#EventOccurrence','U') IS NOT NULL
DROP TABLE #EventOccurrence;
GO
/* Create first temp table */
CREATE TABLE #GradeChange
(
ID varchar(6),
EffectiveDate datetime,
Grade varchar(50)
);
GO
/* Populate it */
INSERT INTO #GradeChange
(ID, EffectiveDate, Grade)
SELECT '678443','Jul 2 2009 11:30PM','Grade 3' UNION ALL
SELECT '678443','Jan 24 2007 2:40PM','Kindergarten - Half Day' UNION ALL
SELECT '678443','Jul 4 2007 11:09PM','Grade 1' UNION ALL
SELECT '678443','Jul 2 2008 11:35PM','Grade 2' UNION ALL
SELECT '718466','May 18 2009 11:50PM','Pre-Kindergarten' UNION ALL
SELECT '718466','Jul 2 2009 11:27PM','Kindergarten - Half Day' UNION ALL
SELECT '718466','Aug 27 2009 11:18PM','Pre-Kindergarten' UNION ALL
SELECT '718466','Jul 9 2010 11:18PM','Kindergarten - Half Day' UNION ALL
SELECT '718466','Aug 2 2010 11:14PM','Kindergarten';
GO
/* Create 2nd temp table */
CREATE TABLE #EventOccurrence
(
ID varchar(6),
EventDate datetime
);
GO
/* Populate it */
INSERT INTO #EventOccurrence
(ID, EventDate)
SELECT '718466','Nov 16 2010 12:00AM' UNION ALL
SELECT '718466','May 20 2009 12:00AM' UNION ALL
SELECT '678443','Dec 7 2007 12:00AM';
GO
So the two tables would look like this:
And the expected results would look like this:
I've tried the code you posted, with (I think) appropriate changes:
SELECT
ID = temp2.ID
,EventDate = temp2.EventDate
,Grade = temp2.Grade
FROM
(SELECT
temp.ID
,temp.EventDate
,temp.Grade
,ROW_NUMBER() OVER (PARTITION BY temp.ID ORDER BY temp.EventDate DESC) rid
FROM
(SELECT B.ID, B.EventDate, A.Grade
FROM #GradeChange A
INNER JOIN
#EventOccurrence B
ON A.ID = B.ID
AND A.EffectiveDate <= B.EventDate
) temp
) temp2
WHERE temp2.rid = 1
But for ID # 718466, I only get one line returned: 718466 / 2010-11-16 00:00:00.000 / Pre-Kindergarten
I'd expect two results for this student, one for each event, as in the results image above.
Thanks again!
-
I've found a solution that works:
select eo.ID, eo.EventDate, gc.Gradefrom #EventOccurrence eo
inner join #GradeChange gc
on eo.ID = gc.ID
and gc.EffectiveDate = (select max(gc.EffectiveDate)
from #GradeChange gc
where gc.ID = eo.ID
and gc.EffectiveDate <= eo.EventDate)
-
d.potter (1/28/2011)
I've found a solution that works:select eo.ID, eo.EventDate, gc.Gradefrom #EventOccurrence eo
inner join #GradeChange gc
on eo.ID = gc.ID
and gc.EffectiveDate = (select max(gc.EffectiveDate)
from #GradeChange gc
where gc.ID = eo.ID
and gc.EffectiveDate <= eo.EventDate)
Great. Good that you found the solution and thanks for posting it here.
I guess I didn't read your requirements too well, since I didn't return those two rows :S
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply