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
Solved

Join tables with differing values

Posted on 2014-03-17
4
227 Views
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
1
2
3

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.

Kelvin
0
Comment
Question by:Kelvin Sparks
  • 2
  • 2
4 Comments
 
LVL 48

Expert Comment

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

e.g. datetime, date, datetime2
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 39933643
Datetime

Kelvin
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39933647
SELECT
      t1.p1
    , 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
FROM t1
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

0
 
LVL 22

Author Closing Comment

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

Kelvin
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 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