2 14 2016/10/1
2 49 2016/10/25
2 8 2017/2/10
2, 3 2017/3/1
2 5 2017/7/26
2 14 2017/8/2
Price table List: ID price EffectiveDate (effective date)
2, 80.47 2016/10/1
2, 81.27 2017/2/1
2, 81.00 2017/8/1
Now want to match the price list of the unit price to the sales list, find out the total price, (product a lot of more phyletic, the price is also very frequent change, here is cited the example of a code of 2)
Note the price effective date and the date of products sold:
The price should be 80.47 2016/10/1-2016/10/25 products;
2017/2/10-2017/7-26 products price should be 81.27;
2017/8/2 product price should be 81.
Is to make the sales list date greater than or equal to the maximum price list date when prices
See a volume problem is similar to this, then according to write, but ran out of 2017/3/1 2017/7/26 - this time the price of the products is null, don't know where there are problems
What a great god help to see see! Just learn SQL, find out problems,
(SELECT ID, Quantity, SalesDate FROM Sales) a
(SELECT ID, Price, EffectiveDate FROM Price) b
The SELECT a.S KU, a.Q uantity, a.S alesDate, p. rice
The FROM (SELECT ID, Price, EffectiveDate FROM Price) b
RIGHT JOIN (SELECT ID, Quantity, SalesDate FROM Sales) a
ON b.I D=Anderson D
And
B.E ffectiveDate=(select Max (b.E ffectiveDate) from (select ID, Price, EffectiveDate from Price) b
Where b.E ffectiveDate & lt;=a.S alesDate)
The ORDER BY a.S KU, a.S alesDate
CodePudding user response:
SELECT *
FROM the SALES of A
The OUTER APPLY (SELECT TOP 1 PRICE FROM PRICE WHERE EFFECTIVEDATE<=A.S ALESDATE AND ID=Anderson D ORDER BY EFFECTIVEDATE DESC) AS B
CodePudding user response:
DECLARE @ the sales TABLE (ID INT, Quantity INT, SalesDate DATE);
INSERT @ sales (ID, Quantity, SalesDate)
VALUES (2, 14, '2016/10/1'), (2, 49, '2016/10/25), (2, 8,' 2017/2/10),
(2, 3, '2017/3/1), (2, 5,' 2017/7/26 '), (2, 14, '2017/8/2');
DECLARE @ price TABLE (ID INT, price a DECIMAL (10, 2), EffectiveDate DATE);
INSERT the @ price (ID, price, EffectiveDate)
VALUES (2, 80.47, '2016/10/1), (2, 81.27,' 2017/2/1), (2, 81.00, '2017/8/1');
SELECT *,
(SELECT TOP (1) Price
The FROM @ price p
WHERE p.I AND D D=s.I s.S alesDate & gt;=p.E ffectiveDate
The ORDER BY p.E ffectiveDate DESC) AS Price
The FROM @ sales s;
CodePudding user response: