asked on

SQL Query to obtain last ordered quantiy for an item for a specific customer

I hope this will be an easy one for an SQL expert as I am somewhere between a comple idiot and novice with sql.

I have a need to get the last quantity ordered and its unit price for a current ordered part for a list (daily orders) by customer.
I have two tables;
CUSTOMER_ORDER (alias CO)
CUST_ORDER_LINE (alias COL, COL2)

in CO i have the following fields
CUSTOMER_ID
ORDER_DATE

in COL and COL2 (same table) i have the following fields
CUST_ORDER_ID
ORDER_QTY
UNIT_PRICE
PART_ID

I have constructed a subquery to include in my primary query.

My primary query works correctly to obtain each order line of a current order.

I am adding my subquery to my primary query as a field query as follows:

SELECT blah, blah, blah, blah, {subquery} from TABLE ....

the subquery produces the following error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Here is the subquery:

(SELECT COL.PART_ID, COL.UNIT_PRICE FROM CUSTOMER_ORDER AS CO INNER JOIN
CUST_ORDER_LINE AS COL ON CO.ID = COL.CUST_ORDER_ID INNER JOIN
(SELECT COL2.PART_ID, MAX(COL2.ORDER_QTY) AS LOQ, MAX(CO2.ORDER_DATE) AS MaxDate
FROM CUSTOMER_ORDER AS CO2 INNER JOIN
CUST_ORDER_LINE AS COL2 ON CO2.ID = COL2.CUST_ORDER_ID
WHERE (CO2.ORDER_DATE < @BEGINBOOKINGSDATE - 1825)
GROUP BY COL2.PART_ID) AS Temp ON COL.PART_ID = Temp.PART_ID AND CO.ORDER_DATE = Temp.MaxDate) AS LOQ

I have viewed all 363 searched for existing answere to no avail.
My poor brain cell (thats right, only 1 left) is about to go on permanent vacation.

Can someone fix this and post a cut-n-paste for me to use so I can get on with my life.

I will review the answer later to learn what I screwed up.

Thanks.

As a PS. this is being used in an SSRS report.

tim_cs

You can't have more than one column or row returned when using a subquery like that. You will have to use a join to get this to work.

tim_cs

Can you post your primary query you are using?

Briancslnet

ASKER

sure.. here is the query

SELECT CUSTOMER_ORDER.ID, CUSTOMER_ORDER.CUSTOMER_ID, CUSTOMER_ORDER.CUSTOMER_PO_REF, CUSTOMER_ORDER.ORDER_DATE,
CUST_ORDER_LINE.LINE_NO, CUST_ORDER_LINE.PART_ID, CUST_ORDER_LINE.ORDER_QTY, CUST_ORDER_LINE.DESIRED_SHIP_DATE,
CUST_ORDER_LINE.UNIT_PRICE, CUST_ORDER_LINE.MISC_REFERENCE, CUST_ORDER_LINE.SELLING_UM, CUSTOMER.NAME, CUSTOMER.USER_4,
PART.UNIT_MATERIAL_COST, PART.UNIT_LABOR_COST, PART.UNIT_BURDEN_COST, PART.UNIT_SERVICE_COST, CUST_ORDER_LINE.SERVICE_CHARGE_ID,
SERVICE_CHARGE.DESCRIPTION, CUSTOMER_ORDER.USER_1, {subquery goes here} FROM CUSTOMER_ORDER AS CUSTOMER_ORDER INNER JOIN
CUST_ORDER_LINE AS CUST_ORDER_LINE ON CUSTOMER_ORDER.ID = CUST_ORDER_LINE.CUST_ORDER_ID INNER JOIN
CUSTOMER ON CUSTOMER_ORDER.CUSTOMER_ID = CUSTOMER.ID LEFT OUTER JOIN
PART ON CUST_ORDER_LINE.PART_ID = PART.ID LEFT OUTER JOIN
SERVICE_CHARGE ON CUST_ORDER_LINE.SERVICE_CHARGE_ID = SERVICE_CHARGE.ID
WHERE (CUSTOMER_ORDER.ORDER_DATE >= @BEGINBOOKINGSDATE) AND (CUSTOMER_ORDER.ORDER_DATE <= @ENDBOOKINGSDATE) AND
(CUSTOMER_ORDER.STATUS <> 'X') AND (CUSTOMER_ORDER.ID NOT LIKE '%-%')
ORDER BY CUSTOMER_ORDER.CUSTOMER_ID, CUSTOMER_ORDER.ID

i DID NOT INCLUDE THE SUBQUERY, JUST A PLACEHOLDER

ASKER CERTIFIED SOLUTION

tim_cs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

millsap_singer

You might look into using CROSS APPLY as your sub-query. Can return a distinct row or set of rows for each row returned from the parent data set and it allows you to get more than one column of data out of it.

SELECT CO.CustomerID
	, Line2.OrderDate
	, Line2.OrderQty
FROM CustomerOrder CO
	CROSS APPLY (
				SELECT MAX(COL2.Order_Date) AS OrderDate, MAX(COL2.Order_Qty) AS OrderQty
				FROM COL2
				WHERE COL2.CustomerID = CO.CustomerID
				) Line2

Open in new window

Briancslnet

ASKER

tim,

used your query vebatim, received the following error;
I tried something similar to this a couple of days ago and got this same error.

I am using SQL SERVER 2008 and working with Microsoft Visual Studio

Here is the error:

An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.

Briancslnet

ASKER

I am thinking maybe the "AND RN = 1" may be the culprit???

millsap_singer

On Tim's query, he does have a syntax error. It's the second line. Line 56 if you look at it on here.

AND (CUSTOMER_ORDER.STATUS <> 'X')
AND (CUSTOMER_ORDER)

tim_cs

Ooops, millsap_singer is right I had an extra AND line in there.

Briancslnet

ASKER

Ok, took out the offending 'AND (CUSTOMER_ORDER)'

Query now errors as:

"Unable to parse query text."

can't say I see anything wrong with the query, I will paste the exact query here with the change.

;WITH CTE AS (
SELECT
      CO.CUSTOMER_ID
      ,COL.PART_ID
      ,COL.UNIT_PRICE
      ,COL.ORDER_QTY
      ,ROW_NUMBER() OVER (Partition By CO.CUSTOMER_ID, COL.PART_ID ORDER BY CO.ORDER_DATE DESC) RN
FROM
      CUSTOMER_ORDER CO
      INNER JOIN CUST_ORDER_LINE COL
            ON CO.ID = COL.CUST_ORDER_ID
)
SELECT
      CUSTOMER_ORDER.ID,
      CUSTOMER_ORDER.CUSTOMER_ID,
      CUSTOMER_ORDER.CUSTOMER_PO_REF,
      CUSTOMER_ORDER.ORDER_DATE,
     CUST_ORDER_LINE.LINE_NO,
      CUST_ORDER_LINE.PART_ID,
      CUST_ORDER_LINE.ORDER_QTY,
      CUST_ORDER_LINE.DESIRED_SHIP_DATE,
CUST_ORDER_LINE.UNIT_PRICE,
      CUST_ORDER_LINE.MISC_REFERENCE,
      CUST_ORDER_LINE.SELLING_UM,
      CUSTOMER.NAME, CUSTOMER.USER_4,
     PART.UNIT_MATERIAL_COST,
      PART.UNIT_LABOR_COST,
      PART.UNIT_BURDEN_COST,
      PART.UNIT_SERVICE_COST,
      CUST_ORDER_LINE.SERVICE_CHARGE_ID,
      SERVICE_CHARGE.DESCRIPTION,
      CUSTOMER_ORDER.USER_1,
      CTE.ORDER_QTY,
      CTE.UNIT_PRICE
FROM
      CUSTOMER_ORDER AS CUSTOMER_ORDER
      INNER JOIN CUST_ORDER_LINE AS CUST_ORDER_LINE
            ON CUSTOMER_ORDER.ID = CUST_ORDER_LINE.CUST_ORDER_ID
      INNER JOIN CUSTOMER
            ON CUSTOMER_ORDER.CUSTOMER_ID = CUSTOMER.ID
      LEFT OUTER JOIN PART
            ON CUST_ORDER_LINE.PART_ID = PART.ID
      LEFT OUTER JOIN SERVICE_CHARGE
            ON CUST_ORDER_LINE.SERVICE_CHARGE_ID = SERVICE_CHARGE.ID
      LEFT JOIN CTE
            ON CUSTOMER_ORDER.CUSTOMER_ID = CTE.CUSTOMER_ID
            AND CUST_ORDER_LINE.PART_ID = CTE.PART_ID
            AND RN = 1
WHERE
      (CUSTOMER_ORDER.ORDER_DATE >= @BEGINBOOKINGSDATE)
      AND (CUSTOMER_ORDER.ORDER_DATE <= @ENDBOOKINGSDATE)
      AND (CUSTOMER_ORDER.STATUS <> 'X')
      AND (CUSTOMER_ORDER.ID NOT LIKE '%-%')
ORDER BY
      CUSTOMER_ORDER.CUSTOMER_ID,
      CUSTOMER_ORDER.ID

tim_cs

hmmmm, I'm not seeing anything and that error message is pretty useless. How are @BeginBookingsDate and @EndBookingsDate being passed/populated/created?

Briancslnet

ASKER

Both are entered Date/Time Parameters. Both work correctly with the primary query.
The only thing I'm unfamiliar with is the

",ROW_NUMBER() OVER (Partition By CO.CUSTOMER_ID" statment

again, other than that, everything looks ok at first glance.

tim_cs

hmmm, try seeing if you can run just the CTE part.

;WITH CTE AS (
SELECT
      CO.CUSTOMER_ID
      ,COL.PART_ID
      ,COL.UNIT_PRICE
      ,COL.ORDER_QTY
      ,ROW_NUMBER() OVER (Partition By CO.CUSTOMER_ID, COL.PART_ID ORDER BY CO.ORDER_DATE DESC) RN
FROM
      CUSTOMER_ORDER CO 
      INNER JOIN CUST_ORDER_LINE COL
            ON CO.ID = COL.CUST_ORDER_ID
)

SELECT * FROM CTE WHERE RN = 1

Open in new window

Briancslnet

ASKER

Thanks for your efforts Tim,
Lost my last brain cell over this and decided to try a different approach.
While it may be more elegant to create a single SQL query and data input I decided to simply use separate subreports to achieve the same end result.

I guess a simple statement can sum up this project.

"I can't stand SQL'.

Briancslnet

ASKER

thanks for putting me in the right direction.