Results 1 to 6 of 6

Thread: Dynamic views

  1. #1
    Join Date
    Sep 2003
    Posts
    212

    Unanswered: 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 help

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Dynamic views

    I think this will do,

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

  3. #3
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Dynamic views

    correction,

    create view myview
    as
    select * from yourarchivetable where date > dateadd(day, -90, getdate

  4. #4
    Join Date
    Sep 2003
    Posts
    212
    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 ?

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    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

  6. #6
    Join Date
    Sep 2003
    Posts
    212
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •