Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Customers last order date Expand / Collapse
Author
Message
Posted Friday, March 22, 2013 12:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 4, 2013 12:06 PM
Points: 25, Visits: 20
Hi All,

I'm having a bit of trouble with the following query, I get the results I need with the exception of the "last_order_date" field... it is empty

What I'm trying to do is this...

Pull the records for the days orders and also return the most recent order for each customer (if they have one).

This is what I have... your help is greatly appreciated


select
o.idOrderCustom, o.orderDate, o.total, o.idCustomer,
c.name, c.lastName, c.customerCompany, c.email, c.city, c.stateCode,
c.phone, last_order_date
from
orders o
LEFT JOIN customers c ON o.idCustomer = c.idCustomer
LEFT JOIN
(
select top 1
orders.orderDate as last_order_date,
orders.idCustomer from orders
where
orders.orderDate < '3/21/2013'
AND
orders.orderStatus = 4
order by orders.orderDate desc
) l ON o.idCustomer = l.idCustomer
where
o.orderDate = '3/21/2013'

Thanks
Rick



Post #1434145
Posted Friday, March 22, 2013 7:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 13,098, Visits: 11,932
Please take a few minutes and read the article at the first link in my signature for best practices when posting questions. We need to see ddl, sample data and desired output.

I don't exactly what you are looking for but I am certain we can do this a little simpler.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1434281
Posted Friday, March 22, 2013 10:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 4, 2013 12:06 PM
Points: 25, Visits: 20
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
(
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'

This is what it returns... look at the last column... empty
	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


This is what I'm trying to return... look at the last column
	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

Any help is appreciated!

Rick



Post #1434382
Posted Friday, March 22, 2013 10:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 13,098, Visits: 11,932
Excellent Rick. That is exactly what we are looking for!!!

The reason your date column was null is because you did a left join but filtered the row out with the earlier date in your where clause. You can do this as a subquery another way and it will work fine. I changed up the hardcoded date to a variable for some additional testing.

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



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1434404
Posted Friday, March 22, 2013 11:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 4, 2013 12:06 PM
Points: 25, Visits: 20
Worked like a charm!!

I originally started with a sub query, but without success changed to the left join... I see now why it was failing.

Sean, Thanks for your help on this!! It'll also help with some other queries I'll be building here shortly



Post #1434421
Posted Friday, March 22, 2013 12:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 13,098, Visits: 11,932
rehook2003 (3/22/2013)
Worked like a charm!!

I originally started with a sub query, but without success changed to the left join... I see now why it was failing.

Sean, Thanks for your help on this!! It'll also help with some other queries I'll be building here shortly


You are welcome. Glad that worked for you and thanks for letting me know.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1434469
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse