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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

861 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

23 Experts available now in Live!

Get 1:1 Help Now