USE [MRCDMS]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION fnOLperformmenttest (
@fromdate date)
RETURNS @OLperformment TABLE
(
CustomerCD NVARCHAR(50),
TranAmount  DECIMAL(20,4)
)
AS
BEGIN
INSERT @OLperformment
SELECT	CASE
	---YTD Actual---
			WHEN tb1.OrderDate BETWEEN DATEADD(yy, DATEDIFF(yy, 0, @fromdate), 0) AND @fromdate
			THEN (SELECT tb1.CustomerCD,SUM(tb1.TranAmt)
			WHERE tb1.OrderDate
			BETWEEN DATEADD(yy, DATEDIFF(yy, 0, @fromdate), 0)  AND @fromdate
			GROUP BY tb1.CustomerCD)
	---Last YTD-----
			WHEN tb1.OrderDate BETWEEN DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,@fromdate),0))
			AND DATEADD(week, -52, @fromdate)
			THEN (SELECT tb1.CustomerCD,SUM(TranAmt)
			WHERE tb1.OrderDate BETWEEN DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,@fromdate),0))
			AND DATEADD(yy, -1, @fromdate)
			GROUP BY tb1.CustomerCD)
	---MTDActual---
			WHEN tb1.OrderDate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, @fromdate), 0) AND @fromdate
			THEN (SELECT tb1.CustomerCD,SUM(tb1.TranAmt)
			WHERE tb1.OrderDate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, @fromdate), 0) AND @fromdate
			GROUP BY tb1.CustomerCD)
			END
FROM dbo.TestOLPerforment AS tb1
RETURN;
END;
GO