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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Pulling 5 years back data based on a Month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

85 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
17636 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

85 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
17636 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  
 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