-- ===== 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 
        (
		idCustomer		INT,
		name			VARCHAR(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'

	201	3/21/2013 12:00:00 AM	200.0000	1	4	Able	Apple			able@email.com		Arlington	AL	555-555-5555	
	203	3/21/2013 12:00:00 AM	400.0000	2	4	Benny	Boone	Box Corp	benny@email.com		Boston		MA	555-555-5555	2/14/2013 12:00:00 AM
	205	3/21/2013 12:00:00 AM	600.0000	3	4	Carla	Clemens			carla@email.com		Colrain		CO	555-555-5555	
	207	3/21/2013 12:00:00 AM	800.0000	4	4	Dennis	Dods	D Corp		dods@email.com		Dallas		DE	555-555-5555	

	201	3/21/2013 12:00:00 AM	200.0000	1	4	Able	Apple			able@email.com		Arlington	AL	555-555-5555	1/12/2013 12:00:00 AM
	203	3/21/2013 12:00:00 AM	400.0000	2	4	Benny	Boone	Box Corp	benny@email.com		Boston		MA	555-555-5555	2/14/2013 12:00:00 AM
	205	3/21/2013 12:00:00 AM	600.0000	3	4	Carla	Clemens			carla@email.com		Colrain		CO	555-555-5555	12/16/2012 12:00:00 AM
	207	3/21/2013 12:00:00 AM	800.0000	4	4	Dennis	Dods	D Corp		dods@email.com		Dallas		DE	555-555-5555	11/18/2012 12:00:00 AM

declare @Date datetime = '20130321'

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, (select max(o2.orderDate) from #mytable_orders o2 where o2.idCustomer = o.idCustomer and o2.OrderDate < @Date) as last_order_date
from
#mytable_orders o
LEFT JOIN #mytable_customers c ON o.idCustomer = c.idCustomer
where
o.orderDate = @Date