I second this, please retry to post your data and expected result. You can try attaching something again, every post can have an attachment, not only the inital one, you can also edit your post for one day.
Anyway, you don't need many updates with several conditions and switch isn't working in SQL you'll need CASE. But don't get this route, you have similar updates differing in the data about conditions, well, that screams out load to be data itself. You have a database here and it's good with data, instead of writing tons of code, put the conditions as data.
To demonstrate the priciple:
Code:
declare @persons as table (age int, agecategory varchar(20));
insert into @persons (age) values (1),(5),(47),(15),(89),(1),(8),(17);
declare @agecategories as table (minage int, maxage int, agecategory varchar(20));
insert into @agecategories values (0,1,'baby'),(2,12,'kid'),(13,19,'teenager'),(20,65,'adult'),(65,1000,'senior');
update @persons set agecategory = agecat.agecategory from @persons as pers inner join @agecategories as agecat on pers.age between agecat.minage and agecat.maxage
Select * from @persons
@persons here compares to the table you want to update differently under different conditions, and the conditions are given as @agecategories defining age ranges for which certain agecategory names are valid, from baby to senior. In your case that would be a table holding the different combinations of market, competitor and measureid, most probably you have this in a table already anyway, otherwise you see how you can build a table variable or you could make this a permanent table of your database, because maybe you want to repeat this with differing editable conditions.
The whole concept is simply a correlated update. The one table acts as a template list. Instead of a table you might also have two functions for N and M determining the two values of rankinng N out of M. M may be a constant or a count of some records. Anything goes, but don't write out several hardcoded and very similar updates, if you can simply turn all of them to one update taking its conditions and target values as data. Common, you have to think the database way, if you use a database. This data merely is a parameterization and generalisation of otherwise same code, this is not only what data is all about, this is also what programming is all about, you generalize things, parameterize and then can do things repeatedly and fast, just changing the outset by configuration, template or most generally speaking meta data.
If you write out all the different updates you could also manually enter all the rankinngs into your table, what amount of work could you really save that way? Be lazy in doing cumbersome things all over again by thinking of the code to do it for you just concentrating on the core and varying data about the doing, control data, meta data, or even just a function to compute that meta data. That's what it's all about.
Bye, Olaf.
