Author |
Topic |
Andrew4789
Starting Member
3 Posts |
Posted - 2014-08-12 : 19:38:12
|
MS SQL 2008 R2I have the following effectively random numbers in a table:n1,n2,n3,n4,SCORE1,2,5,9,i5,20,22,25,i6,10,12,20,iI'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 + 1if n4-n3=1 then i=i + 1if more than 2 consecutive numbers then i=i + 1So, 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 intset @test1=0select top 10 n1,n2,n3,n4,n5,n6,case when (n1=2 andn2>5)then @test1+1 else @test1end as t2fromallNumbers |
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-12 : 21:30:12
|
Select @test = @test+1 ....the rest of your query |
|
|
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 queryKind regardsAndrew |
|
|
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 + 1if n4-n3=1 then i=i + 1if 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 |
|
|
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:Data1,3,6,8,9,10,i6,2,9,12,15,21,iIf n1<5 then i=i+1If n2=3 then i=i+1The query, with the calculated column "i" should return:1,3,6,8,9,10,26,3,9,12,15,21,1Andrew |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-09-01 : 05:08:04
|
[code]UPDATE dbo.Table1SET 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 |
|
|
|
|
|