Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Join tables with differing values

Posted on 2014-03-17
Medium Priority
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 49

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 49

Accepted Solution

PortletPaul earned 2000 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 |

see: http://sqlfiddle.com/#!3/b7567/8

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

618 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