We help IT Professionals succeed at work.

Fiscal Year Date Problem

ochness
ochness asked
on
Medium Priority
893 Views
Last Modified: 2012-05-06
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

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

Open in new window

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

Open in new window

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

Open in new window

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

Ask the Experts

Author

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

Open in new window

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.

Author

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 
	SoHeader.slsperid,
	SalesPerson.Name,
	OrdersEntered = convert(VarChar(20), Convert(money, Sum(SoHeader.TotOrd))),
	OrdersShipped = (SELECT convert(VarChar(20), Convert(money, Sum(SoShipHeader.TotInvc)))
					FROM SoShipHeader
					WHERE 
					SoShipHeader.ShipDateAct >= @dtFirstOfMonth and SoShipHeader.ShipDateAct <= @dtValue and 
					SoShipHeader.SlsPerID = SalesPerson.SlsPerID),
	YTDOrdEnt = (SELECT convert(VarChar(20), Convert(money, Sum(SoHeader.TotOrd)))
				FROM SoHeader
			    Where
				SoHeader.Crtd_DateTime >= @dtBegOfFsclYr and SoHeader.Crtd_DateTime <= @dtValue and
				SoHeader.SlsPerID = SalesPerson.SlsPerID),
	YTDOrdShpd = (SELECT convert(VarChar(20), Convert(money, Sum(SoShipHeader.TotInvc)))
				  From SoShipHeader
				  Where
				  SoShipHeader.ShipDateAct >= @dtBegOfFsclYr and SoShipHeader.TotInvc <= @dtValue and
				  SoShipHeader.SlsPerID = SalesPerson.SlsPerID),
	[From Date] = convert(VarChar, @dtFirstOfMonth, 1),
	[To Date] = convert(VarChar, @dtValue, 1)
FROM 
	SoHeader
	Join SalesPerson on SoHeader.SlsPerID = SalesPerson.SlsPerID
WHERE 
	SoHeader.SoTypeID in ('EO', 'SO') and
	SoHeader.Crtd_DateTime >= @dtFirstOfMonth and SoHeader.Crtd_DateTime <= @dtValue and
	SoHeader.SlsPerID in ('8', '7', '11', '5', '17', '9', '16')
GROUP BY soheader.slsperid, SalesPerson.Name, SalesPerson.slsperid
ORDER BY SoHeader.SlsPerID Asc

Open in new window

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Think you are off the track... Check out what happens in November.

Really,
financial_year = datename(year,dateadd(mm,3,getdate()))
financial_month = datename(month,dateadd(mm,3,getdate()))


do you want some more ?



Mark WillsTopic Advisor, Page Editor
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.
Mark WillsTopic Advisor, Page Editor
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...
Mark WillsTopic Advisor, Page Editor
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....

Author

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.

Author

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.
Mark WillsTopic Advisor, Page Editor
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

Open in new window

CERTIFIED EXPERT
Commented:
Your query has an error. You compare a money column with a date in the YTDOrdShpd column SELECT:

...
Where
            SoShipHeader.ShipDateAct >= @dtBegOfFsclYr and SoShipHeader.TotInvc <= @dtValue and

oShipHeader.TotInvc is a money column as I asume while @dtValue is a datetime variable.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.