Solved

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

Posted on 2011-09-22
15
436 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
  • 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now