asked on

SQL join

Hi,

I am trying to do the following:

In table 1 my data is monthly:

KEY1      VALUE1      DATE1
1      A      4/1/2005
1      B      5/1/2005
1      C      6/1/2005
1      D      7/1/2005
1      E      8/1/2005
1      F      9/1/2006

In table 2 my data is quarterly
KEY2      VALUE2      DATE2
1      a      6/1/2005
1      b      9/1/2005
1      c      12/1/2005
1      d      3/1/2006

I am trying to merge the two on KEY1=KEY2 so that I get the following structure

KEY      VALUE1      VALUE2      DATE1      DATE2
1      A      a      6/1/2005      4/1/2005
1      B      a      6/1/2005      5/1/2005
1      C      a      6/1/2005      6/1/2005
1      D      b      9/1/2005      7/1/2005
1      E      b      9/1/2005      8/1/2005
1      F      b      9/1/2005      9/1/2006

I want to merge on the Key . Each date in table 2 should get merged with 3 dates in table 2: lag 0 month date,lag 1 months date,lag2 months date. The output should look like table3.

How can I join the table 1 and table 2 to get table 3?

Thanks

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kyle Abrahams, PMP

if you're always doing the end of the quarter (eg: 3, 6, 9, 12)
the following will work:

SELECT T1.KEY1, T1.VALUE1, T2.VALUE2, T1.DATE1, T2.DATE2
FROM Table1 AS T1
INNER JOIN Table2 AS T2
   ON datepart(year, t1.Date) = datepart(year, t2.date) AND
          datepart(month,t1.date) between datepart(month,t2.date) - 2 and datepart(month,t2.date)

Open in new window

logic:
the year must be the same
and the month must be 2 months before to the current month.

Vitor Montalvão

camper12, do you still need help with this question?

PortletPaul

SELECT
      m.KEY1
    , m.VALUE1
    , q.VALUE2
    , m.DATE1
    , q.DATE2
FROM Table1 AS m
      INNER JOIN Table2 AS q ON m.KEY1 = q.KEY2
                  AND m.DATE1 BETWEEN DATEADD(MONTH, -2, q.DATE2) AND q.DATE2
;

Open in new window

or maybe:

SELECT
      m.KEY1
    , m.VALUE1
    , q.VALUE2
    , m.DATE1
    , q.DATE2
FROM table1 AS m
      CROSS APPLY (
            SELECT TOP (1)
                  table2.VALUE2
                , table2.DATE2
            FROM table2
            WHERE m.KEY1 = table2.KEY2
                  AND m.DATE1 BETWEEN DATEADD(MONTH, -2, table2.DATE2) AND table2.DATE2
            ORDER BY
                  DATE2
      ) AS q
;

Open in new window