Getting Fiscal Year - SQL Server Forums

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

Getting Fiscal Year

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-02-14 : 09:20:52

Working with the following statement which is a SQL 2000 view.:

SELECT     LEFT(oehdrhst_sql.inv_dt, 4) AS [Invoice Year], LEFT(RIGHT(oehdrhst_sql.inv_dt, 4), 2) AS [Invoice Month], RIGHT(oehdrhst_sql.inv_dt, 2) 
                      AS [Invoice Day], oelinhst_sql.prod_cat AS [Product Category], oelinhst_sql.sls_amt AS [Sales Amount], LEFT(oehdrhst_sql.ord_dt, 4) AS [Order Year], 
                      LEFT(RIGHT(oehdrhst_sql.ord_dt, 4), 2) AS [Order Month], RIGHT(oehdrhst_sql.ord_dt, 2) AS [Order Day]
FROM         dbo.arcusfil_sql arcusfil_sql INNER JOIN
                      dbo.oehdrhst_sql oehdrhst_sql ON arcusfil_sql.cus_no = oehdrhst_sql.cus_no INNER JOIN
                      dbo.arslmfil_sql arslmfil_sql ON oehdrhst_sql.slspsn_no = arslmfil_sql.slspsn_no INNER JOIN
                      dbo.oelinhst_sql oelinhst_sql ON oehdrhst_sql.ord_no = oelinhst_sql.ord_no AND oehdrhst_sql.inv_no = oelinhst_sql.inv_no
WHERE     (oehdrhst_sql.inv_dt >= 20100401)

I would like to be able to just have this automatically pull in the current fiscal year which starts on 04/01 with out having to come in once a year and change the year.

odhdrhst_sql.inv_dt is an int

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-14 : 10:13:01

just take the where clause out of the view then and specify it when you query the view??

Or am I missing something?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-02-14 : 10:17:29

I want it to automatically give me records in the current fiscal year. I don't want to have to query it. Its going to be for a user that is running a report that doesn't have the technical knowledge to make changes to a query. I have to make it as simple as possible for them.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-14 : 10:27:37

I think he wants this
dateadd(year,datediff(year,0,getdate()),'18990401'), but I don't beleieve you can use getdate() in a view?

Jim

Everyday I learn something that somebody else already knew

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-02-14 : 11:14:49

I ended up creating a job that will run once a year that will change my view.

WHERE (oehdrhst_sql.inv_dt >= CONVERT(char(4), YEAR(GETDATE()), 0) + '05' + '01')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources