[Yi fei] the latest price for a specified condition

[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