Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculated Columns based on multiple columns

Author  Topic 

Andrew4789
Starting Member

3 Posts

Posted - 2014-08-12 : 19:38:12
MS SQL 2008 R2

I have the following effectively random numbers in a table:

n1,n2,n3,n4,SCORE
1,2,5,9,i
5,20,22,25,i
6,10,12,20,i

I'd like to generate the calculated column SCORE based on various scenarios in the other columns. eg.

if n1<10 and n2<10 then i=i + 1
if n4-n3=1 then i=i + 1
if more than 2 consecutive numbers then i=i + 1

So, I need to build the score. I've tried the procedure below and it works as a pass or fail but is too limiting. I'd like something that increments the variable @test1.

declare @test1 int
set @test1=0
select top 10 n1,n2,n3,n4,n5,n6,
case when (
n1=2 and
n2>5
)
then @test1+1
else @test1
end as t2

from
allNumbers

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-12 : 21:30:12
Select @test = @test+1 ....the rest of your query
Go to Top of Page

Andrew4789
Starting Member

3 Posts

Posted - 2014-08-27 : 21:54:39
Thanks so much, and sorry for the delay in replying. Are you able to explain where I put your piece of code into my query so I can increment the variable?

Select @test = @test+1 ....the rest of your query

Kind regards
Andrew
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-28 : 00:44:48
quote:
if n1<10 and n2<10 then i=i + 1
if n4-n3=1 then i=i + 1
if more than 2 consecutive numbers then i=i + 1
1. Consecutive based on what?
2. What will happen with SCORE when none of the rules above apply?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Andrew4789
Starting Member

3 Posts

Posted - 2014-08-31 : 21:33:27
I need a way to generate a calculated score column based on column values; the actual business rules are irrelevant?

A simple example:
Data

1,3,6,8,9,10,i
6,2,9,12,15,21,i

If n1<5 then i=i+1
If n2=3 then i=i+1

The query, with the calculated column "i" should return:

1,3,6,8,9,10,2
6,3,9,12,15,21,1

Andrew



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-01 : 05:08:04
[code]
UPDATE dbo.Table1
SET i = CASE
WHEN n1 < 10 AND n2 < 10 THEN i + 1
WHEN n1 < 5 THEN i + 1
WHEN n1 = 3 THEN i + 1
WHEN n4 - n3 = 1 THEN i + 1
ELSE i
END
[/code]



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -