I have a table with the following information

col1, col2, part, desc, cost, retail, quant.

The destination table has the following:

id, part, desc, retail, sale, quant, cost, col1 and col2

What I need to do is update or add to the destination table with the
following info.

Where Retail > 10.00 and col1 = P then Sale = 0.00
Where quant is not null then Sale = 0.00
Where the Greater of (cost * 1.2) or (retail * .8) = sale
then the rest of the data is a stright copy.

How can I do this in one query?

Dennis

it's gonna be something like this

select sales = if((retail > 10 and col1 = P) or (quant is not null),
sales=0, if((cost * 1.2) > (retail *.8), cost * 1.2, retail * 0.8)



--
Eric Li
SQL DBA
MCDBA

Dennis Burgess wrote:

Thank you very much.. Let me get this stright.

So we are selecting if retal is less than 10, and col1 = P. I see that. Or
if quantiy is not nuall. Then sales =0 K.

I don't see how it is selecting the greater of the two. Maye I am not
reading it right ..

Again, thank you VERY VERY much!

Dennis

"Eric.Li" <anonymous (AT) microsoftnews (DOT) org> wrote

The if clause works like this

if (condition, return this if condition is true, return this if
condition is faluse)

in your case, sales = 0 if retail > 0 and col1 = P or quant is not null,
if this condition is true, then sales = 0, otherwise, it goes to the
second if statement, which test if cost * 1.2 > retail * 0.8, if true,
then it returns cost * 1.2, otherwise retail * 0.8.

hope it makes sense to you

--
Eric Li
SQL DBA
MCDBA

Dennis Burgess wrote:

AHHHHHHHHHHHHHHHHHHHHHH

And belive me, I did say that out loud! ha ha. .

Thank you VERY MUCH!

Dennis

"Eric.Li" <anonymous (AT) microsoftnews (DOT) org> wrote

Oops, not if, should be case clause

also, you should check the BOL on case, if you directly copy my code,
you will get syntax error


oh well, let me just write it out for you


select sales =
case when (retail > 10 and col1 = P) or (quant is not null)
then
sales=0
else
case when (cost * 1.2) > (retail *.8)
then
cost * 1.2
else
retail * 0.8
end
end

hope I didn't mess up, but you know, it's Friday afternoon, I can't
think straight Friday after 12

--
Eric Li
SQL DBA
MCDBA

Eric.Li wrote:

ARG.. nothen. I can't get this to work..


"Eric.Li" <anonymous (AT) microsoftnews (DOT) org> wrote

On Tue, 8 Jun 2004 11:07:15 -0500, Dennis Burgess wrote:

Hi Dennis,

You don't say what goes wrong. Am I guessing correct that SQL Server finds
the syntax to be incorrect near '='?

I think that Eric accidentally typed one 'sales=' too many.


select sales =
case when (retail > 10 and col1 = P) or (quant is not null)
then
0 -- <== This line is changed!!!
else
case when (cost * 1.2) > (retail *.8)
then
cost * 1.2
else
retail * 0.8
end
end

(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)