We help IT Professionals succeed at work.

# Fiscal Year Date Problem

on
Medium Priority
893 Views
I am trying to calculate a year to date sales total based off of our fiscal year. Our fiscal year runs from 10/01/yyyy to 9/30/yyyy. My problem is writing the T-SQL that will do the date conversions. Here is an example of what I mean: if I were to want to get the sales totals for the current fiscal year up to todays date I would need to pull data from 10/01/2008 through today (getdate()). So I understand that I would have to take the YEAR of GetDate() and subtract one from it. But here is where/what I'm not understanding...what happens when the fiscal year is between 10/01/yyyy and 12/31/yyyy of the same year? If I use the same code the get the YEAR I'm going to be getting the previous year. Do I need a CASE statement of an IF statement? I'm kind of lost here, any help and direction would be great.....Thanks!
Comment
Watch Question

## View Solutions Only

CERTIFIED EXPERT

Commented:
Try this:
``````-- example with variable
declare
@date datetime
select
--	@date='2008-12-15'
@date='2009-01-15'

select
@date,
cast(case when month(@date) between 10 and 12 then year(@date) else year(@date)-1 end as varchar(4))+'1001' as FY_start,
cast(case when month(@date) between 10 and 12 then year(@date)+1 else year(@date) end as varchar(4))+'0930' as FY_end

-- from table
select
*
from
Sales
where
SaleDate between
cast(case when month(@date) between 10 and 12 then year(@date) else year(@date)-1 end as varchar(4))+'1001'	-- FY_start
and
cast(case when month(@date) between 10 and 12 then year(@date)+1 else year(@date) end as varchar(4))+'0930'  -- FY_end
``````
CERTIFIED EXPERT

Commented:
sorry, in the from table example you need to replace @date with StartDate, the column you filter on.

CERTIFIED EXPERT

Commented:
Another correction is that you need to include the last second of the fiscal year end so you need:

``````-- example with variable
declare
@date datetime
select
--	@date='2008-12-15'
@date='2009-01-15'

select
@date,
cast(case when month(@date) between 10 and 12 then year(@date) else year(@date)-1 end as varchar(4))+'1001' as FY_start,
cast(case when month(@date) between 10 and 12 then year(@date)+1 else year(@date) end as varchar(4))+'0930 23:59:59.997' as FY_end

-- from table
select
*
from
Sales
where
SaleDate between
cast(case when month(SaleDate) between 10 and 12 then year(SaleDate) else year(SaleDate)-1 end as varchar(4))+'1001'	-- FY_start
and
cast(case when month(SaleDate) between 10 and 12 then year(SaleDate)+1 else year(SaleDate) end as varchar(4))+'0930 23:59:59.997'  -- FY_end
``````
CERTIFIED EXPERT
Commented:
sorry, again. the table example won't work as all the rows will comply with the where clause. You need to either group your rows or to provide a fiscal year. Here are to examples of both approaches:
``````-- example with variable
declare
@date datetime
select
--	@date='2008-12-15'
@date='2009-01-15'

select
@date,
cast(case when month(@date) between 10 and 12 then year(@date) else year(@date)-1 end as varchar(4))+'1001' as FY_start,
cast(case when month(@date) between 10 and 12 then year(@date)+1 else year(@date) end as varchar(4))+'0930 23:59:59.997' as FY_end

-- from table grouping by fiscal year
select
case when month(SaleDate) between 10 and 12 then year(SaleDate) else year(SaleDate)-1 end as FiscalYear,
sum(SaleValue) as TotalSalesPerFiscalYear
from
Sales
group by
case when month(SaleDate) between 10 and 12 then year(SaleDate) else year(SaleDate)-1 end

-- from table providing a fiscal year
select
*
from
Sales
where
case when month(SaleDate) between 10 and 12 then year(SaleDate) else year(SaleDate)-1 end = 2008
``````

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
This is what i came up with and it seems to work. Thank you for your quick response and help with my question.
``````DECLARE
@dtValue datetime,
@dtBegOfFsclYr datetime,
@dtMonth int

SELECT
--	@dtValue = getdate(),
@dtValue = '01/05/2009',
@dtMonth = Month(getdate())

if @dtMonth between '10' and '12'
set @dtBegOFFsclYr = Cast(Cast(YEAR(@dtValue) as VarChar) + '-' + '10' + '-' + '01' as datetime)
else
set @dtBegOfFsclYr = Cast(Cast(YEAR(@dtValue) - 1 as VarChar) + '-' + '10' + '-' + '01' as datetime)

select @dtValue, @dtBegOfFsclYr, @dtMonth
``````
CERTIFIED EXPERT

