June 4, 2009 at 8:00 am
Hi,
I need to find minimum value in a two colums but it shouldnt consider 0 while processing.
i have following resultset
Date Name Buy Sell
----------------------------------------
1-1-2008 A 1 4
1-1-2008 A 0 1
1-1-2008 A 2 0
1-1-2008 A 0 0
1-1-2008 B -1 4
1-1-2008 B 0 1
1-1-2008 B 3 6
1-1-2008 B 0 4
I would expect result like:
Date Name Buy Sell
----------------------------------------
1-1-2008 A 1 1
1-1-2008 B -1 1
Thanks in advance.
June 4, 2009 at 10:07 am
Something like this works:
Declare @stocks TABLE([dAte] SMALLDATETIME, [nAme] CHAR(1), Buy INT, sell INT)
INSERT INTO @stocks (
dAte,
[nAme],
Buy,
sell
)
SELECT
'1-1-2008', 'A', 1, 4
UNION ALL
SELECT
'1-1-2008', 'A', 0, 1
UNION ALL
SELECT
'1-1-2008', 'A', 2, 0
UNION ALL
SELECT
'1-1-2008', 'A', 0, 0
UNION ALL
SELECT
'1-1-2008', 'B', -1, 4
UNION ALL
SELECT
'1-1-2008', 'B', 0, 1
UNION ALL
SELECT
'1-1-2008', 'B', 3, 6
UNION ALL
SELECT
'1-1-2008', 'B', 0, 4
SELECT
date,
[name],
MIN(CASE WHEN buy = 0 THEN 100 ELSE Buy End) AS buy,
MIN(CASE WHEN sell = 0 THEN 100 ELSE sell End) AS sell
FROM
@stocks
GROUP BY
date,
[name]
You can replace the constant value with 100 with a more outrageous number if need be.
Note how I provided the test data. It makes helping you easier and faster
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 4, 2009 at 10:19 am
Jack Corbett (6/4/2009)
Something like this works:
Declare @stocks TABLE([dAte] SMALLDATETIME, [nAme] CHAR(1), Buy INT, sell INT)
INSERT INTO @stocks (
dAte,
[nAme],
Buy,
sell
)
SELECT
'1-1-2008', 'A', 1, 4
UNION ALL
SELECT
'1-1-2008', 'A', 0, 1
UNION ALL
SELECT
'1-1-2008', 'A', 2, 0
UNION ALL
SELECT
'1-1-2008', 'A', 0, 0
UNION ALL
SELECT
'1-1-2008', 'B', -1, 4
UNION ALL
SELECT
'1-1-2008', 'B', 0, 1
UNION ALL
SELECT
'1-1-2008', 'B', 3, 6
UNION ALL
SELECT
'1-1-2008', 'B', 0, 4
SELECT
date,
[name],
MIN(CASE WHEN buy = 0 THEN 100 ELSE Buy End) AS buy,
MIN(CASE WHEN sell = 0 THEN 100 ELSE sell End) AS sell
FROM
@stocks
GROUP BY
date,
[name]
You can replace the constant value with 100 with a more outrageous number if need be.
Note how I provided the test data. It makes helping you easier and faster
If you don't mind the the following:
Warning: Null value is eliminated by an aggregate or other SET operation.
You could replace the 100 with null (no quotes around the word).
June 4, 2009 at 10:23 am
Hi and thanks for that, i havent tried , before that i have a doubt. Suppose i have a set which has all 0 like for example
Sell
----
0
0
0
and i use case and replace it with 'x' where x is very large like 99999999 .
so wont i get this number a my minimum ?
sorry if i sound foolish but i m a newbie.
June 4, 2009 at 10:23 am
Or, if you still don't mind the warning above, this would work as well:
Declare @stocks TABLE([Date] SMALLDATETIME, [Name] CHAR(1), Buy INT, Sell INT)
INSERT INTO @stocks (
[Date],
[Name],
Buy,
Sell
)
SELECT
'1-1-2008', 'A', 1, 4
UNION ALL
SELECT
'1-1-2008', 'A', 0, 1
UNION ALL
SELECT
'1-1-2008', 'A', 2, 0
UNION ALL
SELECT
'1-1-2008', 'A', 0, 0
UNION ALL
SELECT
'1-1-2008', 'B', -1, 4
UNION ALL
SELECT
'1-1-2008', 'B', 0, 1
UNION ALL
SELECT
'1-1-2008', 'B', 3, 6
UNION ALL
SELECT
'1-1-2008', 'B', 0, 4;
select * from @stocks;
SELECT
[Date],
[Name],
MIN(nullif(Buy,0)) AS Buy,
MIN(nullif(Sell,0)) AS Sell
FROM
@stocks
GROUP BY
[Date],
[Name]
June 4, 2009 at 10:45 am
Lynn's solution will work, is simpler, and doesn't need an arbitrary number. The only issue is that some UI's don't like the warning so you need to SET ANSI_WARNINGS OFF.
Here's my solution modified to output NULL when you have all 0's:
Declare @stocks TABLE([dAte] SMALLDATETIME, [nAme] CHAR(1), Buy INT, sell INT)
INSERT INTO @stocks (
dAte,
[nAme],
Buy,
sell
)
SELECT
'1-1-2008', 'A', 1, 4
UNION ALL
SELECT
'1-1-2008', 'A', 0, 1
UNION ALL
SELECT
'1-1-2008', 'A', 2, 0
UNION ALL
SELECT
'1-1-2008', 'A', 0, 0
UNION ALL
SELECT
'1-1-2008', 'B', -1, 4
UNION ALL
SELECT
'1-1-2008', 'B', 0, 1
UNION ALL
SELECT
'1-1-2008', 'B', 3, 6
UNION ALL
SELECT
'1-1-2008', 'B', 0, 4
UNION ALL
SELECT
'1-1-2008', 'C', 0, 1
UNION ALL
SELECT
'1-1-2008', 'C', 0, 6
UNION ALL
SELECT
'1-1-2008', 'C', 0, 4
SELECT
date,
[name],
NULLIF(MIN(CASE WHEN buy = 0 THEN 100 ELSE Buy End), 100) AS buy,
NULLIF(MIN(CASE WHEN sell = 0 THEN 100 ELSE sell End), 100) AS sell
FROM
@stocks
GROUP BY
date,
[name]
Here's Lynn's solution with the ANSI_WARNINGS added:
Declare @stocks TABLE([dAte] SMALLDATETIME, [nAme] CHAR(1), Buy INT, sell INT)
INSERT INTO @stocks (
dAte,
[nAme],
Buy,
sell
)
SELECT
'1-1-2008', 'A', 1, 4
UNION ALL
SELECT
'1-1-2008', 'A', 0, 1
UNION ALL
SELECT
'1-1-2008', 'A', 2, 0
UNION ALL
SELECT
'1-1-2008', 'A', 0, 0
UNION ALL
SELECT
'1-1-2008', 'B', -1, 4
UNION ALL
SELECT
'1-1-2008', 'B', 0, 1
UNION ALL
SELECT
'1-1-2008', 'B', 3, 6
UNION ALL
SELECT
'1-1-2008', 'B', 0, 4
UNION ALL
SELECT
'1-1-2008', 'C', 0, 1
UNION ALL
SELECT
'1-1-2008', 'C', 0, 6
UNION ALL
SELECT
'1-1-2008', 'C', 0, 4
SET ANSI_WARNINGS OFF
SELECT
date,
[name],
MIN(NULLIF(Buy, 0)) AS buy,
MIN(NULLIF(sell, 0)) AS sell
FROM
@stocks
GROUP BY
date,
[name]
SET ANSI_WARNINGS ON
You can also set ANSI_WARNINGS OFF in the connection string although I don't recommend it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 4, 2009 at 8:36 pm
Thanks a lot Jack Corbett and Lynn Pettis its worked !!!
i used case with null.
Thanks again 🙂 🙂
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply