Customers last order date

  • 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

  • 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/

  • 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

  • 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/

  • 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

  • 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