Finding the least of three is working great, but how do I save the value to my table for each row?

DECLARE @LeastOf3 numeric BEGIN TRAN SELECT Item#, ID, Market, LiFo, Wgtd_Avg, ( CASE WHEN Market = Wgtd_Avg and Wgtd_Avg = LiFo THEN Market WHEN Lifo = 0 and Wgtd_Avg = 0 and Market <> 0 THEN Market WHEN Market < LiFo AND Market < Wgtd_Avg AND Market <> 0 THEN Market WHEN Market <= Wgtd_Avg AND LiFo = 0 THEN Market WHEN Market <= LiFo AND Wgtd_Avg = 0 THEN Market WHEN Market = 0 and Wgtd_Avg = 0 and LiFo <> 0 THEN LiFo WHEN LiFo < Market AND LiFo < Wgtd_Avg AND LiFo <> 0 THEN LiFo WHEN LiFo <= Market AND Wgtd_Avg = 0 THEN LiFo WHEN LiFo <= Wgtd_Avg AND Market = 0 THEN LiFo WHEN Market = 0 and LiFo = 0 and Wgtd_Avg <> 0 THEN Wgtd_Avg WHEN Wgtd_Avg < Market and Wgtd_Avg < LiFo and Wgtd_Avg <> 0 THEN Wgtd_Avg WHEN Wgtd_Avg <= Market AND LiFo = 0 THEN Wgtd_Avg WHEN Wgtd_Avg <= LiFo AND Market = 0 THEN Wgtd_Avg WHEN Market <= LiFo and LiFo < Wgtd_Avg and Market <> 0 THEN Market WHEN LiFo <= Market and Market < Wgtd_Avg and LiFo <> 0 THEN LiFo WHEN Wgtd_Avg <= LiFo and LiFo < Market and Wgtd_Avg <> 0 THEN Wgtd_Avg ELSE 0 END ) AS LeastOf3 FROM VF_CasINV_Cost where (CalendarYear = 2010) and (Item# < 99999990) --SET LeastOfThree = LeastOf3 --UPDATE VF_CasINV_Cost --SET LeastOfThree = @LeastOf3 --where (CalendarYear = 2010) AND (Item# = Item#) AND (ID = ID) and (Item# < 99999990) --go

--------------Solutions-------------

use an UPDATE FROM statement, sort of like this:

UPDATE VF_CasINV_Cost
SET LeastOfThree = LeastOf3
FROM
SELECT (... your big select statement...)

You just need to include your CASE in the UPDATE statement and not the local variable you were trying to use, like:

