February 20, 2007 at 7:08 pm
AcctNo Month1 Month2 Month3 Highest Lowest
A -1 0 100 100 100
B 10 20 30 30 10
C 50 40 30 50 30
D 100 100 0 100 100
What is the best way to calculate the highest of
Months 1, 2 and 3 (and the lowest). Ignore amounts
that are zero, or negative.
I did this but it is very ugly. The solutions posted here are amazing. Thanks!
February 20, 2007 at 7:32 pm
And what would you like to display if all 3 amounts are <= 0 ?
--Jeff Moden
February 20, 2007 at 7:36 pm
I was setting the default as zero, so it would be zero.
February 20, 2007 at 8:05 pm
This should do it...
--===== Setup a table to test with
CREATE TABLE #MyHead (AcctNo VARCHAR(10), Month1 INT, Month2 INT, Month3 INT)
INSERT INTO #MyHead (AcctNo, Month1, Month2, Month3)
SELECT 'A', -1, 0, 100 UNION ALL
SELECT 'B', 10, 20, 30 UNION ALL
SELECT 'C', 50, 40, 30 UNION ALL
SELECT 'D', 100, 100, 0
--===== Demonstrate the solution
SELECT t.AcctNo, t.Month1, t.Month2, t.Month3,
ISNULL(MIN(d.MonthData),0) AS Lowest,
ISNULL(MAX(d.MonthData),ISNULL(MIN(d.MonthData),0)) AS Highest
FROM #MyHead t,
(
SELECT AcctNo,Month1 AS MonthData
FROM #MyHead
WHERE Month1 > 0
UNION ALL
SELECT AcctNo,Month2 AS MonthData
FROM #MyHead
WHERE Month2 > 0
UNION ALL
SELECT AcctNo,Month3 AS MonthData
FROM #MyHead
WHERE Month3 > 0
) d
WHERE t.AcctNo = d.AcctNo
GROUP BY t.AcctNo, Month1, Month2, Month3
--Jeff Moden
February 21, 2007 at 2:25 am
Thanks, very creative ans simple to understand.
February 21, 2007 at 9:21 am
Yes, a simple solution with only one flaw...
What if none of the value in the month columns are >0 ?
Try this test data
--===== Setup a table to test with
CREATE TABLE #MyHead (AcctNo VARCHAR(10), Month1 INT, Month2 INT, Month3 INT)
INSERT INTO #MyHead (AcctNo, Month1, Month2, Month3)
SELECT 'A', -1, 0, 100 UNION ALL
SELECT 'B', 10, 20, 30 UNION ALL
SELECT 'C', 50, 40, 30 UNION ALL
SELECT 'D', 100, 100, 0 UNION ALL
SELECT 'E', -1, -1, -1
Have a look at the 'E' AcctNo
However, if you change the CROSS JOIN to a left join, it should work
--===== Demonstrate the solution
SELECT t.AcctNo, t.Month1, t.Month2, t.Month3,
ISNULL(MIN(d.MonthData),0) AS Lowest,
ISNULL(MAX(d.MonthData),ISNULL(MIN(d.MonthData),0)) AS Highest
FROM #MyHead t
LEFT JOIN (
SELECT AcctNo,Month1 AS MonthData
FROM #MyHead
WHERE Month1 > 0
UNION ALL
SELECT AcctNo,Month2
FROM #MyHead
WHERE Month2 > 0
UNION ALL
SELECT AcctNo,Month3
FROM #MyHead
WHERE Month3 > 0
) d on t.AcctNo = d.AcctNo
GROUP BY t.AcctNo, Month1, Month2, Month3
DROP
TABLE #MyHead
N 56°04'39.16"
E 12°55'05.25"
February 21, 2007 at 5:10 pm
Thanks, SW...
Peter is correct, though... if all of the values for a given account number are <= 0, the account won't print (dunno if you want that or not). Peter's code fixes that (the Left Outer Join is the key). To have it in the same format as I did before, I've incorporated his fix in the following...
--===== Setup a table to test with
CREATE TABLE #MyHead (AcctNo VARCHAR(10), Month1 INT, Month2 INT, Month3 INT)
INSERT INTO #MyHead (AcctNo, Month1, Month2, Month3)
SELECT 'A', -1, 0, 100 UNION ALL
SELECT 'B', 10, 20, 30 UNION ALL
SELECT 'C', 50, 40, 30 UNION ALL
SELECT 'D', 100, 100, 0 UNION ALL
SELECT 'E', -1, -1, -1 UNION ALL
SELECT 'F', 0, 0, 0
--===== Demonstrate the solution
SELECT t.AcctNo, t.Month1, t.Month2, t.Month3,
ISNULL(MIN(d.MonthData),0) AS Lowest,
ISNULL(MAX(d.MonthData),ISNULL(MIN(d.MonthData),0)) AS Highest
FROM #MyHead t
LEFT OUTER JOIN
(
SELECT AcctNo,Month1 AS MonthData
FROM #MyHead
WHERE Month1 > 0
UNION ALL
SELECT AcctNo,Month2 AS MonthData
FROM #MyHead
WHERE Month2 > 0
UNION ALL
SELECT AcctNo,Month3 AS MonthData
FROM #MyHead
WHERE Month3 > 0
) d
ON t.AcctNo = d.AcctNo
GROUP BY t.AcctNo, Month1, Month2, Month3
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply