December 8, 2013 at 11:52 pm
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.
E.G If PAD is 4, then I know the Month is April. Then I need to pull data starting from April of 5 years back, which is 2008.
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'
December 9, 2013 at 12:06 am
you want to show PAD 4 = April 2008.
you need to map the PAD column data to represent your needs, as there are no dates, as you only now which value of PAD column represent which month and year.
December 9, 2013 at 12:52 am
If PAD is 4, then I know the Month is April. Then I need to pull data starting from April of 5 years back, which is 2008.
December 9, 2013 at 1:06 am
hoseam (12/9/2013)
If PAD is 4, then I know the Month is April. Then I need to pull data starting from April of 5 years back, which is 2008.
which column indicate the which year it is
December 9, 2013 at 1:27 am
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
December 9, 2013 at 2:28 am
you the maintaining your data Month wise, you need a way to segregate this data annual wise, you have PAD which represent month, which you do not have a column which can determine the "year", you need to figure out a way to determine the year if you want to apply the MMM-yyyy filter.
December 9, 2013 at 2:57 am
twin.devil (12/9/2013)
you the maintaining your data Month wise, you need a way to segregate this data annual wise, you have PAD which represent month, which you do not have a column which can determine the "year", you need to figure out a way to determine the year if you want to apply the MMM-yyyy filter.
The year is determined by the current year, DATEADD(yyyy,-5). I minus five for every current year.
December 9, 2013 at 3:06 am
hoseam (12/9/2013)
twin.devil (12/9/2013)
you the maintaining your data Month wise, you need a way to segregate this data annual wise, you have PAD which represent month, which you do not have a column which can determine the "year", you need to figure out a way to determine the year if you want to apply the MMM-yyyy filter.The year is determined by the current year, DATEADD(yyyy,-5). I minus five for every current year.
on which column you will apply this filter (Where Clause).. e.g col_Year = DATEADD(yyyy,-5)?
December 9, 2013 at 3:21 am
twin.devil (12/9/2013)
hoseam (12/9/2013)
twin.devil (12/9/2013)
you the maintaining your data Month wise, you need a way to segregate this data annual wise, you have PAD which represent month, which you do not have a column which can determine the "year", you need to figure out a way to determine the year if you want to apply the MMM-yyyy filter.The year is determined by the current year, DATEADD(yyyy,-5). I minus five for every current year.
on which column you will apply this filter (Where Clause).. e.g col_Year = DATEADD(yyyy,-5)?
select DATEADD(yyyy,-5, getdate()). Current year, I want data from 5 years back. for any year I will always return 5 years back.
I think I need to create @startdate. a day as '01', a month from PAD and a years as DATEADD(yyyy,-5, getdate()). I think I', not sure, or even if that's a wise approach
December 9, 2013 at 4:03 am
hoseam (12/9/2013)
twin.devil (12/9/2013)
hoseam (12/9/2013)
twin.devil (12/9/2013)
you the maintaining your data Month wise, you need a way to segregate this data annual wise, you have PAD which represent month, which you do not have a column which can determine the "year", you need to figure out a way to determine the year if you want to apply the MMM-yyyy filter.The year is determined by the current year, DATEADD(yyyy,-5). I minus five for every current year.
on which column you will apply this filter (Where Clause).. e.g col_Year = DATEADD(yyyy,-5)?
select DATEADD(yyyy,-5, getdate()). Current year, I want data from 5 years back. for any year I will always return 5 years back.
I think I need to create @startdate. a day as '01', a month from PAD and a years as DATEADD(yyyy,-5, getdate()). I think I', not sure, or even if that's a wise approach
If today is 09-DEC-2013 then 5 year back date will be "09-DEC-2008", so if you want to go back to Let Say ''09-Apr-2008", then you have to create your StartDate. like you mentioned in your reply. Why you think that approach will not be WISE?
December 9, 2013 at 6:10 am
I thought maybe there could be another shorter way, I already implemented @startdate I was checking for other ways to implement it.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply