• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 627
  • Last Modified:

SQL Query, last price paid paid by customers

I need to know the last price paid by customers for every specific PART_ID  that they ordered in the last 365 days.  Below are the 2 tables where I get the data from.

TBL :customer_order
ID
order_date
sales_rep_ID

TBL: customer_order_line
customer_order_ID
line_no
part_ID
unit_price
0
gringotani
Asked:
gringotani
  • 6
  • 5
  • 3
  • +2
1 Solution
 
Leigh PurvisDatabase DeveloperCommented:
Do you not have a Customers table?  And a Customer Foreign Key in your Order table?
0
 
Leigh PurvisDatabase DeveloperCommented:
Or do you not care about the customers - just the data of the Parts (rather than per customer)?

Does your [customer_order_line] table at least have a Primary Key of it's own?
Is that line_no?  Is it an ascending (auto) number?
0
 
gringotaniAuthor Commented:
Customer_order_line table does have the primairy key line #.  I dont see how that is relevenat?  Here I just want to kwo tha last  price a customer paid for a specific PART_ID.  SO if a customer ordered the same pn on 3 different dates, I just want to know the last price from th elast customer order
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Leigh PurvisDatabase DeveloperCommented:
SELECT COL.[part_ID], COL.[unit_price]
FROM ([customer_order] CO
    INNER JOIN [customer_order_line] COL ON CO.ID = COL.[customer_order_ID])
        INNER JOIN
        (SELECT COL2.[part_ID], Max([order_date]) AS MaxDate FROM [customer_order] CO2 INNER JOIN [customer_order_line] COL2 ON CO2.ID = COL2.[customer_order_ID] WHERE CO2.[order_date] >= Date() - 365 GROUP BY COL2.[part_ID]) Temp
        ON COL.[part_ID] = Temp.[part_ID] AND CO.[order_date] = Temp.[MaxDate]
0
 
GRayLCommented:
Try this:

SELECT a.ID, b.Part_id, Last(b.unit_price) FROM customer_order AS a INNER JOIN customer_order_line AS b WHERE
a.order_date BETWEEN Date()-365 and Date()
GROUP BY a.ID b.Part_id;
0
 
