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