$5000 worth of Web report sharing

$5000 worth of Web report sharing

Chat with a friend, found that he recently made a great report, in his words, at least 5000RMB, I took to share with you, and common progress.

With friend A, that is, he recently received the company's finance minister's needs, through the procurement and Research and Development Department of the Plan, statistics of the various departments of the reduction of this information, and then if the finance Department of the little sister hand statistics, it is too difficult, if you help IT staff with reporting software finereport.

Friend A through careful analysis of the data source of the plan, think about the following logic:

① Nuclear price Single head table contains information: Price date, supplier, leading department

② Price list contains information: Product number, Unit price, original unit price, effective date, expiration date

③ Purchase incoming Goods single head information: the individual, document number, supplier, document date

④ Purchase Order single information: Product number, name, specifications, purchase price, tax rate

As a result, friend a thought how to get the difficulty is how to take the original unit price, the final report as follows:

The price of the original price logic is when the arrival of the single date in the price of the effective date and the expiry date when the corresponding price of the original price information.

Here's how to get the original unit price function:

ALTER Function [dbo]. [Uf_getlastprice] (@TL004 as nvarchar, @date as nvarchar (8), @item as nvarchar ()) returns decimal (18,6) asbegin--set @TL004 = ' 09001 '--set @date = ' 20150116 '--set @item = ' 10102069620001 ';d eclare @price as Decimal (18,6);d eclare @zdbm as nvarchar (20) ; with B as (select Tm004,tm014,tm018,tl004,purtl. UDF01 from Purtl inner joins Purtm on tl001=tm001 and Tl002=tm002where tm011= ' Y ' and [email protected]  and   tm014< ; [Email protected] and [email protected]) 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

So, it's done!

$5000 worth of Web report sharing