SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculated Columns based on multiple columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Andrew4789
Starting Member

New Zealand
3 Posts

Posted - 08/12/2014 :  19:38:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1183 Posts

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

Andrew4789
Starting Member

New Zealand
3 Posts

Posted - 08/27/2014 :  21:54:39  Show Profile  Reply with Quote
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

Sweden
30277 Posts

Posted - 08/28/2014 :  00:44:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

New Zealand
3 Posts

Posted - 08/31/2014 :  21:33:27  Show Profile  Reply with Quote
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

Sweden
30277 Posts

Posted - 09/01/2014 :  05:08:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote

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




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000