Fiscal YTD WHERE statement help

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

General SQL Server Forums

New to SQL Server Programming

Fiscal YTD WHERE statement help

Author

Topic

hk13
Starting Member

12 Posts

Posted - 2015-02-03 : 11:12:15

Hello,

I'm trying to create a WHERE statement that will calculate values from our current fiscal year to the last complete month. This would be much easier if we had a normal year, but our fiscal year is from March to February, so that complicates things a bit.

I'm using code that was created for us that does the calculations for our entire fiscal years. I thought I had fixed the WHERE statement to work like we wanted last year, but it appears to be broken now after trying it again in January and February. I'm guessing my WHERE statement only works for March and up, but I have no idea how to get it to work for every month. Most attempts I'm trying it's just returning very large and inaccurate values.

I included my WHERE statement below of what I originally had that worked last year. The @BeginYear/Month/etc are retrieved from a different table and @Month is just set to MONTH(GETDATE())-1.

Thanks in advance.

WHERE
(YEAR(SA3.DocumentDate)=@BeginYear AND MONTH(SA3.DocumentDate)>=@BeginMonth AND MONTH(SA3.DocumentDate)<=@Month)
OR
(YEAR(SA3.DocumentDate)=@EndYear AND MONTH(SA3.DocumentDate)<=@EndMonth AND MONTH(SA3.DocumentDate)>=@Month)

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-02-03 : 11:42:45

Why are you saying:

AND MONTH(SA3.DocumentDate)<=@Month)
OR
AND MONTH(SA3.DocumentDate)>=@Month)

with MONTH(GETDATE())-1 you will get everything before and after!!

We are the creators of our own reality!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-03 : 11:45:46

The best thing is to create a table of dates called a date dimension. There's lots of material on it. Here's one reference: http://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho

Note that the AdventureWorksDW2012 sample database has a date dimension table called DimDate. Have a look at it.

The whole point is to look up information (such as your FYTD) rather than computing them.

hk13
Starting Member

12 Posts

Posted - 2015-02-03 : 12:27:29

Thanks for the responses.

I believe the date dimension is what the original creator used to get the @BeginYear, @BeginMonth, @EndYear, and @EndMonth values and they work just fine when calculating for the entire fiscal year.

However, I'm trying to get it to only calculate the fiscal year values up to the last completed month, so it would need some computation I think. For example, it would be March to January if I was to run it today. Therefore, I edited the original SQL query and added an extra @Month parameter.

I used the below because they are also part of different years so it wouldn't have gotten everything. I think it would go something like.. if it's 2014, I want all data greater than or equal to March, but less than the current month. If it's 2015, I want all data less than or equal to February. It made sense to me at the time, and it worked when I originally played around with it in December, so I left it. However, it has since stopped working in the new year, and I've been unable to get it to work properly again.

AND MONTH(SA3.DocumentDate)<=@Month)
OR
AND MONTH(SA3.DocumentDate)>=@Month)

If it helps, here's the whole SQL. It's designed to do it for previous years by changing the -0 and can take parameters.

DECLARE @now date = dateadd(yy,-0,getdate())
DECLARE @FiscalYear int
SELECT @FiscalYear=year(EndDate) from FinancialYear where @now>=StartDate and @now<=EndDate

DECLARE @BranchID int = 8--%DrillDownBranchID%
DECLARE @PG int = 111--%DrillDownPG%
DECLARE @Year int = YEAR(@now)
DECLARE @Month int = MONTH(GETDATE())-1

-- get fiscal begin/end periods
declare @BeginYear int,@BeginMonth int,@EndYear int,@EndMonth int
select @BeginYear=d.BeginYear,@BeginMonth=d.BeginMonth,@EndYear=d.EndYear,@EndMonth=d.EndMonth
from dbo.GetFiscalDates(@FiscalYear) d

SELECT
SUM(TotalQuantityInStatsPer) TotalQty
FROM SalesAnalysis3 SA3 WITH(NOLOCK)

WHERE SA3.BranchID=@BranchID AND SA3.ProductGroup2ID=@PG
AND
(YEAR(SA3.DocumentDate)=@BeginYear AND MONTH(SA3.DocumentDate)>=@BeginMonth AND MONTH(SA3.DocumentDate)<=@Month)
OR
(YEAR(SA3.DocumentDate)=@EndYear AND MONTH(SA3.DocumentDate)<=@EndMonth AND MONTH(SA3.DocumentDate)>=@Month)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-03 : 12:46:15

If you have a date dimension that shows the FY for each date, then isn't it just


where documentdate >= FinancialYear.fybegin and documentdate <= FinancialYear.fyend
-- or whatever the columns are called

??

Better not to mess around with date parts if you can help it.

hk13
Starting Member

12 Posts

Posted - 2015-02-03 : 13:40:42

The table it's pulling from only has the start date and end dates for each fiscal year, so I don't think that would work.

I'll take another look at the date dimension you linked and see if I can work something out from that.

Thanks again for the help.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-03 : 14:05:26

OK -- so say you know the last complete month. Then we have:


where documentdate >= FinancialYear.fybegin and documentdate <= FinancialYear.fyend
  and documentdate <= lastcompletedmonth

or if you want to be fancy:
[code]
where documentdate >= FinancialYear.fybegin and documentdate <= (select min(d) from (values (FinancialYear.fyend), lastcompletedmonth)) v(d)
[/code[

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources