SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Customers last order date


Customers last order date

Author
Message
rehook2003
rehook2003
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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



Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59818 Visits: 17947
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 Modens 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)
rehook2003
rehook2003
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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



Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59818 Visits: 17947
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 Modens 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)
rehook2003
rehook2003
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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



Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59818 Visits: 17947
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 Modens 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search