I am trying to join 3 tables to get the Cost of the parts when bought, based on the Stock Level.
For example Part Number 34, we have 3 in stock (on the shelf) and we want to know the current value of these 3 parts when we purchased them, with the currency exchange rate of that day. This part is easy because the current stock level, is less than the last time we bought it. So it is a straight join on all 3 tables, where table 2, Adjustments is greater than 0.
For part number 35, this is more difficult. The stock level (25) is greater than the last time we purchased (20) these parts.
So for the first 20 parts, the cost is calculated based when we purchased those 20 parts. The next 5 parts we want to calculate the cost based on the time previous.
Table 1 (tbl_StockLevel)
PartNumber_PK StockLevel
34 3
35 25
36 244
PartNumber_PK : is a unique part number
Stocklevel : current stock count on the shelf
Table 2 (tbl_StockAdjustments)
PartNumber_FK Adjustments Currency Date
34 -5 0 2017-09-25
34 -2 0 2017-09-21
34 10 1.34 2017-09-20
34 -5 0 2017-09-19
35 -5 0 2017-09-25
35 -5 0 2017-09-25
35 20 1.34 2017-09-25
35 -15 0 2017-09-25
35 20 1.45 2017-09-20
35 -5 0 2017-09-19
35 -2 0 2017-09-19
Table 3 (tbl_Price)
PartNumber_FK Price Date
34 .25 2017-09-20
35 5.99 2017-09-22
35 5.99 2017-09-20
PartNumber_FK : Joins back to the parent table
Adjustments : Purchase price that date
Date : Date records changed
Joining table 1 and 3 is no issue.
My problem is I need to join table 1, and table 2, with table 3, but only on PartNumber, Date, and
only on table 2, then the total of the joining records less or equal to the stocklevel in table 1.
For example:
Part number 34
partNumber Stocklevel Adjustments Currency Cost costAdjustmentCurrency
34 3 10 1.34 $.25 $3.375
Because StockLevel is less than Adjustment (Where we purchased it) we do not need any more records.
part Number 35
partNumber Stocklevel Adjustments Currency Cost (StockLevel or adjustmens if lower)costCurrency
35 25 20 1.34 5.99 (205.991.34) 160.532
35 25 20 1.45 5.99 (5*5.991.45) 43.4275
SQL
SELECT
s.PartNumber_PK, s.StockLevel
FROM
tbl_StockLevel s
LEFT JOIN tbl_PRice p ON p.PartNumber_FK = s.PartNumber_PK AND s.DATE = p.DATE
LEFT JOIN tbl_StockAdjustments sa ON sa.p.PartNumber_FK = s.PartNumber_PK
AND (s.DATE = p.DATE AND sa.Adjustments > 0)
AND
sum of Adjustments Descending is less than StockLevel
I do not need records after I reach where SUM of POSITIVE Adjustments is greater than StockLevel.