Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Pulling 5 years back data based on a Month
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

86 Posts

Posted - 12/09/2013 :  01:52:29  Show Profile  Reply with Quote
I have a below query, with only 1 input, Scheme, then pull the data. I have PAD in table2 that holds a month, then I need to pull five years back data based on that month.

A year will always be the current year. A day will always be '01'. I need a Month to start counting, which I get it from PAD, and the last Month should always be the current Month, which will now be December.

so in short, From 5 years back(from a '01', a month from PAD, 5 years back from the current year) to: (a day is always be '01', Current Month and a year is always a current year.)

today is the 09 Dec 2013, five years back data will be:
startdate: 01, a month from PAD, 2008
enddate: 01, Dec, 2013

My issue is with the Where clause, I thought of DATEADD(yyyy,-5, convert(datetime, 'DATE', 112)) but my problem is I have have a DATE yet, begin and end DATE, since I need to look at PAD then derive a date. please help.

select top 5

,tbl1.SalaryBill
,tbl1.Rate
,tbl1.Rate
,tbl2.PAD

From Table1 tbl1
JOIN Table22 tbl2
ON tbl1.Scheme = tbl2.Scheme
where tbl1.Scheme = '99993'

Edited by - stahorse on 12/09/2013 03:30:38

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 12/09/2013 :  03:15:08  Show Profile  Reply with Quote

WHERE date_column >= DATEADD(MONTH, tbl2.PAD - 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 5, 0))



KH
Time is always against us

Go to Top of Page

stahorse
Yak Posting Veteran

86 Posts

Posted - 12/09/2013 :  03:29:34  Show Profile  Reply with Quote
A year will always be the current year. A day will always be '01'. I need a Month to start counting, which I get it from PAD, and the last Month should always be the current Month, which will now be December.

so in short, From 5 years back(from a '01', a month from PAD, 5 years back from the current year) to: (a day is always be '01', Current Month and a year is always a current year.)

today is the 09 Dec 2013, five years back data will be:
startdate: 01, a month from PAD, 2008
enddate: 01, Dec, 2013

this is actually what I need
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 12/09/2013 :  05:04:59  Show Profile  Reply with Quote
WHERE date_column >= DATEADD(MONTH, tbl2.PAD - 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 5, 0))
AND   daet_column <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next 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.08 seconds. Powered By: Snitz Forums 2000