Same query for Fiscal and Normal calendar

tblFiscalMapping

Assume that you have a mapping table between Fiscal and Normal calendar above.

Sometimes, you need to write one stored procedure with a param @IsFiscal to determine when to return order info on Fiscal year or Normal calendar year.

You can implement as the following

DECLARE @StartMonth SMALLDATETIME;   
DECLARE @EndMonth SMALLDATETIME;

IF ISNULL(@IsFiscal,0) = 0
    SELECT @StartMonth = CAST(CAST(@Year AS VARCHAR) + '-01-01' AS SMALLDATETIME);
ELSE
    SELECT @StartMonth = CAST(CAST([Year] AS VARCHAR) + '-' + CAST(NormalMonth AS VARCHAR) + '-01' AS SMALLDATETIME)
    FROM dbo.tblFiscalMapping
    WHERE FiscalYear = @Year;

SET @EndMonth = DATEADD(m, 11, @StartMonth);

SELECT OrderID, OrderNumber, OrderDate, CustomerID, TotalAmount

FROM dbo.tblOrder

WHERE DATEDIFF(m, @StartMonth, OrderDate) >= 0
                                AND DATEDIFF(m, @EndMonth, OrderDate) <= 0