checking columns\field value with condition

Question

checking columns\field value with condition

midan1

SSCertifiable

Points: 5100
More actions
January 13, 2008 at 12:59 pm

#178102

hi need help
how to calculate column
need to check how many times i have
in my table each value
and if i miss show it in the last ROW
Select Case When val1=1 count*<2 Else miss('2')
Select Case When val1=1 count*=0 Else miss('2')
Select Case When val1=2 count*<1 Then miss('3')
Select Case When val2=1 count*<2 Else miss('2')
Select Case When val2=1 count*=0 Else miss('2')
Select Case When val2=2 count*<1 Then miss('3')
empid name date val1 val2
------------------------------------------------------------------------------------------
1111 aaaa 01/01/2008 1 0
2222 bbbb 01/01/2008 0 0
3333 cccc 01/01/2008 1 2
4444 dddd 01/01/2008 2 1
5555 eeee 01/01/2008 3 3
6666 ffffff 01/01/2008 2 0
-----------------------
need to get this result
empid name date val1 val2
------------------------------------------------------------------------------------------
1111 aaaa 01/01/2008 1 0
2222 bbbb 01/01/2008 0 0
3333 cccc 01/01/2008 1 2
4444 dddd 01/01/2008 2 1
5555 eeee 01/01/2008 3 3
6666 ffffff 01/01/2008 2 0
#### #### ###### miss(8,_7,_5,_0) miss(4,3,5,_,_)
TNX

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

It's not completely clear what results you are looking for and what the data means from your explanation. Please clarify with a simpler example using the fields you need and the conditions that require you to count or choose values.

midan1 SSCertifiable Points: 5100 More actions · Answer 2

midan1 (1/13/2008)
hi need help
how to calculate column
empid name date val1 val2
------------------------------------------------------------------------------------------
1111 aaaa 01/01/2008 1 0
2222 bbbb 01/01/2008 0 0
3333 cccc 01/01/2008 1 2
4444 dddd 01/01/2008 2 1
5555 eeee 01/01/2008 3 3
6666 ffffff 01/01/2008 2 0
#### #### ###### miss(8,_7,_5,_0) miss(4,3,5,_,_)
TNX

OK TNX

i need to count+calculate field VAL1

see

val1

-----------------

1

0

1

2

3

2

---------------------

count+calculate to a new ROW the result

val2= twice 2,one zero,twice 1,one 3

i need to see warning

if i don'T have

twice 1 than =miss('1')

if i don'T have

twice 2 than =miss('2')

if i don'T have

twice 3 than =miss('3')

i have 0 than =*

thank

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Ok... let's try this one more time and then I'm just going to ignore your posts in the future...

Read this, do this, or forget this...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)