Post reply

Calc Highest and Lowest

  • February 20, 2007 at 7:08 pm

    #122998

    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

    #690791

    And what would you like to display if all 3 amounts are <= 0 ?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 20, 2007 at 7:36 pm

    #690792

    I was setting the default as zero, so it would be zero.

  • February 20, 2007 at 8:05 pm

    #690798

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 21, 2007 at 2:25 am

    #690842

    Thanks, very creative ans simple to understand.

  • February 21, 2007 at 9:21 am

    #690957

    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

    #691047

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply