Отфильтровать таблицу перед применением правого соединения НА 3-Й ТАБЛИЦЕ

Использование MSSQL: просто для уточнения

Таблица клиентов

CustomerNumber Name
===================

1              David
2              Thomas
3              Mangold    
4              Issac
------------------------------------------------------------

Таблица адресов клиентов

CustomerNumber State EffectiveDate
==================================

1              AL    01/01/2017
1              VA    06/01/2017
1              GA    02/01/2018 
1              FL    10/01/2018
2              TX    01/01/2017
3              MA    
4              IL    04/01/2015

Таблица SalesOrder

CUSTOMER ORDERNo OrderDate
========================

1        1000   03/01/2017
2        1001   10/10/2017
1        1002   11/01/2017
3        1003   12/01/2017
4        1004   01/01/2018
1        1005   02/01/2018
1        1006   01/01/2019

Мне нужно получить все заказы с информацией о клиенте и адресом клиента на дату заказа.

SELECT T1.ORDERNo, T1.ORDERDATE, T1.CUSTOMER, T2.NAME, T3.STATE     
FROM SALESORDER  T1,  CUSTOMER T2, CUSTOMERADDRESS T3 
RIGHT JOIN(
   SELECT CUSTOMER, MAX(EFFECTIVEDATE) FROM CUSTOMERADDRESS
   --WHERE EFFECTIVEDATE <= T1.ORDERDATE
   GROUP BY CUSTOMER)T4 
   ON T3.CUSTOMER = T4.CUSTOMER AND T3.EFFECTIVEDATE=T4.EFFECTIVEDATE
WHERE T1.CUSTOMER = T2.CUSTOMERNUMBER
AND T1.CUSTOMER = T3.CUSTOMERNUMBER

Хотите посмотреть, как выполнить сравнение в соединении, где я сравниваю первую таблицу с 3-й таблицей в соединении. см. закомментированный код --WHERE EFFECTIVEDATE <= T1.ORDERDATE

Если я удалю закомментированный код, таблица в объединении не сможет ссылаться на внешнюю таблицу.

Ожидаемый результат:

 CUSTOMER ORDERNo       OrderDate     CustomerName State
 =============================================

 1        1000          03/01/2017    David        AL
 2        1001          10/10/2017    Thomas       TX
 1        1002          11/01/2017    David        VA
 3        1003          12/01/2017    Mangold      MA
 4        1004          01/01/2018    Issac        IL
 1        1005          02/01/2018    David        GA
 1        1006          01/01/2019    David        FL

Таблицы в скрипте sql http://sqlfiddle.com/#!18/9eecb:

            CREATE TABLE Customer
                ('CustomerNumber' int, 'CustomerName' varchar(30))
            ;

            INSERT INTO Customer
                ('CustomerNumber', 'CustomerName')
            VALUES
                (1, 'David'),
                (2, 'Thomas'),
                (3, 'Mangold'),
                (4, 'Issac')
            ;

            CREATE TABLE CustomerAddress
                ('CustomerNumber' int, 'State' varchar(2), 'EffectiveDate' date)
            ;

            INSERT INTO CustomerAddress
                ('CustomerNumber', 'State', 'EffectiveDate')
            VALUES
                (1, 'AL', 01/01/2017),
                (1, 'VA', 06/01/2017),
                (1, 'GA', 02/01/2018), 
                (1, 'FL', 10/01/2018),
                (2, 'TX', 01/01/2017),
                (3, 'MA',),   
                (4, 'IL', 04/01/2015)
            ;

            CREATE TABLE SalesOrder 
                ('CUSTOMER' int, 'ORDERNO' int,  'OrderDate' Date)
            ;

            INSERT INTO SalesOrder
                ('CUSTOMER', 'ORDERNO', 'OrderDate')
            VALUES
                (1,        1000,   03/01/2017),
                (2,        1001,   10/10/2017),
                (1,        1002,   11/01/2017),
                (3,        1003,   12/01/2017),
                (4,        1004,   01/01/2018),
                (1,        1005,   02/01/2018),
                (1,        1006,   01/01/2019)
            ;

            CREATE TABLE CustomerAddress
                (`CustomerNumber` int, 'State' varchar(2), `EffectiveDate` date)
            ;

            INSERT INTO CustomerAddress
                (`CustomerNumber`, `State`, 'EffectiveDate')
            VALUES
                (1, 'AL', 01/01/2017),
                (1, 'VA', 06/01/2017),
                (1, 'GA', 02/01/2018), 
                (1, 'FL', 10/01/2018),
                (2, 'TX', 01/01/2017),
                (3, 'MA',),   
                (4, 'IL', 04/01/2015)
            ;

            CREATE TABLE SalesOrder 
                (`CUSTOMER` int, 'ORDERNO' int,  `OrderDate` Date)
            ;

            INSERT INTO SalesOrder
                (`CUSTOMER `, `ORDERNO`, 'OrderDate')
            VALUES
                (1,        1000,   03/01/2017),
                (2,        1001,   10/10/2017),
                (1,        1002,   11/01/2017),
                (3,        1003,   12/01/2017),
                (4,        1004,   01/01/2018),
                (1,        1005,   02/01/2018),
                (1,        1006,   01/01/2019)
            ;

