|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
November 3rd, 2009, 07:06 AM
|
|||
|
|||
|
Query - General - Best way to Join "Lineitems" & "SKU Price Hist" Tables?
I am having trouble getting my head around the join for two tables. One table has lineitems (what sku was ordered, and when), and the other has the price dimension data for SKUs, over time.
"LINEITEMS" - from orders, with a sku, qty, date. EG: Code:
LINE_DATE SKU QTY ---------- ------- ---- 2008-08-29 1000101 10 2007-05-15 1000102 4 2001-08-03 1000103 9 "SKU_PRICE" - with a sku and date. EG: Code:
SKU EFF_DATE UNIT_PRICE ----------- ---------- ---------- 1000101 2005-05-04 0.07276 1000101 2008-05-10 0.091 1000101 2009-01-29 0.08596 For this example, look at SKU 1000101. It was ordered on 2008-08-29. What is the best way to join the SKU_PRICE table to get the relevent price for that sku on the date it was ordered? I know that I should be joining on sku first, but then how to I set up the dates to get the correct join? In this example, the query should return the SKU_PRICE example row #2 above - with effective date 2008-05-10 at 9.1 cents per unit. Here is the shell query, I just need the date part. Code:
SELECT L.LINE_DATE, L.SKU, L.QTY, P.UNIT_PRICE FROM LINEITEMS L INNER JOIN SKU_PRICE P ON L.SKU = P.SKU AND ???????????????? Thanks in advance, G
|
|
#2
November 3rd, 2009, 07:25 AM
|
||||||
|
||||||
|
LINE_DATE and EFF_DATE are not same as per your examples!!
Just making a wild guess here Code:
SELECT
L.LINE_DATE,
L.SKU,
L.QTY,
P.UNIT_PRICE
FROM
LINEITEMS L INNER JOIN
SKU_PRICE P ON
L.SKU = P.SKU
WHERE L.LINE_DATE=P.EFF_DATE
|
|
#3
November 3rd, 2009, 07:44 AM
|
|||
|
|||
|
I know they aren't, because I can set a price effective today, and sell that sku today, tomorrow, the next day, etc...
All skus sold from today up to the day I set the next new price should be priced by eff_date of today. Thats why it's tricky. This is a COMMON database design so there has to be a way.
|
|
#4
November 3rd, 2009, 08:18 AM
|
||||||
|
||||||
|
Something on lines of
Code:
SELECT
L.LINE_DATE,
L.SKU,
L.QTY,
P.UNIT_PRICE
FROM
LINEITEMS L INNER JOIN
SKU_PRICE P ON
L.SKU = P.SKU
WHERE P.EFF_DATE<=L.LINE_DATE Order By P.EFF_DATE
|
|
#5
November 3rd, 2009, 08:21 AM
|
|||
|
|||
|
heh, still doesn't work. Of course, it should return 1 price for a given date.
In your example, if there were previous price changes, it would return a row for each of those.
|
|
#8
November 4th, 2009, 03:18 AM
|
||||||
|
||||||
|
What if we try it like this, as we just need 1 record, assuming database is MS SQL Server
Code:
SELECT TOP 1
L.LINE_DATE,
L.SKU,
L.QTY,
P.UNIT_PRICE
FROM
LINEITEMS L INNER JOIN
SKU_PRICE P ON
L.SKU = P.SKU
WHERE P.EFF_DATE<=L.LINE_DATE Order By P.EFF_DATE
|
|
| Viewing: ASP Free Forums > Database > SQL Development > Query - General - Best way to Join "Lineitems" & "SKU Price Hist" Tables? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