Commented:
Don't do that. It works to get the fiscal dates in some variables but you need to do that in a select statement in order to achieve your goal.

Use the code I gave you.
CERTIFIED EXPERT

Commented:
The solution I gave doesn't use any variable and can be very easily used in a select statement of its own or in a view. Your approach cannot be used in these situations as it uses variables so it will work only in a batch script, stored procedure or table value function.

Commented:
I have attached the Stored Proc that I created and it seems to work. I think that it is returning the correct data. Please let me know your thoughts and if I'm totally out in left field...Thanks again.
``````SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[x_SlsbyPrsn_rpt]

AS

DECLARE
@dtValue datetime,
@dtFirstOfMonth datetime,
@dtBegOfFsclYr datetime,
@dtMonth int

SELECT
@dtValue = GETDATE(),
@dtFirstOfMonth = CAST((CAST(YEAR(@dtValue) AS varchar) + '-' + CAST(MONTH(@dtValue) AS varchar) + '-1') AS datetime),
@dtBegOfFsclYr = Cast(Cast(YEAR(@dtValue) - 1 as VarChar) + '-' + '10' + '-' + '01' as datetime)

IF  @dtMonth between '10' and '12'
set @dtBegOFFsclYr = Cast(Cast(YEAR(@dtValue) as VarChar) + '-' + '10' + '-' + '01' as datetime)
ELSE
set @dtBegOfFsclYr = Cast(Cast(YEAR(@dtValue) - 1 as VarChar) + '-' + '10' + '-' + '01' as datetime)

SELECT
SalesPerson.Name,
WHERE
YTDOrdEnt = (SELECT convert(VarChar(20), Convert(money, Sum(SoHeader.TotOrd)))
Where
YTDOrdShpd = (SELECT convert(VarChar(20), Convert(money, Sum(SoShipHeader.TotInvc)))
Where
[From Date] = convert(VarChar, @dtFirstOfMonth, 1),
[To Date] = convert(VarChar, @dtValue, 1)
FROM
Join SalesPerson on SoHeader.SlsPerID = SalesPerson.SlsPerID
WHERE
SoHeader.SlsPerID in ('8', '7', '11', '5', '17', '9', '16')
``````
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Think you are off the track... Check out what happens in November.

Really,

do you want some more ?

CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
sorry, financial_month = datepart(month,dateadd(mm,3,getdate()))   (want the period number)

CERTIFIED EXPERT

Commented:
The point is ochness YOU DON'T NEED stored procedure. A simple select statement will do. I will send you the code later.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Although late to this thread, and have not read the detail, tending to agree with Zberteoc....

I have a customer with the same financial period and they went through all kinds of "fun" and really, it was kind of OK to handle directly in a query. We did end up building a calendar table for BI diemsions and that found it's way into some of the more complex queries.

But from what I have seen (and again it is not detailed), gotta agree with that Zberteoc person...
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
There might be other reasons to use a Stored Procedure, but financial period alone is not the be all and end all....

Commented:
Thank you both for your comments. The reason I'm using a Stored Proc is becuse I'm returing this data in a report through SSRS. I know I can use the plain code with SSRS as well. you will have to forgive my ignorance as I am still fairly new to this and I'm trying to learn. I'm still kind of confused, but I really appreciate you both taking the time to assist me with this issue.

Commented:
Also I have to be able to run the report at a given point in time/date. The report will show sales for each sales person for the month (from the beginning of the month to the date the report is run) and then fiscal year to date.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
OK, but, please consider (copying directly from above) 2009-10-01  :

``````DECLARE
@dtValue datetime,
@dtFirstOfMonth datetime,
@dtBegOfFsclYr datetime,
@dtMonth int

SELECT
@dtValue = '20091001',
@dtFirstOfMonth = CAST((CAST(YEAR(@dtValue) AS varchar) + '-' + CAST(MONTH(@dtValue) AS varchar) + '-1') AS datetime),
@dtBegOfFsclYr = Cast(Cast(YEAR(@dtValue) - 1 as VarChar) + '-' + '10' + '-' + '01' as datetime)

IF  @dtMonth between '10' and '12'
set @dtBegOFFsclYr = Cast(Cast(YEAR(@dtValue) as VarChar) + '-' + '10' + '-' + '01' as datetime)
ELSE
set @dtBegOfFsclYr = Cast(Cast(YEAR(@dtValue) - 1 as VarChar) + '-' + '10' + '-' + '01' as datetime)

select @dtfirstofmonth, @dtbegoffsclyr
``````
CERTIFIED EXPERT
Commented:
Your query has an error. You compare a money column with a date in the YTDOrdShpd column SELECT:

...
Where

oShipHeader.TotInvc is a money column as I asume while @dtValue is a datetime variable.
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile