SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Getting Fiscal Year
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vack
Constraint Violating Yak Guru

USA
484 Posts

Posted - 02/14/2011 :  09:20:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 02/14/2011 :  10:13:01  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Vack
Constraint Violating Yak Guru

USA
484 Posts

Posted - 02/14/2011 :  10:17:29  Show Profile  Reply with Quote
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.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 02/14/2011 :  10:27:37  Show Profile  Reply with Quote
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
Go to Top of Page

Vack
Constraint Violating Yak Guru

USA
484 Posts

Posted - 02/14/2011 :  11:14:49  Show Profile  Reply with Quote
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')
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000