Home Forums SQL Server 2008 T-SQL (SS2K8) Need help with a script to identify count of negative and positive numbers in a record RE: Need help with a script to identify count of negative and positive numbers in a record

  • There is another, more mathematical way:

    If no zero values in your columns:

    select *, 5-Positives as Negatives

    from (select *,(sign(col1)+sign(col2)+sign(col3)+sign(col4)+sign(col5)+5)/2 Positives

    from tablename) sn

    And, if there are zero values:

    select col1, col2, col3, col4, col5

    ,(s1+abs(s1)+s2+abs(s2)+s3+abs(s3)+s4+abs(s4)+s5+abs(s5))/2 as Positive

    ,abs((s1-abs(s1)+s2-abs(s2)+s3-abs(s3)+s4-abs(s4)+s5-abs(s5))/2) as Negative

    from (select *,sign(col1) s1,sign(col2) s2,sign(col3) s3,sign(col4) s4,sign(col5) s5

    from tablename) sn

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]