Current, Future and Previous Fiscal Years

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Current, Future and Previous Fiscal Years

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2012-04-23 : 14:10:56

Hi there,

I've been trying to solve this issue for a while.

I have been asked create a job that runs each night to load numbers into a column. The numbers represent Fiscal Years


01   Other Previous Fiscal year   (Every other)
02   Current Fiscal Year - 2      (1 Apr 2010 - 31 Mar 2011)
03   Current Fiscal Year - 1      (1 Apr 2011 - 31 Mar 2012)
04   Current Fiscal Year          (1 Apr 2012 - 31 Mar 2013)
05   Current Fiscal Year + 1      (1 Apr 2013 - 31 Mar 2014)
06   Other Fiscal Years           (All other future years)

Fiscal years run from 1st April to 31st March as seen above in brackets. I've given which periods the numbers represent

I'm using a date table that has every date from 2004 - 2015. I have a FISCAL_YEAR column which has the format (12-13...which is this years fiscal) and a standard DATE column.


FISCAL_YEAR   DATE
11-12         2012-03-27
11-12         2012-03-28
11-12         2012-03-29
11-12         2012-03-30
11-12         2012-03-31
12-13         2012-04-01 < NEW FISCAL
12-13         2012-04-02
12-13         2012-04-03
12-13         2012-04-04

I'd like 04 placed against dates between 1st April 2012 - 31st March 2013 as this is the current fiscal year. and 03 for last years dates etc...

If the job runs on the 1st April next year I would want this to be the new fiscal year and 04 placed for all dates

Please can anyone help me... Any help provided is much appreciated

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 15:31:18

[code]
UPDATE table
SET YourCol= CASE WHEN [DATE] >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())+3,0)
AND [DATE]< DATEADD(mm,3,DATEADD(yy,DATEDIFF(yy,1,GETDATE()),0))
THEN '04'
WHEN [DATE] >= DATEADD(mm,DATEDIFF(mm,0,DATEADD(yy,-1,GETDATE()))+3,0)
AND [DATE]< DATEADD(mm,3,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))
THEN '03'
WHEN [DATE] >= DATEADD(mm,DATEDIFF(mm,0,DATEADD(yy,-2,GETDATE()))+3,0)
AND [DATE]< DATEADD(mm,3,DATEADD(yy,DATEDIFF(yy,-1,GETDATE()),0))
THEN '02'
WHEN [DATE] >= DATEADD(mm,DATEDIFF(mm,0,DATEADD(yy,1,GETDATE()))+3,0)
AND [DATE]< DATEADD(mm,3,DATEADD(yy,DATEDIFF(yy,2,GETDATE()),0))
THEN '03'
WHEN [DATE] < DATEADD(mm,DATEDIFF(mm,0,DATEADD(yy,-2,GETDATE()))+3,0)
THEN '01'
ELSE '06'
END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cidr
Posting Yak Master

207 Posts

Posted - 2012-04-23 : 17:08:49

Hi visakh

I couldn't get the results I was looking for. I tried to play about with the code but I'm still a little lost.

For example, there's no results for 04 and 02 starts at 2010-07-01.

I'll keep trying with the code you provided. If you find the time to have a look I would be grateful:)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-23 : 19:07:36

This is a slightly different approach from what Visakh proposed - here I am creating sort of a calendar table on the fly. I like this because you can uncomment the "--select * from cte;" and execute the code upto that line to see what the calendar looks like. Also, if you like you can save the calendar data in a temporary or permanent table.

DECLARE @referenceDate DATE = GETDATE();
;WITH cte AS
(
	SELECT
		CAST('01' AS CHAR(2)) AS FiscalYearCode,
		CAST('19000101' AS DATE) AS FiscalYearStartDate,
		DATEADD(YEAR,DATEDIFF(YEAR,'19000101',@referenceDate)-2,'19000401') AS NextFiscalYearStartDate
	UNION ALL
	SELECT
		'02',	
		DATEADD(YEAR,DATEDIFF(YEAR,'19000101',@referenceDate)-2,'19000401'),
		DATEADD(YEAR,DATEDIFF(YEAR,'19000101',@referenceDate)-1,'19000401')
	UNION ALL
	SELECT
		'03',	
		DATEADD(YEAR,DATEDIFF(YEAR,'19000101',@referenceDate)-1,'19000401'),
		DATEADD(YEAR,DATEDIFF(YEAR,'19000101',@referenceDate)-0,'19000401')
	UNION ALL
	SELECT
		'04',	
		DATEADD(YEAR,DATEDIFF(YEAR,'19000101',@referenceDate)-0,'19000401'),
		DATEADD(YEAR,DATEDIFF(YEAR,'19000101',@referenceDate)+1,'19000401')
	UNION ALL
	SELECT
		'05',	
		DATEADD(YEAR,DATEDIFF(YEAR,'19000101',@referenceDate)+1,'19000401'),
		DATEADD(YEAR,DATEDIFF(YEAR,'19000101',@referenceDate)+2,'19000401')
	UNION ALL
	SELECT
		'06',	
		DATEADD(YEAR,DATEDIFF(YEAR,'19000101',@referenceDate)+2,'19000401'),
		'20990401'
) -- select * from cte;
UPDATE y SET
	YourCol = c.FiscalYearCode
FROM
	YourTable y
	INNER JOIN cte c ON
		y.DATE >= FiscalYearStartDate AND y.DATE < NextFiscalYearStartDate;

I tested the calendar part, but not the update part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 19:18:43

try this instead?


UPDATE table
SET YourCol= CASE WHEN [DATE] >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())+3,0)
                  AND [DATE]< DATEADD(mm,3,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))
                  THEN '04'
                  WHEN [DATE] >= DATEADD(mm,DATEDIFF(mm,0,DATEADD(yy,-1,GETDATE()))+3,0)
                  AND [DATE]< DATEADD(mm,3,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))
                  THEN '03'
                  WHEN [DATE] >= DATEADD(mm,DATEDIFF(mm,0,DATEADD(yy,-2,GETDATE()))+3,0)
                  AND [DATE]< DATEADD(mm,3,DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0))
                  THEN '02'
                  WHEN [DATE] >= DATEADD(mm,DATEDIFF(mm,0,DATEADD(yy,1,GETDATE()))+3,0)
                  AND [DATE]< DATEADD(mm,3,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))
                  THEN '03'
                  WHEN [DATE] < DATEADD(mm,DATEDIFF(mm,0,DATEADD(yy,-2,GETDATE()))+3,0)
                  THEN '01'
                  ELSE '06'
             END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 20:26:09

[code]
UPDATE t
SET t.Col= r.Code
FROM YourTable t
INNER JOIN
(
select case when M > -3 then dateadd(yy,datediff(yy,0,getdate())+ M,'19000401') else 0 end AS Start,
case when M < 2 then dateadd(yy,datediff(yy,0,getdate())+ M+1,'19000401')-1 else '99991231' end AS [End],
Code
from (select 0 AS M,'04' AS Code
union all
select -1,'03'
union all
select -2,'02'
union all
select -3,'01'
union all
select 1,'05'
union all
select 2,'06'
)tmp
)r
ON t.[DATE] BETWEEN r.Start AND r.[End]
[/code]

I hope [Date] field doesnt have time part stored in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources