Need a little help on how to handle a rank query

[tmcrouse]

I started to do an update query like:

update comprank set ranking = '1 out of 1' where measureid = '234' and prodabbrev = 'hmo' and rank = 1 and market = 'ca;

but then I realized that is not going to work based on the raw data I have. Here is an example of the data and I also have 3924 total rows and 36 different measureids, 2 different prodabbrev, and 14 different markets. Here is the data like it is in the table, just a snippet. Well it does not paste right so going to attach the excel

 

[Olaf Doschke]

1. Attachment missing.
2. I don't see a connection to RANK queries, most probably it doesn't have to do with RANK() T-SQL.

Otherwise your goal is still totally in the dark.

Bye, Olaf.

 

[tmcrouse]

Thanks. The attachment not sure why it did not work but I will just have to build it like case statements. No other way I have found to do it. Someone gave me this but it does not work in SQL R2008. I have 36 measureids in all with 14 markets and 2 prodabbrevs. So trying to figure out how to use some sort of switch otherwise I will have to case statement it

update
comprank set ranking = switch (measureID=12,'5 out of 10',measureID=24,'4 out of 10',etc for all measureids) where market
='ca'and prodabbrev ='hmo' and competitor ='California -HMO/POS';
update
comprank set ranking = switch (measureID=12,'5 out of 10',measureID=24,'4 out of 10',etc for all measureids) where market
='ct'and prodabbrev ='hmo' and competitor ='Connecticut -HMO/POS';
update
comprank set ranking = switch (measureID=12,'5 out of 10',measureID=24,'4 out of 10',etc for all measureids) where market
='me'and prodabbrev ='hmo' and competitor ='Maine -HMO/POS';

 

[gmmastros]

If you want what I think you want.... this seems doable, but before I give you advice, I would really need to see some sample data and expected results.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Andrzejek]

If would (probably) help if you would state:

This is what I currently have in my table:

[pre]
comprank

ranking measureid prodabbrev rank market
??? ??? ??? ??? ???
??? ??? ??? ??? ???
??? ??? ??? ??? ???
...
[/pre]
And this is what I need to have after my update statements:

[pre]
comprank

ranking measureid prodabbrev rank market
??? ??? ??? ??? ???
??? ??? ??? ??? ???
??? ??? ??? ??? ???
...
[/pre]
Based on these rules:
1...
2...
3...
Present the data that is representative of your issue(s)

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Olaf Doschke]

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:

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.

 

[Ringedep]

Thank you dudes, I learned many helpful things.