GET LATEST PRICE FROM DIFFERENT PRICE TABLE

emi_sastra
emi_sastra used Ask the Experts™
on
Hi,

I want to get latest price from different table.

TMBARANG is the Item Table.
TMHRGBAHANSUPP  is the price table.

BahanCode is the Item Code
SuppCode is the Supplier Code.

One Item Code could have :

1. More than one Supplier.
2. More than one price Date.

I try below code, but not success yet.

What's wrong with my code?

Thank you.


SELECT BahanCode, FullName,
        (SELECT ISNULL(A.BahanPrice, 0)
      FROM TMHRGBAHANSUPP AS B JOIN
               (SELECT MAX(B.PriceDate) AS MaxDate
             FROM TMHRGBAHANSUPP AS C) ON 
           B.BahanCode = C.BahanCode AND 
           B.SuppCode = C.SuppCode
      WHERE SuppCode = '0034' AND 
           C.BahanCode = A.BahanCode AND 
           B.PriceDate = C.MaxDate)) AS BahanPrice
FROM TMBARANG AS A

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
This should help you out:
SELECT BahanCode, FullName, BahanPrice
FROM (
SELECT t1.BahanCode, t1.FullName, ISNULL(t1.BahanPrice, 0) BahanPrice, 
row_number() over ( partition by t1.BahanCode order by t2.PriceDate desc) rnum
FROM TMBARANG t1, TMHRGBAHANSUPP t2
WHERE t1.BahanCode = t2.BahanCode
and t2.SuppCode = '0034' ) temp
WHERE rnum = 1

Open in new window

Author

Commented:
Hi rrjegan17,

I am sorry, how about using SQL 7, my data is in it.

Thank you.

Author

Commented:
I have tried in using SQL 2005.

SELECT     BarangCode, FullName, BahanPrice
FROM         (SELECT     t1.BarangCode, t1.FullName, ISNULL(t1.BahanPrice, 0) BahanPrice, row_number() OVER (partition BY t1.BarangCode
                       ORDER BY t2.PriceDate DESC) rnum
FROM         TMBARANG t1, TMHRGBAHANSUPP t2
WHERE     t1.BarangCode = t2.BahanCode AND t2.SuppCode = '0034') temp
WHERE     rnum = 1

Result : All BahanPrice = 0

Thank you.
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Result : All BahanPrice = 0

That means for the Max(t2.PriceDate) your corresponding BahanPrice is 0.
Kindly confirm whether you have price as 0 or valid values in those columns.

Author

Commented:
The problem is at:

ISNULL(t1.BahanPrice, 0), should be ISNULL(t2.BahanPrice, 0)

After I examine the query, I think it is not efficient since It read all data from TMHRGBAHANSUPP.

Actually what I need it just certain Item Code and Supplier.

Correct me if I am wrong?

Thank you.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> ISNULL(t1.BahanPrice, 0), should be ISNULL(t2.BahanPrice, 0)

Hope replacing this one fetches you the desired results.

>> Actually what I need it just certain Item Code and Supplier.

Then kindly include the required columns in the inner and outer SELECT queries.
As table structures are not provided, I wont be able to choose the columns right.

Hope this helps

Author

Commented:
Ok.

TMBARANG

1. BarangCode
2. FullName

TMHRGBAHANSUPP

1. BahanCode (equal to BarangCode)
2. SuppCode
3. PriceDate
4. BahanPrice.

Hope this help.

Thank you.

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Does this help:

Kindly remove unwanted columns in the Final Select statement.
SELECT BahanCode, FullName, SuppCode, PriceDate, BahanPrice
FROM (
SELECT t1.BahanCode, t1.FullName, t2.SuppCode, t2.PriceDate, ISNULL(t2.BahanPrice, 0) BahanPrice, 
row_number() over ( partition by t1.BahanCode order by t2.PriceDate desc) rnum
FROM TMBARANG t1, TMHRGBAHANSUPP t2
WHERE t1.BahanCode = t2.BahanCode
and t2.SuppCode = '0034' ) temp
WHERE rnum = 1

Open in new window

Author

Commented:
What is rnum for?

Thank you.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> row_number() over ( partition by t1.BahanCode order by t2.PriceDate desc) rnum

This is where I find the Price for the Most recent date (Max date) and that's achieved using Row_Number() over ( partition by xx order by xx)

Hope this clarifies

Author

Commented:
I have tried it using empty data of TMHRGBAHANSUPP.

It generated no result, but I still want result from it, but with zero price.
The BahanCode, FullName should be there at any condition.

That's why in my code at the question, I use subquery to do it.

How to make your code work with the condition above?

Thank you.
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Then it should be LEFT OUTER JOIN instead of INNER JOIN.

Hope this helps
SELECT BahanCode, FullName, SuppCode, PriceDate, BahanPrice
FROM (
SELECT t1.BahanCode, t1.FullName, t2.SuppCode, t2.PriceDate, ISNULL(t2.BahanPrice, 0) BahanPrice, 
row_number() over ( partition by t1.BahanCode order by t2.PriceDate desc) rnum
FROM TMBARANG t1 LEFT OUTER JOIN TMHRGBAHANSUPP t2 ON t1.BahanCode = t2.BahanCode
and t2.SuppCode = '0034' ) temp
WHERE rnum = 1

Open in new window

Author

Commented:
Yes, it works.

Thank you very much for your help.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome..
Glad to help you out.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial