Web report sharing worth RMB 5000 and web report worth RMB 5000

Web report sharing worth RMB 5000 and web report worth RMB 5000
Web report sharing worth RMB 5000

I chatted with a friend and found that he had made a great report recently. In his words, it should be at least RMB. I will share it with you and make progress together.

In the words of friend A, he has recently received A demand from the company's finance department. He needs to collect statistics on the cost reduction information of each department through the cost reduction plan of the sourcing and R & D departments, then IT would be too difficult for the finance department's sister-in-law to make statistics manually. For example, ask IT personnel to use the report software FineReport.

After careful analysis, friend A found out the data source of the Plan and thought about the following logic:

① Information contained in a single price list table: price date, supplier, and leading department

② Information contained in the Single Table of the price list: Product NO., unit price, original unit price, effective date, and expiration date

③ Purchase order ticket header information: Ticket, Document No., supplier, and document date

④ Purchase order single information: product number, name, specification, purchase unit price, Tax Rate

As A result, friend A thinks that the difficulty is how to get the original unit price. The final report is as follows:

The logic of original unit price acquisition is to capture the original unit price information of the corresponding ticket when the receipt date is between the validity date and expiration date of the ticket.

The following is a function for getting the original unit price:

ALTER Function [dbo].[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);declare @zdbm as nvarchar(20);with b as(select TM004,TM014,TM018,TL004,PURTL.UDF01 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.TM018,0),@zdbm=t.UDF01 from (select b.UDF01,b.TM004,b.TM018,ROW_NUMBER() over(partition by b.TM004 order by b.TM014 desc )rnfrom  b ) twhere rn=1return  @priceend

That's all!