Pulling 5 years back data based on a Month

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

SQL Server 2012 Forums

Transact-SQL (2012)

Pulling 5 years back data based on a Month

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-12-09 : 01:52:29

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'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-09 : 03:15:08

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

KH
[spoiler]Time is always against us[/spoiler]

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-12-09 : 03:29:34

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-09 : 05:04:59

[code]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)[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources