set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go








create FUNCTION [dbo].[FunGoodLastFeeByDate] 
	(
	@Date char(8)='99/99/99'
	)
RETURNS TABLE
AS
	RETURN 
SELECT     derivedtbl_1.GoodSysCode, dbo.VWInvoiceDetailComplete.Fee AS LastFee, 
                      CASE WHEN NOT FK_AccountSysCode IS NULL THEN Title ELSE customername END AS LastSeller
FROM         dbo.VWInvoiceDetailComplete INNER JOIN
                          (SELECT     GoodSysCode, MAX(SysCode) AS LastSysCode
FROM         VWInvoiceDetailComplete AS VWInvoiceDetailComplete_1
WHERE     (Type = 2) AND (InvoiceDate <= @Date) AND (NOT (FK_DocsysCode IS NULL)) OR
                      (Type = 2) AND (InvoiceDate <= @Date) AND (Amani = 1)
GROUP BY GoodSysCode) AS derivedtbl_1 ON dbo.VWInvoiceDetailComplete.SysCode = derivedtbl_1.LastSysCode LEFT OUTER JOIN
                      dbo.CodeMNGUPTbl ON dbo.VWInvoiceDetailComplete.FK_AccountSysCode = dbo.CodeMNGUPTbl.AccountSysCode0