| Author |
Topic |
|
detlion1643
Yak Posting Veteran
67 Posts |
 Posted - 2010-03-16 : 09:50:43
|
| I am looking for a way to check against 10 or so values. They are basically in sets of 2 (cola/colb go together, colc/cold, etc...).I need to check the first columns to see if they are greater than 0, and depending which ones are, check the corresponding second columns to see which ones of those are greater than 0, and if there are any, take the minimum of those values. If this second check fails, I need to take a maximum of the second columns, otherwise if all checks fail, I need to return NULL.I've never created a udf before but have used case when's. Just looking for some advice on an efficient way of doing these many checks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-16 : 09:52:02
|
| can you show some sample data and explain what you want?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
detlion1643
Yak Posting Veteran
67 Posts |
Posted - 2010-03-16 : 10:02:14
|
| This is how my table looks now.a,b,c,d,e,f,g,h0,0,0,0,0,0,0,05,3,7,0,4,8,1,30,0,5,7,2,0,4,8This is how my table should look after (just adding a column)a,b,c,d,e,f,g,h,i0,0,0,0,0,0,0,0,NULL (or 0 if it can't be null)5,3,7,0,4,8,1,3,10,0,5,7,2,0,4,8,4a,c,e,g are prices, b,c,d,f,h are inventories if this helps (can't use a price if there is no inventory).First thing - check if prices are 0 (if they are can't use)Second thing - when price is >0, check if it has stockThird thing - column i = minimum of all prices with stockFourth thing - if nothing has stock, but there are prices, column i = maximum of all pricesFifth thing - if nothing has prices greater than 0, then column i= Null (or 0). |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-16 : 10:05:16
|
| how do you identify which price is for which stock?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
detlion1643
Yak Posting Veteran
67 Posts |
Posted - 2010-03-16 : 10:08:16
|
| As in the first post, they are basically in sets of 2.a,b - price,stockb,c - price,stockc,d - price,stocke,f - price,stockg,h - price,stockIt's arranged like this because there are 5 different suppliers. The prices and stocks are not stored in any other database/table (stupid design, but that's how I took it over). |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-16 : 10:09:48
|
| its not a good method to store it like this. have you heard of normalisation?why cant you store it row wise?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
detlion1643
Yak Posting Veteran
67 Posts |
Posted - 2010-03-16 : 10:17:39
|
| I was hoping to not have to redo the design. Ya, I've heard of nomalization.Oh well, guess I'm off to redesigning... |
|
|
|
|
|
|
udf or nested case when's