UPDATE VF_CasINV_Cost
SET LeastOfThree = --<<your big case here
where (CalendarYear = 2010) AND (Item# = Item#) AND (ID = ID) and (Item# < 99999990)

so this is the final code:

UPDATE VF_CasINV_Cost
SET LeastOfThree = CASE
WHEN Market = Wgtd_Avg and Wgtd_Avg = LiFo THEN Market

WHEN Lifo = 0 and Wgtd_Avg = 0 and Market <> 0 THEN Market
WHEN Market < LiFo AND Market < Wgtd_Avg AND Market <> 0 THEN Market
WHEN Market <= Wgtd_Avg AND LiFo = 0 THEN Market
WHEN Market <= LiFo AND Wgtd_Avg = 0 THEN Market

WHEN Market = 0 and Wgtd_Avg = 0 and LiFo <> 0 THEN LiFo
WHEN LiFo < Market AND LiFo < Wgtd_Avg AND LiFo <> 0 THEN LiFo
WHEN LiFo <= Market AND Wgtd_Avg = 0 THEN LiFo
WHEN LiFo <= Wgtd_Avg AND Market = 0 THEN LiFo

WHEN Market = 0 and LiFo = 0 and Wgtd_Avg <> 0 THEN Wgtd_Avg
WHEN Wgtd_Avg < Market and Wgtd_Avg < LiFo and Wgtd_Avg <> 0 THEN Wgtd_Avg
WHEN Wgtd_Avg <= Market AND LiFo = 0 THEN Wgtd_Avg
WHEN Wgtd_Avg <= LiFo AND Market = 0 THEN Wgtd_Avg

WHEN Market <= LiFo and LiFo < Wgtd_Avg and Market <> 0 THEN Market
WHEN LiFo <= Market and Market < Wgtd_Avg and LiFo <> 0 THEN LiFo
WHEN Wgtd_Avg <= LiFo and LiFo < Market and Wgtd_Avg <> 0 THEN Wgtd_Avg

ELSE 0
END
where (CalendarYear = 2010) AND (Item# = Item#) AND (ID = ID) and (Item# < 99999990)

I tried to make your statement shorter

UPDATE VF_CasINV_Cost SET LeastOfThree =
(select coalesce(min(low), 0) from
(select market low where market > 0 union all
select wgtd where wgtd > 0 union all
select lifo where lifo > 0 union all select null) a
)
WHERE CalendarYear = 2010

This proves it works

declare @t table (LeastOfThree int, market int, wgtd int, lifo int)

insert @t values (null, 1,2,0)

UPDATE @t SET LeastOfThree =
(select coalesce(min(low), 0) from
(select market low where market > 0 union all
select wgtd where wgtd > 0 union all
select lifo where lifo > 0 union all select null) a
)
select * from @t

Category:tsql Time:2011-07-12 Views:1
Tags: tsql

Related post

  • What is unit testing? 2008-08-04

    I saw many questions asking 'how' to unit test in a specific language, but no question asking 'what', 'why', and 'when'. What is it? What does it do for me? Why should I use it? When should I use it (also when not)? What are some common pitfalls and

  • Evidence for sealed class performance benefit 2008-08-05

    I have come across a lot of optimization tips which say that you should mark your classes as sealed to get extra performance benefits. I ran some tests to check the performance differential and found none. Am I doing something wrong? Am I missing the

  • How to calculate Big O notation from piece of code 2008-08-06

    Most people with a degree in CS will certainly know what Big O stands for. It helps us to measure how (in)efficient an algorithm really is and if you know in what category the problem you are trying to solve lays in you can figure out if it is still

  • DISCUSSION - Uploading and Storing Mass Images 2008-08-06

    So I'm using an app that stores images heavily in the DB. What's your outlook on this? I'm more of a type to store the location in the filesystem, than store it directly in the DB. What do you think are the pros/cons? --------------Solutions---------

  • storing files to a database or just to the local hard-disk? 2008-08-06

    So I'm using an app that stores images heavily in the DB. What's your outlook on this? I'm more of a type to store the location in the filesystem, than store it directly in the DB. What do you think are the pros/cons? --------------Solutions---------

  • How do I retrieve my MySQL username and password? 2008-08-07

    I lost my MySQL username and password. How do I retrieve it? --------------Solutions------------- Stop the MySQL process. Start the MySQL process with the --skip-grant-tables option. Start the MySQL console client with the -u root option. List all th

  • Drop all tables whose names begin with a certain string 2008-08-07

    I'd like a script to drop all tables whose name begins with a given string. I'm sure this can be done with some dynamic sql and the INFORMATION_SCHEMA tables. If anyone has a script, or can knock one up quickly, please post it. If no-one posts an ans

  • How do I update Ruby Gems from behind a Proxy (ISA-NTLM) 2008-08-07

    Firewall I'm behind is running Microsoft ISA server (NTLM only mode), anyone have success getting their Ruby gems to install/update via Ruby SSPI gem or other method? ... or am I just being lazy? Edit: rubysspi-1.2.4 does not work UPDATE: This also w

  • ruby script/plugin discover RuntimeError 2008-08-07

    Firewall I'm behind is running Microsoft ISA server (NTLM only mode), anyone have success getting their Ruby gems to install/update via Ruby SSPI gem or other method? ... or am I just being lazy? Edit: rubysspi-1.2.4 does not work UPDATE: This also w

  • Vista or XP for Dev Machine 2008-08-07

    I am about to get a new PC from work, and it will include the option to have either Vista Business as the OS, or a downgrade to XP Pro. Aside from a tiny bit of testing, I have never used Vista, but overall I have heard many more bad reports than goo

  • DataTable Loop Performance Comparison 2008-08-07

    Which of the following has the best performance? I have seen method two implemented in JavaScript with huge performance gains, however, I was unable to measure any gain in C# and was wondering if the compiler already does method 2 even when written l

  • Video Compression: What is discrete cosine transform? 2008-08-07

    I've implemented an image/video transformation technique called discrete cosine transform. This technique is used in MPEG video encoding. I based my algorithm on the ideas presented at the following URL: http://vsr.informatik.tu-chemnitz.de/~jan/MPEG

  • How can I execute dynamic SQL in c#? 2008-08-07

    I can do an eval("something()"); to execute the code dynamically in JavaScript. Is there a way for me to do the same thing in C#? What I am exactly trying to do is that I have an integer variable (say i) and I have multiple properties by the names: "

  • C# eval code from string 2008-08-07

    I can do an eval("something()"); to execute the code dynamically in JavaScript. Is there a way for me to do the same thing in C#? What I am exactly trying to do is that I have an integer variable (say i) and I have multiple properties by the names: "

  • Learning Regular Expressions 2008-08-07

    I don't really understand regular expressions. Can you explain them to me in an easy-to-follow manner? If there are any online tools or books, could you also link to them? --------------Solutions------------- The most important part is the concepts.

  • How much database performance overhead when using LINQ? 2008-08-07

    How much database performance overhead is involved with using C# and LINQ compared to custom optimized queries loaded with mostly low-level C, both with a SQL Server 2008 backend? I'm specifically thinking here of a case where you have a fairly data-

  • What is called a Node in a WebSpere Network Deployment 2008-08-07

    In a installation of WebSphere Application Server with Network Deployment, a node is: a physical machine an instance of operative system a logical set of WAS instances that is independent of physical machine or OS instance --------------Solutions----

  • Javascript keyboard events primer? (or rather: help me with my custom dropdown) 2008-08-07

    I need help finishing my custom built ajax [div] based dynamic dropdown. Basically, I have an [input] box which; onkeyup, runs an Ajax search which returns a bunch of results in divs and are drawn back in using innerHTML. These divs all have highligh

  • Should I use the username, or the user's ID to reference authenticated users in ASP.NET 2008-08-07

    So in my simple learning website, I use the built in ASP.NET authentication system. I am adding now a user table to save stuff like his zip, DOB etc'. My question is: In the new table, should the key be the user name (the string) or the user ID which

  • Setting a div's height in HTML with CSS 2008-08-07

    I am a CSS newbie trying to layout a table-like page with two columns. I want the rightmost column to dock to the right of the page, and this column should have a distinct background color. The content in the right side is almost always going to be s

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.404 (s). 13 q(s)