Sean, Thanks for your reply and the article... Hopefully I've done it correctly

Here is the info to create the results

-- ===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable_orders','U') IS NOT NULL

DROP TABLE #mytable_orders

IF OBJECT_ID('TempDB..#mytable_customers','U') IS NOT NULL

DROP TABLE #mytable_customers

-- ===== Create the test table with

--/*

CREATE TABLE #mytable_orders

(

idOrderCustom VARCHAR(5),

orderDate DATETIME,

total MONEY,

idCustomer INT,

orderStatus INT

)

--*/

--/*

CREATE TABLE #mytable_customers

(

idCustomerINT,

nameVARCHAR(20),

lastName VARCHAR(20),

customerCompany VARCHAR(20),

email VARCHAR(20),

city VARCHAR(20),

stateCode VARCHAR(20),

phone VARCHAR(20)

)

--*/

--/*

-- ===== Setup any special required conditions especially where dates are concerned

SET DATEFORMAT DMY

--*/

--/*

-- ===== All Inserts into the IDENTITY column

--SET IDENTITY_INSERT #mytable_orders ON

--SET IDENTITY_INSERT #mytable_customers ON

--*/

--/*

-- ===== Insert the test data into the test table

INSERT INTO #mytable_orders

(idOrderCustom, orderDate, total, idCustomer, orderStatus)

SELECT '100','jan 12 2013 12:00AM',100.00,'1',4 UNION ALL

SELECT '201','mar 21 2013 12:00AM',200.00,'1',4 UNION ALL

SELECT '102','feb 14 2013 12:00AM',300.00,'2',4 UNION ALL

SELECT '203','mar 21 2013 12:00AM',400.00,'2',4 UNION ALL

SELECT '104','dec 16 2012 12:00AM',500.00,'3',4 UNION ALL

SELECT '205','mar 21 2013 12:00AM',600.00,'3',4 UNION ALL

SELECT '106','nov 18 2012 12:00AM',700.00,'4',4 UNION ALL

SELECT '207','mar 21 2013 12:00AM',800.00,'4',4

INSERT INTO #mytable_customers

(idCustomer, name, lastName, customerCompany, email, city, stateCode, phone)

SELECT '1','Able','Apple','','able@email.com','Arlington', 'AL', '555-555-5555' UNION ALL

SELECT '2','Benny', 'Boone','Box Corp','benny@email.com','Boston', 'MA', '555-555-5555' UNION ALL

SELECT '3','Carla', 'Clemens','','carla@email.com','Colrain', 'CO', '555-555-5555' UNION ALL

SELECT '4','Dennis', 'Dods','D Corp','dods@email.com','Dallas', 'DE', '555-555-5555'

--*/

--/*

-- ===== Set the identity insert back to normal

-- SET IDENTITY_INSERT #mytable_orders OFF

--SET IDENTITY_INSERT #mytable_customers OFF

--*/

--/*

--SELECT * FROM #mytable_orders

--SELECT * FROM #mytable_customers

--*/

select

o.idOrderCustom, o.orderDate, o.total, o.idCustomer, o.orderStatus,

c.name, c.lastName, c.customerCompany, c.email, c.city, c.stateCode,

c.phone, last_order_date

from

#mytable_orders o

LEFT JOIN #mytable_customers c ON o.idCustomer = c.idCustomer

LEFT JOIN

(

select top 1

orderDate as last_order_date,

idCustomer from #mytable_orders

where

orderDate < 'mar 21 2013 12:00AM'

AND

orderStatus = 4

order by orderDate desc

) l ON o.idCustomer = l.idCustomer

where

o.orderDate = 'mar 21 2013 12:00AM'

This is what it returns... look at the last column... empty

2013/21/2013 12:00:00 AM200.000014AbleAppleable@email.comArlingtonAL555-555-5555

2033/21/2013 12:00:00 AM400.000024BennyBooneBox Corpbenny@email.comBostonMA555-555-55552/14/2013 12:00:00 AM

2053/21/2013 12:00:00 AM600.000034CarlaClemenscarla@email.comColrainCO555-555-5555

2073/21/2013 12:00:00 AM800.000044DennisDodsD Corpdods@email.comDallasDE555-555-5555

This is what I'm trying to return... look at the last column

2013/21/2013 12:00:00 AM200.000014AbleAppleable@email.comArlingtonAL555-555-55551/12/2013 12:00:00 AM

2033/21/2013 12:00:00 AM400.000024BennyBooneBox Corpbenny@email.comBostonMA555-555-55552/14/2013 12:00:00 AM

2053/21/2013 12:00:00 AM600.000034CarlaClemenscarla@email.comColrainCO555-555-555512/16/2012 12:00:00 AM

2073/21/2013 12:00:00 AM800.000044DennisDodsD Corpdods@email.comDallasDE555-555-555511/18/2012 12:00:00 AM

Any help is appreciated!

Rick