Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need help with a script to identify count of negative and positive numbers in a record Expand / Collapse
Author
Message
Posted Monday, November 19, 2012 9:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:23 PM
Points: 1,360, Visits: 1,761
Hello All,

I need help with a tsql query. Below is the table structure.

CREATE TABLE [dbo].[TableName](
[col1] [int] NOT NULL,
[col2] [int] NOT NULL,
[col3] [int] NOT NULL,
[col4] [int] NOT NULL,
[col5] [int] NOT NULL
) ON [PRIMARY]

GO


Select 1op 10* from tablename

col1 col2 col3 col4 col5
-15 12 9 5 -7
-3 5 -15 6 -16
10 -14 8 17 -16
-11 -10 5 2 -4
-6 -2 7 -13 8
-4 2 -6 7 19
-10 8 7 16 -14
17 -17 -9 14 18
7 19 15 12 -13
4 -7 -12 -13 -11

I need to get a count of negative numbers and positive in every row on a row by row basis. Thanks for your help in advance.


“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Post #1386448
Posted Monday, November 19, 2012 9:25 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 10:21 AM
Points: 605, Visits: 3,537
SELECT 
CASE WHEN col1 < 0 THEN 1 ELSE 0 END + CASE WHEN col2 < 0 THEN 1 ELSE 0 END + CASE WHEN col3 < 0 THEN 1 ELSE 0 END + CASE WHEN col4 < 0 THEN 1 ELSE 0 END + CASE WHEN col5 < 0 THEN 1 ELSE 0 END AS CountNegs,
CASE WHEN col1 >= 0 THEN 1 ELSE 0 END + CASE WHEN col2 >= 0 THEN 1 ELSE 0 END + CASE WHEN col3 >= 0 THEN 1 ELSE 0 END + CASE WHEN col4 >= 0 THEN 1 ELSE 0 END + CASE WHEN col5 >= 0 THEN 1 ELSE 0 END AS CountPos
FROM TableName

Post #1386455
Posted Monday, November 19, 2012 9:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:23 PM
Points: 1,360, Visits: 1,761
Thanks for your help. It works like a charm

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Post #1386458
Posted Monday, November 19, 2012 10:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,873, Visits: 5,189
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1386486
Posted Monday, November 19, 2012 10:20 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, November 23, 2014 12:50 AM
Points: 3,109, Visits: 11,515
This should work also:
select
abs(sign(col1)+sign(col2)+sign(col3)+
sign(col4)+sign(col5)) as NegativeCount
sign(col1)+sign(col2)+sign(col3)+
sign(col4)+sign(col5)+5 as PositiveCount
from
MyTable

Post #1386488
Posted Monday, November 19, 2012 10:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,873, Visits: 5,189
Michael Valentine Jones (11/19/2012)
This should work also:
select
abs(sign(col1)+sign(col2)+sign(col3)+
sign(col4)+sign(col5)) as NegativeCount
sign(col1)+sign(col2)+sign(col3)+
sign(col4)+sign(col5)+5 as PositiveCount
from
MyTable



Have you tested it? You should try:

CREATE TABLE [dbo].[MyTable](
[col1] [int] NOT NULL,
[col2] [int] NOT NULL,
[col3] [int] NOT NULL,
[col4] [int] NOT NULL,
[col5] [int] NOT NULL
) ON [PRIMARY]

insert MyTable
select 0,-0,0,0,0
union select -3,5,-15,6,0
union select -3,5,-15,6,-16
union select 10,-14,8,17,-16
union select -11,-10,5,2,-4
union select -6,-2,7,-13,8
union select -4,2,-6,7,19
union select -10,8,7,16,-14
union select 17,-17,-9,14,18
union select 7,19,15,12,-13
union select 7,19,15,12,-13

It's not so simple as appears at first glance, so check my version...



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

How to post your question to get the best and quick help
Post #1386492
Posted Monday, November 19, 2012 10:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,873, Visits: 5,189
Actually, I found even more elegant way:


SELECT *
FROM tablename
CROSS APPLY(SELECT SUM(SIGN(c) + ABS(SIGN(c)))/2 AS Positive
,ABS(SUM(SIGN(c) - ABS(SIGN(c)))/2) AS Negative
FROM (VALUES (col1),(col2),(col3),(col4),(col5)) c(c)) calc


... however, I will not be surprised if CASE WHEN outperform all of mathematical-puzzle based.
It requires some testing, but I have no time for this right now


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

How to post your question to get the best and quick help
Post #1386501
Posted Monday, November 19, 2012 6:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
Eugene Elutin (11/19/2012)
Michael Valentine Jones (11/19/2012)
This should work also:
select
abs(sign(col1)+sign(col2)+sign(col3)+
sign(col4)+sign(col5)) as NegativeCount
sign(col1)+sign(col2)+sign(col3)+
sign(col4)+sign(col5)+5 as PositiveCount
from
MyTable



Have you tested it? You should try:

CREATE TABLE [dbo].[MyTable](
[col1] [int] NOT NULL,
[col2] [int] NOT NULL,
[col3] [int] NOT NULL,
[col4] [int] NOT NULL,
[col5] [int] NOT NULL
) ON [PRIMARY]

insert MyTable
select 0,-0,0,0,0
union select -3,5,-15,6,0
union select -3,5,-15,6,-16
union select 10,-14,8,17,-16
union select -11,-10,5,2,-4
union select -6,-2,7,-13,8
union select -4,2,-6,7,19
union select -10,8,7,16,-14
union select 17,-17,-9,14,18
union select 7,19,15,12,-13
union select 7,19,15,12,-13

It's not so simple as appears at first glance, so check my version...



I think you meant to use "union all select" so that the last entry (a duplicate) is retained.

Your next solution gets a +1!



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1386647
Posted Monday, November 19, 2012 9:56 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:39 PM
Points: 2,031, Visits: 2,533
I tried for 6 numbers with the below query.

select   col1, col2, col3, col4, col5, Col6
,(s1+abs(s1)+s2+abs(s2)+s3+abs(s3)+s4+abs(s4)+s5+abs(s5)+ s6+abs(s6))/2 as Positive
,abs((s1-abs(s1)+s2-abs(s2)+s3-abs(s3)+s4-abs(s4)+s5-abs(s5)+ s6 - abs(s6))/2) as Negative
from (select *,sign(col1) s1,sign(col2) s2,sign(col3) s3,sign(col4) s4,sign(col5) s5, sign(Col6) s6
from MyTable) sn

select *, 6-Positives as Negatives 
from (select *,(sign(col1)+sign(col2)+sign(col3)+sign(col4)+sign(col5)+sign(Col6)+ 6)/2 Positives
from MyTable) sn

It works fine.

what is the secret behind this mathematical formula? It would be great if you explain it in detail.

I am always your fan for such a mathematical formula ( The one you used to generate the sequence number by using BIT WISE & operator)


karthik
Post #1386673
Posted Tuesday, November 20, 2012 3:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,873, Visits: 5,189
dwain.c (11/19/2012)
Eugene Elutin (11/19/2012)
[quote]Michael Valentine Jones (11/19/2012)
This should work also:
select

...
union select 7,19,15,12,-13
union select 7,19,15,12,-13

It's not so simple as appears at first glance, so check my version...



I think you meant to use "union all select" so that the last entry (a duplicate) is retained.

Your next solution gets a +1!


No, I really didn't, it was just cut-&-paste, the data variation above is enough for testing functionality of the query...
Thanks for "+1", I like it too




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

How to post your question to get the best and quick help
Post #1386773
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse