Dynamic views

I havea archive of tables and i want to make a dynamic view for the tables. like a view that shows the last 90 days of data.
is that possible without runnning a script every day or sumthin of that sort?
plz helpI think this will do,

create view myview
as
select * from yourarchivetable where date < dateadd(day, -90, getdate())|||correction,

create view myview
as
select * from yourarchivetable where date > dateadd(day, -90, getdate|||that works ok if the archival table is known. but the archival tables are usally dated (ie name contains the date or year in it) in that case what do u do? the archival tables are monthly tables

say if the view wants last 90 days and today is the 12dec so the view should capture dec, nov, oct, and spet data.

now is this possible ?|||I think what you are looking for here is a partitioned view. It doesn't work exactly the way you want it to, but it should give you what you need with minimal administrative overhead and good performance.

Basically, each of your underlying tables needs to have a check contstraint applied (that should be easy, since you indicate in your post that they are grouped logically by month).

Then you create a view that unions together all the data from the various tables (I'm assuming that the table definitions are the same).

You could probably even figure out a way to programmatically update the view definition on the first day of each month to add the new table (and eliminate the oldest table if you're only going for 90 days).

The check constraints in the underlying tables are important for performance.

Check out Partitioned Views in SQL BOL.

hmscott|||Well rite now i have scripts running running as jobs periodically that keep the views updated for say fiscal year, fiscal calendar year, last 90 days, last 60 days etc.