Another way to do it is if your criteria may change year to year you can put it
in a table and do a join.
CREATE TABLE AIMSweb_Winter_Tiers (
GRADE CHAR(2) NOT NULL
,SCORESTART INT NOT NULL
,SCOREEND INT NOT NULL
,WINTERTIER INT NOT NULL
);
INSERT INTO AIMSweb_Winter_Tiers (GRADE, SCORESTART, SCOREEND, WINTERTIER)
VALUES
('1', 3, 9999, '1')
,('1', 2, 2, '2')
,('1', 0, 1, '3')
,('2', 9, 9999, '1')
,('2', 5, 8, '2')
,('2', 0, 4, '3')
,...;
Then you can do the update like this:
UPDATE AIMSweb_Winter2014_Skyimport
SET WINTERTIER = TIER.WINTERTIER
FROM AIMSweb_Winter2014_Skyimport as WINTER
ON WINTER.GRADE = TIER.GRADE
AND WINTER.WINTERSCORE BETWEEN TIER.SCORESTART AND TIER.SCOREEND
WHERE GOM = 'MAZE';
Steve
-----Original Message-----
From: Carl Von Stetten [mailto:vonner.li...@vonner.net]
Sent: Tuesday, February 04, 2014 2:08 PM
To: sql
Subject: Re: sql query help
Oooohhhhh! Nice. I had thought about doing something with CASE, but
I've never used CASE with multiple conditions like that before. I
learned me something new today. :-)
-Carl V.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3536
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
