Join tables with differing values

Posted on 2014-03-17
Last Modified: 2014-03-17
Hi experts,

I have three tables that I need to produce a meaningful dataset from.

Table T1 is the parent table with PK of P1 (integer)
Table T2 has child records with FK joining it to T1 using P1, the other meaningful column of a datetime datatype (say column D1)
Table T3 has another set of attributes of T1 that is unrelated data for T2, but also has a meaningful datetime colun (Say D2)

What I need to do is to Join T2 and T3 using the P1 common column and match data from T3 records to T2 data as per the following

Table T1

P1 Values

Table T2
P1         D1                       Other column
1           1-jan-2013                 v1
1            1-march-2013          v2
1           15-march-2013         v3
2            2-jan-2013                v64
2            3-feb-2013                v25
2            6-mar-2013              v23
2            6-apr-2013                v9
3           23-feb-2013               v6
3           26-apr-2013               v8
3           25-may-2013             v12

Table T3
P1             D2                         Attribute
1           1-jan-2013                 a1
1            12-feb-2013              a2
1            16-march-2013        a3
2           1-jan-2013                 a4
2            12-feb-2013              a5
2            15-march-2013        a6
3           1-jan-2013                  a7
3            14-feb-2013              a8
3            25-march-2013        a9

The resultant dataset needs to look like
P1             D1                    Other Column   Attribute
1           1-jan-2013                 v1                     a1
1            1-march-2013          v2                     a2
1           15-march-2013         v3                     a2
2            2-jan-2013                v64                   a4
2            3-feb-2013                v25                  a4
2            6-mar-2013              v23                   a5
2            6-apr-2013                v9                    a6
3           23-feb-2013               v6                   a8
3           26-apr-2013               v8                   a9
3           25-may-2013             v12                 a9

The date D2 in T3 should been seen as an "effective date" where data from T2 from that date forward attracts the attribute from that effective date until a new effective date applies. The latest D2 attribute applies for all records from that date.

I know I've done this before, but cannot for the life of me remember how I did it.

Question by:Kelvin Sparks
  • 2
  • 2
LVL 48

Expert Comment

ID: 39933632
What data types are the fields D1 and D2 please?

e.g. datetime, date, datetime2
LVL 22

Author Comment

by:Kelvin Sparks
ID: 39933643

LVL 48

Accepted Solution

PortletPaul earned 500 total points
ID: 39933647
    , convert(varchar, t2.d1 ,106) AS D1
    , t2.other
    , (
       SELECT top 1 attribute
       FROM t3
       WHERE t3.p1 = t1.p1
       AND t3.d2 <= t2.d1
       ORDER BY t3.d2 DESC
      ) AS attribute
LEFT JOIN t2 ON t1.p1 = t2.p1

produces this result from the sample data provided:
    | P1 |          D1 | OTHER | ATTRIBUTE |
    |  1 | 01 Jan 2013 |    v1 |        a1 |
    |  1 | 01 Mar 2013 |    v2 |        a2 |
    |  1 | 15 Mar 2013 |    v3 |        a2 |
    |  2 | 02 Jan 2013 |   v64 |        a4 |
    |  2 | 03 Feb 2013 |   v25 |        a4 |
    |  2 | 06 Mar 2013 |   v23 |        a5 |
    |  2 | 06 Apr 2013 |    v9 |        a6 |
    |  3 | 23 Feb 2013 |    v6 |        a8 |
    |  3 | 26 Apr 2013 |    v8 |        a9 |
    |  3 | 25 May 2013 |   v12 |        a9 |


Open in new window

LVL 22

Author Closing Comment

by:Kelvin Sparks
ID: 39935079
Thanks, just what I needed to refresh my memory.


Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now