Monday 7 January 2013

Genenerating unique key for 3 columns

Using concatenation of three columns

CREATE TABLE myTable( f1 varchar(10), f2 varchar(10), f3 varchar (10), Keycol as f1+f2+f3)
GO

insert into myTable values(111, 222, 333);
insert into myTable values(111, 222, 333);
insert into myTable values(aaa, bbb, ccc);
insert into myTable values(aaa, bbb, ddd);
insert into myTable values(aaa, bbb, ccc);
insert into myTable values(111, 222, 333);

Result:

f1         f2         f3         KeyCol
-----------------------------------------
111      222      333      111222333
111      222      333      111222333
aaa      bbb     ccc       aaabbbccc
aaa      bbb     ddd     aaabbbddd
aaa      bbb     ccc       aaabbbccc
111      222      333      111222333

Using Checksum function


CREATE TABLE myTable( f1 varchar(10), f2 varchar(10), f3 varchar (10), Keycol as Checksum(f1+f2+f3));

insert into myTable values(111, 222, 333);
insert into myTable values(111, 222, 333);
insert into myTable values(aaa, bbb, ccc);
insert into myTable values(aaa, bbb, ddd);
insert into myTable values(aaa, bbb, ccc);
insert into myTable values(111, 222, 333);

Result:

f1         f2         f3         KeyCol
-----------------------------------------
111      222      333      1239501031
111      222      333      1239501031
aaa      bbb     ccc       2125954685
aaa      bbb     ddd     2125692521
aaa      bbb     ccc       2125954685
111      222      333      1239501031

  
Creating view for the same..

CREATE TABLE myTable( f1 varchar(10), f2 varchar(10), f3 varchar (10));

insert into myTable values(111, 222, 333);

CREATE VIEW v1 as select f1,f2,f3, Checksum(f1+f2+f3) as Keycol from myTable;
SELECT * FROM V1;

f1         f2         f3         KeyCol
-----------------------------------------
111      222      333      1239501031


Sum of digits in a number is Even or Odd?

Using While loop

DECLARE @num varchar(70), @len int, @sum int;
set @num = '9075453574568458765456786586578567856785677548964154022106422' + '0'
set @len = LEN(@num )
set @sum = 0
 while(@len>-1)
   begin
      set @sum = @sum + convert (int, RIGHT(@num ,1))
      set   @num = left(@num ,@len)
      set   @len -= 1
   end
select (case @sum%2 whenthen 'ODD' Else 'EVEN' end) as Oddity
GO


Using CTE (Recursive logic)

With NumOddity(cnt,dig,num)
as
(
select 1,0,'9075453574568458765456786586578567856785677548964154022106422'
union all
select  cnt+1,convert (int,SUBSTRING(num,LEN(num)+1-cnt,1)), num from   NumOddity where NumOddity.cnt < LEN(num)+1
)
select case (SUM(dig))%2 when 0 then 'EVEN' else 'ODD' End as Oddity from NumOddity

Using Replace function

Based upon the rules

Even  + Even = Even
Even  + Odd  = Odd
Odd   + Odd  = Even

1)  Remove all Even digits from the given number, gives the number containing only Odd digits in the number
2)  Since,   sum of Odd digits is ‘Even’    (If count of the digits is Even)
sum of Odd digits is ‘Odd’     (If count of the digits is Odd)
Ex:
Given number:     9075453574568458765456786586578567856785677548964154022106422
Resulting number, after removing all Evens(0,2,4,6,8) is 9755357557557557575757759151
Count of all digits in the number 9755357557557557575757759151 is, 28, which is Even. So, the sum of the digits in the number becomes Even

select      case len(Replace(Replace(Replace(Replace(Replace('9075453574568458765456786586578567856785677548964154022106422','2',''),'4',''),'6',''),'8',''),'0',''))%2
            when 0 then 'Even' Else 'Odd' End as Oddity