[Yi fei] the latest price for a specified condition
Requirements:
Leading department |
Supplier |
Product NO. |
Name |
Specification |
Actual unit price |
Original unit price |
Incoming quantity |
Price Difference |
Amount difference |
Price ticket header Custom Field PURTL. UDF01 |
|
|
|
|
|
|
|
|
|
Description
1. Data of suppliers, product numbers, names, specifications, actual unit prices, and purchase quantities from purchase orders
2. original unit price = original unit price in the current validity period of the price list
3. Price Difference = actual unit price-original unit price
4. Amount Difference = price difference * purchase quantity
Example:
There was A purchase record in June March 16: supplier A, A purchased 500 million items, purchased 5 yuan per unit price, purchased amount = 500*5 = 2500 yuan
On the 15th day of February 13, there is A price ticket for supplier A. The unit price for A is 4 yuan, and the original unit price is 6 yuan. The effective date is February 15.
Result 1:
Supplier |
Product NO. |
Name |
Specification |
Actual unit price |
Original unit price |
Incoming quantity |
Price Difference |
Amount difference |
Jia |
A |
|
|
5 |
6 |
500 |
-1 |
-500 |
Condition 2:
On April 9, February 15, there was A price ticket for supplier A. The unit price of A was 4 yuan, and the original unit price was 6 yuan. The effective date was February 15.
On April 9, March 15, there was A price ticket for supplier A. The unit price of A was 5 yuan, and the original unit price was 4 yuan. The effective date was March 15.
Result 2:
Supplier |
Product NO. |
Name |
Specification |
Actual unit price |
Original unit price |
Incoming quantity |
Price Difference |
Amount difference |
Jia |
A |
|
|
5 |
4 |
500 |
1 |
500 |
Condition 3:
On May 25, February 15, there was A price ticket for supplier A. The unit price of A was 4 yuan, and the original unit price was 0 yuan. The effective date was February 15.
If the original price of the price list is 0, no result is output.
Condition 4:
If product A and product A have no nuclear price list, no result is output.
Create Function UF_GetLastPrice( @TL004 AS nvarchar(10), @date as nvarchar(8), @item as nvarchar(30))returns decimal(18,6)asbegin--set @TL004='09001'--set @date='20150116'--set @item='10102069620001';declare @price as decimal(18,6);with b as(select TM004,TM014,TM010,TL004 from PURTL inner join PURTM ON TL001=TM001 AND TL002=TM002where TM011='Y' AND TL004=@TL004 and TM014<=@date and TM004=@item)select @price=isnull(t.TM010,0) from (select b.TM004,b.TM010,ROW_NUMBER() over(partition by b.TM004 order by b.TM014 desc )rnfrom b ) twhere rn=1return @priceend