[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

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.
0
Briancslnet
Asked:
Briancslnet
  • 7
  • 6
  • 2
1 Solution
 
tim_csCommented:
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.  
0
 
tim_csCommented:
Can you post your primary query you are using?
0
 
BriancslnetAuthor Commented:
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
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
tim_csCommented:
Give this a try.
;WITH CTE AS (
SELECT
	CO.Customer_ID
	,COL.PartID
	,COL.Unit_Price
	,COL.Order_QTY
	,ROW_NUMBER() OVER (Partition By Co.Customer_ID, COL.PartID ORDER BY CO.Order_Date DESC) RN
FROM
	Customer_Order CO 
	INNER JOIN Cust_Order_line COL
		ON CO.ID = COL.Cust_OrderID

)


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) 
	AND (CUSTOMER_ORDER.ID NOT LIKE '%-%')
ORDER BY 
	CUSTOMER_ORDER.CUSTOMER_ID, 
	CUSTOMER_ORDER.ID

Open in new window

0
 
millsap_singerCommented:
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

0
 
BriancslnetAuthor Commented:
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'.
0
 
BriancslnetAuthor Commented:
I am thinking maybe the "AND RN = 1" may be the culprit???
0
 
millsap_singerCommented:
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)
0
 
tim_csCommented:
Ooops, millsap_singer is right I had an extra AND line in there.
0
 
BriancslnetAuthor Commented:
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
0
 
tim_csCommented:
hmmmm, I'm not seeing anything and that error message is pretty useless.  How are @BeginBookingsDate and @EndBookingsDate being passed/populated/created?  
0
 
BriancslnetAuthor Commented:
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.
0
 
tim_csCommented:
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

0
 
BriancslnetAuthor Commented:
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'.
0
 
BriancslnetAuthor Commented:
thanks for putting me in the right direction.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now