The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
PLUS it appears all 3 subqueries are actually the same thing (? is that right)No, they are different in this part: AND A.Satuan2 = B.SatuanCode
;WITH
ctePrices AS (
/* This CTE is used to calculate row_number() once only
that row_number() calculation provides "the most recent price"
*/
SELECT
B.SalesPrice
, B.BarangCode
, B.SatuanCode
, row_number() over (partition by B.BarangCode, B.SatuanCode order by B.PriceDate DESC) as row_ref
/* NB: row_ref = 1 is "the most recent price" as it is sorted in DESCending order*/
FROM TMPRICELIST AS B
WHERE B.PriceDate <- getdate()
)
SELECT
A.BarangCode
, A.FullName
, Satuan
, 1 AS Ratio1
, P1.SalesPrice AS SalesPriceSatuan
, Satuan1
, Ratio1
, P2.SalesPrice AS SalesPriceSatuan1
, Satuan2
, Ratio2
, P3.SalesPrice AS SalesPriceSatuan2
FROM TMBARANG AS A
LEFT JOIN ctePrices as P1 ON A.BarangCode = P1.BarangCode
AND A.Satuan = P1.SatuanCode /* NB link on Satuan */
AND P1.row_ref = 1
LEFT JOIN ctePrices as P2 ON A.BarangCode = P2.BarangCode
AND A.Satuan1 = P2.SatuanCode /* NB link on Satuan1 */
AND P1.row_ref = 1
LEFT JOIN ctePrices as P3 ON A.BarangCode = P3.BarangCode
AND A.Satuan2 = P3.SatuanCode /* NB link on Satuan2 */
AND P1.row_ref = 1
NB: LEFT JOINs have been used as these have the same behaviour as a subquery in the select clause. IF possible use INNER JOINS - but only you can make that choice.
THAT is an example of something to note in your code
I don't claim perfectionbelow are the compound corrections
;WITH
ctePrices AS (
/* This CTE is used to calculate row_number() once only
that row_number() calculation provides "the most recent price"
*/
SELECT
B.SalesPrice
, B.BarangCode
, B.SatuanCode
, row_number() over (partition by B.BarangCode, B.SatuanCode order by B.PriceDate DESC) as row_ref
/* NB: row_ref = 1 is "the most recent price" as it is sorted in DESCending order*/
FROM TMPRICELIST AS B
WHERE B.PriceDate <= getdate() -- correction
)
SELECT
A.BarangCode
, A.FullName
, Satuan
, 1 AS Ratio1
, P1.SalesPrice AS SalesPriceSatuan
, Satuan1
, Ratio1
, P2.SalesPrice AS SalesPriceSatuan1
, Satuan2
, Ratio2
, P3.SalesPrice AS SalesPriceSatuan2
FROM TMBARANG AS A
LEFT JOIN ctePrices as P1 ON A.BarangCode = P1.BarangCode
AND A.Satuan = P1.SatuanCode /* NB link on Satuan */
AND P1.row_ref = 1
LEFT JOIN ctePrices as P2 ON A.BarangCode = P2.BarangCode
AND A.Satuan1 = P2.SatuanCode /* NB link on Satuan1 */
AND P2.row_ref = 1 -- & correction here
LEFT JOIN ctePrices as P3 ON A.BarangCode = P3.BarangCode
AND A.Satuan2 = P3.SatuanCode /* NB link on Satuan2 */
AND P3.row_ref = 1 -- & correction here
SELECT
A.BarangCode
, A.FullName
, Satuan
, 1 AS Ratio1
, P1.SalesPrice AS SalesPriceSatuan
, Satuan1
, Ratio1
, P2.SalesPrice AS SalesPriceSatuan1
, Satuan2
, Ratio2
, P3.SalesPrice AS SalesPriceSatuan2
FROM TMBARANG AS A
LEFT JOIN (
SELECT
B.SalesPrice
, B.BarangCode
, B.SatuanCode
, row_number() over (partition by B.BarangCode, B.SatuanCode order by B.PriceDate DESC) as row_ref
/* NB: row_ref = 1 is "the most recent price" as it is sorted in DESCending order*/
FROM TMPRICELIST AS B
WHERE B.PriceDate <= getdate() -- correction
) as P1 ON A.BarangCode = P1.BarangCode
AND A.Satuan = P1.SatuanCode /* NB link on Satuan */
AND P1.row_ref = 1
LEFT JOIN (
SELECT
B.SalesPrice
, B.BarangCode
, B.SatuanCode
, row_number() over (partition by B.BarangCode, B.SatuanCode order by B.PriceDate DESC) as row_ref
/* NB: row_ref = 1 is "the most recent price" as it is sorted in DESCending order*/
FROM TMPRICELIST AS B
WHERE B.PriceDate <= getdate() -- correction
) as P2 ON A.BarangCode = P2.BarangCode
AND A.Satuan1 = P2.SatuanCode /* NB link on Satuan1 */
AND P2.row_ref = 1 -- & correction here
LEFT JOIN (
SELECT
B.SalesPrice
, B.BarangCode
, B.SatuanCode
, row_number() over (partition by B.BarangCode, B.SatuanCode order by B.PriceDate DESC) as row_ref
/* NB: row_ref = 1 is "the most recent price" as it is sorted in DESCending order*/
FROM TMPRICELIST AS B
WHERE B.PriceDate <= getdate() -- correction
) as P3 ON A.BarangCode = P3.BarangCode
AND A.Satuan2 = P3.SatuanCode /* NB link on Satuan2 */
AND P3.row_ref = 1 -- & correction here
Open in new window
Use just the dates:Open in new window
.You can try to convert the SalesPriceSatuan, SalesPriceSatuan1, SalesPriceSatuan2 calculations to cte, but you will not gain any performance improvements, IMHO.