Lowest cost in a record
-
I have a table that has about 500,000 records, each record has four different cost fields that are already populated(List, Itemfile, bluebook, mfr). I would like to populate a fifth cost field (best cost) with the lowest of the four costs in the other fields. Is there an easy/quick way to do this? I wrote a Cursor that does it, but it took over a half hour to run, which is unacceptable. The table layout looks like this...Thanks...
Item - MFR - Itemfile - List - bluebook - BestCost
-
do a simple case, it's wordy but it works
update table
set col = CASE WHEN A< B AND A< C AND A< D AND A < E THEN A
WHEN B < A AND B< C AND....
you get the idea
Or write a MIN function that accepts as many columns (parms) as you need.
But never, never ever use a cursor. If you ever think you have to, you're 99.99% of the time wrong. And I do not know of any example where it would be quicker than set processing.
-
And I know only one... which ain't the case here.
Why can't you add a computed column that will contain the lowest price? Also why isn't the price in its own table??
-
What's involved with a computed column?
The CASE worked like a champ, but sometimes one of the four cost columns is null and it's updating the best cost field to null, which I don't want. I would want it to be the lowest of what ever has a value.
-
Make a function with 4 parameters. Then use this function in the formula for the column (becoming a calculated field).
The function would look something like this :
SET @ReturnValue = @Cost1
IF @Cost2 < @ReturnValue
BEGIN
SET @ReturnValue = @Cost2
END
IF @Cost3 < @ReturnValue
BEGIN
SET @ReturnValue = @Cost3
END
IF @Cost4 < @ReturnValue
BEGIN
SET @ReturnValue = @Cost4
END
RETURN @ReturnValue
You didn't answer my question as to why there were more than 1 price field in the table??
-
Remi, if Cost1 is NULL you function will always return NULL.
IF @Cost2 < @ReturnValue OR @ReturnValue IS NULL
will fix it.
_____________
Code for TallyGenerator -
I never set returnvalue to null... I don't see how that would help, if the Costx is null, then the condition will not be true so the status shouldn't change.
-
If Cost1 is null, @ReturnValue becomes null in first statement, and all conditions will not be be true, so no further statements to execute. @ReturnValue will stay NULL till the end of the function.
My fix will let further statements to execute, and if there is at least one cost not null @ReturnValue will pick it up.
_____________
Code for TallyGenerator -
Oops, I thaugth sometimes the 4th was null... but anyone can be null so that's another problem altogether. Thanx for pointing it out.
-
But as Remi said, you might as well make your 5th cost a computed column. This saves you from updating it all the time - it will always be up to date! If you then need to index it, you still can, although then you can get messy with having all of the ANSI SET options enabled... Look up indexed view in SQL Server Books Online and you'll see what I mean
you can set defaults for the SET option at the server & database level though, so it's not such a problem -
Although the least cost can be determined with the CASE statement, since there are nulls and the least cost algorithm must handle nulls, the case statement would be very complicated. This is a situation where a user defined function would be easier to code, maintain and debug.
CREATE Function udf_LeastCost
(@MFRCost integer , @ItemfileCost integer, @ListCost integer, @bluebookCost integer)
returns integer
as
begin
IF @MFRCost is null and @ItemfileCost is null and @List is null and @bluebookCost is null
return null
-- Replace null with largest possible value for the integer data type
set @MFRCost= COALESCE(@MFRCost, 2147483647 )
set @ItemfileCost= COALESCE(@ItemfileCost, 2147483647 )
set @ListCost = COALESCE(@ListCost , 2147483647 )
set @bluebookCost= COALESCE(@bluebookCost, 2147483647 )
Declare @LeastCost integer
Set @LeastCost = 2147483647
IF @MFRCost < @LeastCost set @LeastCost = @MFRCost
IF @ItemfileCost< @LeastCost set @LeastCost = @ItemfileCost
IF @ListCost < @LeastCost set @LeastCost = @ListCost
IF @bluebookCost< @LeastCost set @LeastCost = @bluebookCost
return @LeastCost
end
go
Alter table Cars
add LeastCost as
dbo.udf_LeastCost
(MFRCost,ItemfileCost,ListCost,bluebookCost)
SQL = Scarcely Qualifies as a Language
-
What about this??
update bestcost set lowestcost = coalesce(itemfilecost, listcost, bluebookcost, mfrcost)
update bestcost set lowestcost = listcost where listcost < lowestcost
update bestcost set lowestcost = bluebookcost where bluebookcost < lowestcost
update bestcost set lowestcost = mfrcost where mfrcost < lowestcost
-
Are you gonna run that statement everytime the rows are inserted/updated?? That's why I suggested the use of a computed column.
-
The way it's working now is I update the four costs first then I ran the code in the previous post to get lowestcost and it seems to be working out pretty well.
-
Are you going to update whole table 4 times every time any single row is updated?
Hard drives on your server not gonna stay long.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply