Get the Last Order for each Customer using a join and where

Transact-SQL
1

I have a customer, order and Sales Agent table. I am trying to get the last order for customers using max date on the orders table. This is what I have so far.

SELECT ct.CSNAME, ct.CSADDR1, ct.CSCITY, ct.CSST, ct.CSZIP, ct.CSPHONE, ct.CSCODE, ot.LastOrderDate, SALESAGENT.SANAME
FROM CUSTOMER AS ct INNER JOIN
(SELECT CSCODE, MAX(ORDER_DATE) AS LastOrderDate
FROM ORDERS
GROUP BY CSCODE) AS ot
ON ct.CSCODE = ot.CSCODE INNER JOIN
SALESAGENT ON ct.SACODE = SALESAGENT.SACODE
WHERE (ot.LastOrderDate BETWEEN '01/01/2010' AND '08/01/2015') AND (ct.CSTYPE = 'A')
ORDER BY ct.CSNAMEindent preformatted text by 4 spaces

This query works great but I need to add a couple things. First in the Orders table there is a field called PLT_NO and I only want orders that have a '2' in that field. Secondly in the Orders table I want to select a field called TOTAL_AMT. So when the query selects the MaxDate record for each customer it would also pull the value of the TOTAL_AMT field for that record. I've tried in the query below but I get this error Column 'ORDERS.TOTAL_AMT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT ct.CSNAME, ct.CSADDR1, ct.CSCITY, ct.CSST, ct.CSZIP, ct.CSPHONE, ct.CSCODE, ot.LastOrderDate, ot.TOTAL_AMT, SALESAGENT.SANAME
FROM CUSTOMER AS ct INNER JOIN
(SELECT CSCODE, TOTAL_AMT, PLT_NO, MAX(ORDER_DATE) AS LastOrderDate
FROM ORDERS
GROUP BY CSCODE) AS ot ON ct.CSCODE = ot.CSCODE INNER JOIN
SALESAGENT ON ct.SACODE = SALESAGENT.SACODE
WHERE (ot.LastOrderDate BETWEEN '01/01/2010' AND '08/01/2015') AND (ct.CSTYPE = 'A') AND PLT_NO = '2'

I am at a complete loss as to how to get the Plt_No = 2 in the Where clause and the Total_Amt in the select for Orders. Any help would be much appreciated.

Thanks,
Stacy

2

Use a cross apply like shown below.

SELECT  ct.CSNAME ,
        ct.CSADDR1 ,
        ct.CSCITY ,
        ct.CSST ,
        ct.CSZIP ,
        ct.CSPHONE ,
        ct.CSCODE ,
        ot.LastOrderDate ,
        SALESAGENT.SANAME
FROM    CUSTOMER AS ct
		CROSS JOIN
		(
			SELECT TOP (1)
				o.CSCODE,
				o.ORDER_DATE AS LastOrderDate,
				o.TOTAL_AMT
			FROM
				ORDERS o
			WHERE
				ct.CSCODE = ot.CSCODE
				AND o.PLT_NO = 2
			ORDER BY
				ORDER_DATE DESC 
		) AS ot
        INNER JOIN SALESAGENT ON 
			ct.SACODE = SALESAGENT.SACODE
WHERE   ( ot.LastOrderDate BETWEEN '01/01/2010'
                           AND     '08/01/2015' )
        AND ( ct.CSTYPE = 'A' )
ORDER BY ct.CSNAME;

Couple of other things to note:

If our ORDER_DATE column has a time portion also to it, then you should use a strictly less than for the ending date. What I mean is, instead of

WHERE ( ot.LastOrderDate BETWEEN '01/01/2010' AND '08/01/2015' )

use

WHERE ( ot.LastOrderDate >= '20100101' AND ot.LastOrderDate < '20150802' )

Note that instead of August 1, 2015, I am using August 2, 2015, but with a strictly less than operator.

Also, specify the dates in the YYYYMMDD format. This is unambigous, and will be interpreted correctly by SQL Server regardless of locale settings. Otherwise, 8/1/2015 may be interpreted as August 1, 2015 or January 8, 2015 depending on which locale settings are in effect on the server.

You could put the date filter within the cross apply if you choose to.

3

Thanks JamesK for your quick knowledgeable response. I did not know to specify the date as YYYYMMDD.

The query is giving an error at the below location. Particularly at the ct.CSCODE, The error says "The multi-part identifier "ct.CSCODE" could not be bound."
WHERE
ct.CSCODE = ot.CSCODE
AND ot.PLT_NO = 2

I'm not sure what that means. CT.CSCODE is correct, why does it think it is not?

Thanks again,
Stacy

4

Ugh! My bad!!!

It should not be a CROSS JOIN, it should be CROSS APPLY. Sorry about that!!!

SALESAGENT.SANAME,
ot.TOTAL_AMT  ---- <<< YOU CAN ADD TOTAL_AMT to the select list if you need to.
FROM    CUSTOMER AS ct
		CROSS APPLY ----- <<<<< THIS SHOULD BE CROSS APPLY.
		(```
5

By the way, in YYYYMMDD format, you would specify August 1, 2015 as '20150801'. That is, four digit year, 2 digit month and then 2-digit day.

6

Thanks JamesK that did the trick. I've never done a cross apply query before. Need to do some reading on that.

I think my query is still not quite right. I think I am asking SQL to give me the MaxDate Order that falls within my date range, but what I really want is to get only the customers that haven't had an order since my date range and then display the last order that is within the date range. So if a customer ordered this year I would not want that record because their MaxDate would be this year. Does that make sense?

Stacy

7

If I understood you correctly, the following query should give you what you want. It is picking up all the latest orders for each customer from 1/1/2010 to 8/1/2015. Then, the NOT EXISTS clause removes those customers who had orders on or after 8/2/2015.

If a customer had no orders in the period 1/1/2010 to 8/1/2015 that customer will not be returned via this query even if they didn't place any orders on or after 8/2/2015

SELECT  ct.CSNAME ,
        ct.CSADDR1 ,
        ct.CSCITY ,
        ct.CSST ,
        ct.CSZIP ,
        ct.CSPHONE ,
        ct.CSCODE ,
        ot.LastOrderDate ,
        SALESAGENT.SANAME,
        ot.TOTAL_AMT
FROM    CUSTOMER AS ct
		CROSS APPLY
		(
			SELECT TOP (1)
				o.CSCODE,
				o.ORDER_DATE AS LastOrderDate,
				o.TOTAL_AMT
			FROM
				ORDERS o
			WHERE
				ct.CSCODE = ot.CSCODE
				AND o.PLT_NO = 2
				AND o.ORDER_DATE >= '20100101'
				AND o.ORDER_DATE < '20150802'
			ORDER BY
				ORDER_DATE DESC 
		) AS ot
        INNER JOIN SALESAGENT ON 
			ct.SACODE = SALESAGENT.SACODE
WHERE   ( ct.CSTYPE = 'A' )
		AND NOT EXISTS
		(
			SELECT *
			FROM ORDERS o2
			WHERE 
				o2.OSCODE = ct.OSCODE
				AND o2.ORDER_DATE >= '20150802'
		);