SQL 用函数从（单价表）中取最接近当前记录日期的单价

SQL 用函数从（单价表）中取最接近当前记录日期的单价

 sql语句：

SELECT      MID,  ProjectNumber, MaterialCode, MaterialName, MateriaModel, Unit, Quantity, 
                    dbo.G_ZmNoTaxPrice(CONVERT(varchar(100), BatchDate, 112), MaterialCode) AS Price, 
                    ROUND(Quantity * ISNULL(dbo.[G_YesTaxPrice](CONVERT(varchar(100), BatchDate, 112), MaterialCode), 0), 2) AS Money
              FROM   dbo.LldListZm

 函数：

USE [ToData]
GO
/****** Object:  UserDefinedFunction [dbo].[G_YesTaxPrice]    Script Date: 08/08/2016 15:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--求得最近日期的单价函数
create function [dbo].[G_YesTaxPrice](@mDt int ,@codeNo Varchar(200))
 returns  money 
as
begin
  declare @Price money
 SELECT TOP 1 @Price=OriginalYesTaxPrice FROM dbo.LldAllPrice where dbo.LldAllPrice.EAN=@codeNo ORDER BY  ABS
 ( CONVERT(varchar(100), dbo.LldAllPrice.OrderDate, 112) - @mDt)
  return  @Price
end

GO