'версия SQL-сервера'

            CREATE TABLE Customer
                (CustomerNumber int, CustomerName varchar(30))
            ;


            INSERT INTO Customer
                (CustomerNumber, CustomerName)
            VALUES
                (1, 'David'),
                (2, 'Thomas'),
                (3, 'Mangold'),
                (4, 'Issac');               
            ;

            CREATE TABLE CustomerAddress
                (CustomerNumber int, State varchar(2), EffectiveDate date)
            ;

            INSERT INTO CustomerAddress
                (CustomerNumber, State, EffectiveDate)
            VALUES
                (1, 'AL', '01/01/2017'),
                (1, 'VA', '06/01/2017'),
                (1, 'GA', '02/01/2018'), 
                (1, 'FL', '10/01/2018'),
                (2, 'TX', '01/01/2017'),                    
                (4, 'IL', '04/01/2015')
            ;

            INSERT INTO CustomerAddress
                (CustomerNumber, State)
            VALUES
                (3, 'MA' )
            ;

            CREATE TABLE SalesOrder 
                (CUSTOMER int, ORDERNO int,  OrderDate Date)
            ;

            INSERT INTO SalesOrder
                (CUSTOMER, ORDERNO, OrderDate)
            VALUES
                (1,        1000,   '03/01/2017'),
                (2,        1001,   '10/10/2017'),
                (1,        1002,   '11/01/2017'),
                (3,        1003,   '12/01/2017'),
                (4,        1004,   '01/01/2018'),
                (1,        1005,   '02/01/2018'),
                (1,        1006,   '01/01/2019')
            ;

Проблема: нужно выбрать все заказы на продажу и их имя клиента, а также адрес клиента. Важной и сложной частью является изменение адреса клиента в зависимости от даты заказа на продажу.

--ИЗМЕНЕННАЯ ВЕРСИЯ НЕПОЛНОГО ЗАПРОСА

        SELECT T1.ORDERNo, T1.ORDERDATE, T1.CUSTOMER, T2.CustomerName, T3.STATE     
        FROM   CUSTOMER T2, SALESORDER  T1 INNER JOIN CUSTOMERADDRESS T3 ON T1.CUSTOMER = T3.CUSTOMERNUMBER
        RIGHT JOIN(
           SELECT CustomerNumber, MAX(EFFECTIVEDATE) as EffectiveDate4 FROM CUSTOMERADDRESS 
           --WHERE EFFECTIVEDATE < T1.ORDERDATE  
           GROUP BY CustomerNumber 
           --HAVING EFFECTIVEDATE < T1.ORDERDATE
           ) T4
           ON T3.CustomerNumber = T4.CustomerNumber AND T3.EFFECTIVEDATE=T4.EffectiveDate4
        WHERE T1.CUSTOMER = T2.CUSTOMERNUMBER