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
 Finding Records between two years
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amphillips
Starting Member

3 Posts

Posted - 06/19/2013 :  14:05:49  Show Profile  Reply with Quote
I'm trying to write a query that will be used several times (potentially to create a view) that will find records that are dated between the current year and two years ago. Example: if I were to run this now, it should pull up all records dated from 2011 to 2013.

I'm not sure where to even start trying to capture just the year. Any suggestions?

select afe_number, rpt_cntr, account, aquisition, SUM(line_amount), gl_date, gl_line_description
from dmrt_nfx.xxnf_gl_detail_table
where rc_uid in (288070, 288071, 288072)
and datepart(year, gl_date) between dateadd(year,-1,GETDATE()) and datepart(year, getdate())
group by afe_number, rpt_cntr, account, aquisition, gl_date, gl_line_description

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 06/19/2013 :  14:09:24  Show Profile  Reply with Quote
Aren't you already doing that via the "and datepart..." in your where clause? It might be more efficient to do that like shwon below:
SELECT  afe_number ,
        rpt_cntr ,
        account ,
        aquisition ,
        SUM(line_amount) ,
        gl_date ,
        gl_line_description
FROM    dmrt_nfx.xxnf_gl_detail_table
WHERE   rc_uid IN ( 288070, 288071, 288072 )
		AND gl_date >= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-2,0)
		AND gl_date < DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
GROUP BY afe_number ,
        rpt_cntr ,
        account ,
        aquisition ,
        gl_date ,
        gl_line_description
Go to Top of Page

amphillips
Starting Member

3 Posts

Posted - 06/19/2013 :  14:14:14  Show Profile  Reply with Quote
Thanks for the tip and help!
When I try running the adjustment, it gives an error.

ORA-00904: "DATEADD": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 5 Column: 20
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 06/19/2013 :  14:16:16  Show Profile  Reply with Quote
Ah, you are using Oracle. This forum is for Microsoft SQL Server, and the query I posted is T-SQL syntax. I am not familiar with Oracle - you might get better and faster responses at an oracle forum or a generalized database forum.
Go to Top of Page

amphillips
Starting Member

3 Posts

Posted - 06/19/2013 :  14:18:21  Show Profile  Reply with Quote
oops! Thanks anyways!
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.06 seconds. Powered By: Snitz Forums 2000