how to get last 9 months data?

I am working on a stored proc and need last 9 months data. Need a syntax Which will automatically deletes the oldest data when new data will be added to the table(Latest 9 months data).

That syntax is gonna be used in Select Syntax.

I have used

select * from tablename t
left oute开发者_如何学Pythonr join calendartable r on
t.fiscal_month=r.fiscal_month
where t.date > dateadd(m,-9,date) 

I know it is wrong. Could you guys please help me with this.

Thanks

---

You probably want GETDATE to calculate the nine month boundary from now:

where t.date >= dateadd(m,-9, GETDATE())

Beware that if t.date is a date and time field not just date you'll see odd behaviour on the nine-month boundary unless you also round away the time before the comparison.

Or if you're comparing it against another value e.g. the date of the inserted record in your trigger then what you've got is probably OK, e.g. something like

declare @latest date
select @latest = inserted.date
delete from ... where t.date < dateadd(m, -9, @latest)

although I suggest you actually archive off the data, not delete it.

---

Since you've clarified you want whole months, i.e. 9 months from the end of last month, you could use

declare @today date;
declare @firstOfMonth date;
declare @nineMonthsAgo date;
set @today = GETDATE();
set @firstOfMonth = DATEADD(d, 1-DAY(@today), @today);
set @nineMonthsAgo = DATEADD(m, -9, @firstOfMonth);

... WHERE date >= @nineMonthsAgo AND date < @firstOfMonth

---

seems like it's pretty close. Do you need 9 months from right now, or 9 months from a given date?

how about:

declare @date datetime

set @date = dateadd(m, -9, getdate()) -- 9 months from right now

select * 
from tablename t
  left outer join calendartable r 
   on t.fiscal_month=r.fiscal_month
where t.date > @date

---

If You need to delete the data when new data is added, you will need to do it in a trigger. And the sintax inside the trigger would be like this.

DELETE t
FROM 
tablename t
left outer join calendartable r on
t.fiscal_month=r.fiscal_month
where datediff(month, t.date, GETDATE()) > 9

And the select to retrieve your data will be similar.

select * from tablename t
left outer join calendartable r on
t.fiscal_month=r.fiscal_month
where datediff(month, t.date, GETDATE()) < 9