small logic required - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

small logic required

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-11-25 : 01:44:11

Hi,

Have a small requirement. I have a table with two columns and i require the below expected output.

create table test1
(c1 int,
c2 int
)

insert into test1
select 1,2
union all
select 2,3
union all
select 6,8
union all
select 7,7
union all
select 8,8
union all
select 4,9
union all
select 9,9

select c1,c2 from test1

For the first row the value for c3 should start from 1.
For the next record, we need compare c1 and c2 columns.

The logic is as follows
Case 1
-------
If the c1 == c2 then we need to retain the previous c3 value.

Case 2
--------
if c1 != c3 then we need to increment the previous value + 1 and
display it as C3 column.

Case 3
-------
a.In the first record itself C1 == C2 i.e equal then keep 0 for C3 column in the ouput.
b. if the first record itself C1 ! = 2 then keep 1 as for C3 column in the ouput.

For every record we need, keep the previous record intact.

Expected output
===============
C1 C2 C3
1 2 1
2 3 2
6 8 3
7 7 3 -- bcz C1 and C2 is equal
8 8 3 -- bcz C1 and C2 is equal
4 9 4
9 9 4
7 2 5

Any help would be greatly appreciated.

Thanks in Advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-27 : 02:48:34

[code]
DECLARE @Cnt int
SET @Cnt=0

UPDATE table
SET @Cnt=C3=CASE WHEN C1 != C2 THEN @Cnt+1 ELSE @Cnt END
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources