[Yi fei] the latest price for a specified condition

Source: Internet
Author: User

[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

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.