I started to reply to this one but was busy so
stopped.

Yes the problem here is the addition of new fields
once a year. The view you currently have is
essentially a cross tabulation.

I see this is a example of where the data structure
has dictated the data table design. 

I think your structure is likely to be something like 
field1     1999,    2000 et..
clientid    value    value

This structure leads to some years where there are no
values

In cases like this I reverse the table structure

If you create a new table structure

field1/autoid    client    year   value
00001            client1   1999    5665
00002            client1   2000     879
00003            client2   1998    8767

If your table had the above structure then a query
over a range of years would be easy.  It is also a
very efficient way to store data because you only
create a record if you have a year.

From the revisied structure then a cross tabulation
VIEW (Not difficult to implement) will give you your
current column year view. So you can have the best of
both worlds.

I hope this helps or gives ideas

Roland


--- David Cameron <dcameron@i...> wrote:
> 
> I hate to suggest it but you may need to alter the
> view or proc each time
> you add a new record to the year table. The only
> solutions I could think of
> where generating a string and executing it. If you
> alter the view each time
> you edit the table you would end up with a SELECT
> statement something like
> this.
> 
> SELECT SUM(Year)
> FROM MyTable
> WHERE Year <= 2000 AND Year >= 1998
> 
> UNION 
> 
> SELECT SUM(Year)
> FROM MyTable
> WHERE Year <= 1997 AND Year >= 1995
> 
> etc..
> 
> If the year isn't changing regularly this should not
> be too much of a
> problem.
> 
> Ask the SQL guru at
> http://www.sqlteam.com/AskUs.asp, if you 'stump the
> guru' you get merchandise!
> 
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
> 
> -----Original Message-----
> From: Chris [mailto:a9603897@u...]
> Sent: Thursday, 18 October 2001 7:08 PM
> To: sql language
> Subject: [sql_language] Changing view/Stored
> procedure dynamically
> 
> 
> Hello,
> 
> I need to do following in SQL Server 2000:
> 
> I have a table with a field called year: e.g.: 1999
> In this row are also some values contained. e.g.
> field: Value1:
> 
> I need to create a view/stored procedure which
> accumulates the value of 3 
> years -> BUT always taken from the actual year.
> example: in the table are 6 rows, every with a
> different year
> (1999,1998,1997,1996,1995,1994). The view should
> then consist of the 2 
> rows -> One row with the sum of: 1999,1998 and 1997
> and one with the sum 
> of 1996,1995 and 1994).
> 
> BUT -> when I add a year to the table(e.g.: 2000),
> then the view/stored 
> procedure should consist of 3 rows(1. row: sum of
> 2000,1999,1998, 2.row: 
> 1997, 1996 and 1995, and the third with the sum of
> 1994)
> 
> Does anyone knows a solution for my problem?
> maybe an example.
> 
> Thanks a lot for every hint.
> 
> Bye,
> 
> Christian
> 
> 
> 
>