Solved

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

Posted on 2011-09-22
15
451 Views
Last Modified: 2012-05-12
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
Comment
Question by:Briancslnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 15

Expert Comment

by:tim_cs
ID: 36581130
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
 
LVL 15

Expert Comment

by:tim_cs
ID: 36581211
Can you post your primary query you are using?
0
 

Author Comment

by:Briancslnet
ID: 36581306
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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 15

Accepted Solution

by:
tim_cs earned 125 total points
ID: 36581585
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
 
LVL 1

Expert Comment

by:millsap_singer
ID: 36581819
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
 

Author Comment

by:Briancslnet
ID: 36581943
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
 

Author Comment

by:Briancslnet
ID: 36581952
I am thinking maybe the "AND RN = 1" may be the culprit???
0
 
LVL 1

Expert Comment

by:millsap_singer
ID: 36582038
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
 
LVL 15

Expert Comment

by:tim_cs
ID: 36582074
Ooops, millsap_singer is right I had an extra AND line in there.
0
 

Author Comment

by:Briancslnet
ID: 36582570
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
 
LVL 15

Expert Comment

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

Author Comment

by:Briancslnet
ID: 36583155
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
 
LVL 15

Expert Comment

by:tim_cs
ID: 36586548
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
 

Author Comment

by:Briancslnet
ID: 36814215
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
 

Author Closing Comment

by:Briancslnet
ID: 36814223
thanks for putting me in the right direction.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Text file into sql server 5 42
SQL Stored Proc - Performance Enhancement 15 80
SQL Server maintenance plan 8 54
MSSQL Convert Char to Date Time 5 12
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question