SQL and Fiscal year data retrieval

Question

I have an issue trying to retrieve fiscal data from my database. For example, the table I am using to calculate data between months has the month and the fy year (i.e. 1112 (Nov 2012)). When I try to calculate the AMT Due data between the months Oct - Dec there is no data that is calculated. However, when I calculate the data between Jan - Sept, it has no problem but fails to incorporate the data from the beginning of the fy(OCT NOV DEC) resulting with inaccurate data.

M_Y       Amt Due   dept    FY
1112      362.44     D2     2013
1212      10.50      D4     2013
0213      55.55      D1     2013

@LF = CAST((CAST@Current_FY_IN AS INT)-1) AS CHAR)
@startdate = @LFy + '10'
@enddate = CASE SUBSTRING(@mon_IN,1,1) WHEN '1' THEN @LY+@mon_IN ELSE @Current_FY_IN+@month_IN
@Current_FY_IN  (i.e. 2013) 
END

I know the issue, I just can't seem to fix this problem. I would love any ones input on this matter.

 SELECT M_Y, ISNULL(SUM(Amt Due),0) AS AmtDueNow 
      FROM FYAmmountDue 
      WHERE
'20' + RIGHT(M_F,2) + LEFT(M_F,2) BETWEEN @startdate AND @enddate - 1 
 AND FY = @Current_FY_IN 

Answer 1

You're relying on the BETWEEN operator, which might give you surprising results with columns that you might interpret as numbers, but which the dbms will interpret as char(n) or varchar(n). This is especially true with incomplete dates.

The simplest approach is to avoid calculating altogether, and just name the months you want in the WHERE clause. Defensive programming would name the fiscal year, too.

WHERE M_Y IN ('1012', '1112', '1212') 
  AND FY = 2013

A better approach is to fix your storage in a way that preserves useful (and sensible) semantics. Here are two different ways to store fiscal periods, "month first" and "year first".

drop table fiscal_periods;
create table fiscal_periods (
  y_m char(4) not null unique,
  m_y char(4) not null unique
);

insert into fiscal_periods values ('1201', '0112');
insert into fiscal_periods values ('1202', '0212');
insert into fiscal_periods values ('1203', '0312');
insert into fiscal_periods values ('1204', '0412');
insert into fiscal_periods values ('1205', '0512');
insert into fiscal_periods values ('1206', '0612');
insert into fiscal_periods values ('1207', '0712');
insert into fiscal_periods values ('1208', '0812');
insert into fiscal_periods values ('1209', '0912');
insert into fiscal_periods values ('1210', '1012');
insert into fiscal_periods values ('1211', '1112');
insert into fiscal_periods values ('1212', '1212');
insert into fiscal_periods values ('1301', '0113');
insert into fiscal_periods values ('1302', '0213');
insert into fiscal_periods values ('1303', '0313');
insert into fiscal_periods values ('1304', '0413');
insert into fiscal_periods values ('1305', '0513');
insert into fiscal_periods values ('1306', '0613');
insert into fiscal_periods values ('1307', '0713');
insert into fiscal_periods values ('1308', '0813');
insert into fiscal_periods values ('1309', '0913');
insert into fiscal_periods values ('1310', '1013');
insert into fiscal_periods values ('1311', '1113');
insert into fiscal_periods values ('1312', '1213');

Now try a simple query, month first.

-- Select the periods between Oct 2012 and Feb 2013
select m_y
from fiscal_periods
where m_y between '1012' and '0213'
order by m_y;
-- Returns the empty set.

The same query, year first.

select y_m
from fiscal_periods
where y_m between '1210' and '1302'
order by y_m;

y_m 
--
1210
1211
1212
1301
1302

Better still, avoid Y2K issues and store the year as four digits: 201201, 201302, etc.

Answer 2

You could write a user-defined function to return Fiscal year for a given date...

    CREATE FUNCTION dbo.fFiscalYear(@calendarDt DateTime)
      RETURNS Int
    (
        DECLARE @year Int
        SET @year = YEAR(@calendarDt)
        IF MONTH(@calendarDt) NOT IN (10, 11, 12)
           SET @year = @year - 1
        RETURN @year
    )
    GO

GRANT EXECUTE ON dbo.fFiscalYear TO  PUBLIC
GO

It's a pretty light-weight function, which you can now use in queries.

Here's an example:

 SELECT CONVERT(varchar(4), trans_dt, 12) AS m_y
        COALESCE(SUM(amt_due), 0) AS amt_due_now,
        dbo.fFiscalYear(trans_dt) AS fiscal_year
   FROM TransactionTable
        GROUP BY CONVERT(varchar(4), trans_dt, 12), dbo.fFiscalYear(trans_dt) 
        ORDER BY dbo.fFiscalYear(trans_dt), CONVERT(varchar(4), trans_dt, 12)

IF m_y is already in your database, you can use that for your BETWEEN selection, as long as everything is in the same century.