March 22, 2013 at 12:35 am
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
March 22, 2013 at 7:57 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 22, 2013 at 10:27 am
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
March 22, 2013 at 10:58 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 22, 2013 at 11:13 am
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
March 22, 2013 at 12:28 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply