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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 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