• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 875
  • Last Modified:

Fiscal Year Date Problem

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!
0
ochness
Asked:
ochness
  • 8
  • 5
  • 4
3 Solutions
 
ZberteocCommented:
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

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

0
 
ZberteocCommented:
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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ZberteocCommented:
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

0
 
ochnessAuthor 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

0
 
ZberteocCommented:
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.
0
 
ZberteocCommented:
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.
0
 
ochnessAuthor 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

0
 
Mark WillsTopic AdvisorCommented:
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 ?



0
 
Mark WillsTopic AdvisorCommented:
sorry, financial_month = datepart(month,dateadd(mm,3,getdate()))   (want the period number)
 
0
 
ZberteocCommented:
The point is ochness YOU DON'T NEED stored procedure. A simple select statement will do. I will send you the code later.
0
 
Mark WillsTopic AdvisorCommented:
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...
0
 
Mark WillsTopic AdvisorCommented:
There might be other reasons to use a Stored Procedure, but financial period alone is not the be all and end all....
0
 
ochnessAuthor 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.
0
 
ochnessAuthor 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.
0
 
Mark WillsTopic AdvisorCommented:
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

0
 
ZberteocCommented:
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.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 8
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now