gringotaniAuthor Commented:
I tried to redo ur query with my table names.  and got an error message. I raised the points of this question to 150
 SELECT SYSADM_CUSTOMER_ORDER.ID, SYSADM_CUSTOMER_ORDER_LINE.[PART_ID] Last(SYSADM_CUSTOMER_ORDER_LINE.[UNIT_PRICE] FROM SYSADM_CUSTOMER_ORDER AS a INNER JOIN SYSADM_CUSTOMER_ORDER_LINE AS SYSADM_CUSTOMER_ORDER_LINE WHERE
SYSADM_CUSTOMER_ORDER.ORDER_DATE BETWEEN Date()-365 and Date()
GROUP BY SYSADM_CUSTOMER_ORDER.ID SYSADM_CUSTOMER_ORDER_LINE.PART_ID;

error message:
Syntax error (missing operator) in query expression 'SYSADM_CUSTOMER_ORDER_LINE.[PART_ID]
LAST(SYSADM_CUSTOMER_ORDER_LINE/[UNIT_PRICE] FROM SYSADM_CUSTOMER_ORDER AS a INNER JOIN SYSADM_CUSOTMER_ORDER_LINE AS _CUSOTMER_ORDER_LINE WHERE SYSADM_CUSOTMER_ORDER.ORDER_DATE BETWEEN datre(-365 and date ()gr',
0
 
dqmqCommented:
Don't know if it gives the right answer, but this fixes several syntax problems and adds the necessary order by clause so that "Last" is meaningful:

SELECT line.[PART_ID], Last(line.[UNIT_PRICE])
FROM SYSADM_CUSTOMER_ORDER AS order INNER JOIN SYSADM_CUSTOMER_ORDER_LINE AS line
 on order.ID = line.ID
WHERE order.ORDER_DATE > Date()-365
GROUP BY  line.PART_ID
order by order_date desc
0
 
dqmqCommented:
Slight correction on one of the column names:

SELECT line.[PART_ID], Last(line.[UNIT_PRICE])
FROM SYSADM_CUSTOMER_ORDER AS order INNER JOIN SYSADM_CUSTOMER_ORDER_LINE AS line
 on order.ID = line.customer_order_ID
WHERE order.ORDER_DATE > Date()-365
GROUP BY  line.PART_ID
order by order_date desc

But you said>
Customer_order_line table does have the primairy key line #.  I dont see how that is relevenat?  Here I just want to kwo tha last  price a customer paid for a specific PART_ID.  SO if a customer ordered the same pn on 3 different dates, I just want to know the last price from th elast customer order

The problem is that the above SQL and all other suggested SQL works across customers.  In other words, they try to give the last price paid by all customers.  To get an answer customer-by-customer, then we need a customer ID in the order table. Thus, LPurvis's first reply:

      Do you not have a Customers table?  And a Customer Foreign Key in your Order table?


0
 
gringotaniAuthor Commented:
I am getting the error message "SYNTAX ERROR IN FROM CLAUSE".   and the word "order" is highlighted after I close the error message dialog box  (see the arrow)

                                                                l
                                                                l
                                                                v
....FROM SYSADM_CUSTOMER_ORDER AS order INNER JOIN SYSADM_CUSTOMER_ORDER_LINE AS  ...
0
 
Leigh PurvisDatabase DeveloperCommented:
Have you even _tried_ my suggestion??
0
 
harfangCommented:
Hello gringotani

ORDER is a reserved word. You should choose another alias.

SELECT
    SCOL.PART_ID,
    Last(SCOL.UNIT_PRICE) AS ANY_PRICE
FROM SYSADM_CUSTOMER_ORDER AS SCO
    INNER JOIN SYSADM_CUSTOMER_ORDER_LINE AS SCOL
    ON SCO.ID = SCOL.CUSTOMER_ORDER_ID
WHERE SCO.ORDER_DATE BETWEEN Date()-365 and Date()
GROUP BY SCOL.PART_ID;

However, you cannot add an "ORDER BY" clause on the date, which would – if it were allowed – make this query meaningful. As it stands, the "Last(unit_price)" will not return the "latest" unit price, but the last in any arbitrary sort order.

At first sight, this query could in fact produce the same result as Leigh's exact answer {http:#16722672}, but that would be sheer luck, depending on the order in which the data has been entered, the choice of key fields, the last database compact, and some other factors, among which the answers to:
- Are several orders allowed on the same date?
- Are several customer_order_line's allowed for the same part_id?
Of course, Leigh's answer addresses these questions.

Finally, the "by customers" element of the requisite has been dropped, lacking any customer-related fields.

(°v°)
0
 
gringotaniAuthor Commented:
I am getting the following error message:  
"microsoft jet database engine cant find the input table or query 'SYSADM_customer_order_line'.  Make sure it exists and that it is spelled correctly"

plz help.  I am clueless
0
 
harfangCommented:
gringotani,

Do you have a "table or query [called] 'SYSADM_customer_order_line'"?

The name is from your own comment:
{http:#16722909} "I tried to redo ur query with my table names..."

(°v°)
0
 
harfangCommented:
so be it, although it should have been LPurvis' {http:#16722672}, imho
good luck...
(°v°)
0
 
Leigh PurvisDatabase DeveloperCommented:
Hey Markus.
Not to worry (but thanks for the mention :-).  
Been an experience this one...

*Future readers of this Q*
You might want to pop and have a look at it - as a (hopefully) reasonably robust answer to the question.
0
 
gringotaniAuthor Commented:
 LPurvis,  I am sorry, I am very new to tis and barely know what I am doing. will gladly give u points, if u can tell me how.  Do u want me to post a null question 4u to answer?  
0
 
Leigh PurvisDatabase DeveloperCommented:
Hi gringotani
Like I said to Markus - not to worry.  I've no concerns about the points.
(I am curious though - did you just not see it, or the subsequent posts asking if you'd seen it? :-)

The PAQ is important, but if we just leave this (much as it was) pointing to the suggestion in question then future readers should find it and decide for themselves.

Have you been able to try it for yourself - and determine if it's effective?
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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