22,207
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #T
(EFFECTDATE DATE,
PRICE DECIMAL(12,2))
INSERT INTO #T
SELECT '2019-03-01',8.9 UNION ALL
SELECT '2019-09-01',9 UNION ALL
SELECT '2019-11-01',9.1 UNION ALL
SELECT '2020-01-01',9.5 UNION ALL
SELECT '2020-03-01',9.2
DECLARE @BEGIN_DATE DATE
DECLARE @END_DATE DATE
SET @BEGIN_DATE='2019-01-01'
SET @END_DATE='2020-12-01'
;WITH CTE
AS
(SELECT DATEADD(MONTH,NUMBER,@BEGIN_DATE) AS SINGEL_MONTH
FROM MASTER.DBO.SPT_VALUES A
JOIN (SELECT DATEDIFF(MONTH,@BEGIN_DATE,@END_DATE) AS DATE_DIFF) AS B ON A.NUMBER<=B.DATE_DIFF
WHERE TYPE='P')
SELECT A.*,ISNULL(B.PRICE,ISNULL(C.MIN_PRICE,D.MAX_PRICE)) FROM CTE A
LEFT JOIN #T B ON A.SINGEL_MONTH=B.EFFECTDATE
OUTER APPLY (SELECT TOP 1 PRICE AS MIN_PRICE FROM #T WHERE EFFECTDATE<A.SINGEL_MONTH ORDER BY EFFECTDATE DESC) AS C
OUTER APPLY (SELECT TOP 1 PRICE AS MAX_PRICE FROM #T WHERE EFFECTDATE>A.SINGEL_MONTH ORDER BY EFFECTDATE) AS D
if object_id (N'Fn_GetMonthAsPrice') is not null
drop function [dbo].[Fn_GetMonthAsPrice]
go
create function [dbo].[Fn_GetAccountMonth](@Date varchar(30))
returns DECIMAL (10,2)
as
begin
DECLARE @WaterPrice DECIMAL (10,2)
SELECT @WaterPrice = wasteWaterPrice from tblBase_Wastewater_Price
WHERE @Date = convert(varchar(7),effectdate,120)
RETURN @WaterPrice
END
GO
基本就是这样写,要注意看下传入的字段,是date还是datetime,直接把它转换成varchar(7)即可进行匹配。