Professional Web Applications Themes

More evidence of brain-damage... - Microsoft SQL / MS SQL Server

greetings - i have spent too much time in the dimly-lit backroom working on old xbase-programs. i now would like to use the function i used to know as MIN(a,b,c) to retrieve the smaller of a list of values. T-Sql merely smirks at me and points out that MIN(column) will give me the smallest value from a named column. (stop that chuckling out there, you lot). what function or method will give me what i so dearly desire? i would like to say MIN((normalhrs * hour_rate) * 26), 200000) and be told which of the two values is smaller.... TIA ...

  1. #1

    Default More evidence of brain-damage...

    greetings - i have spent too much time in the dimly-lit backroom working on
    old xbase-programs. i now would like to use the function i used to know as
    MIN(a,b,c) to retrieve the smaller of a list of values. T-Sql merely smirks
    at
    me and points out that MIN(column) will give me the smallest value from a
    named column. (stop that chuckling out there, you lot).
    what function or method will give me what i so dearly desire? i would like
    to
    say MIN((normalhrs * hour_rate) * 26), 200000) and be told which of the
    two values is smaller....
    TIA
    b clark at lrgh dot org


    Bill Guest

  2. #2

    Default Re: More evidence of brain-damage...

    Try

    case (normalhrs * hour_rate) * 26 < 200000 then (normalhrs * hour_rate) *
    26 else 200000

    Jun Fang

    --
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Use of included script samples are subject to the terms specified at
    http://www.microsoft.com/info/cpyright.htm.


    "Bill Clark" <bclark_at_lrgh.org> wrote in message
    news:net... 
    on 
    smirks 


    SQL Guest

  3. #3

    Default Re: More evidence of brain-damage...

    There is no inbuilt function in T-SQL which allows you to do this directly.
    You can use CASE expressions like:

    SELECT CASE WHEN a > b THEN a ELSE b END AS "Max"

    If there are three values, you'll have to change with nested CASEs or
    multiple IF...ELSE.. accordingly. If there is a bunch of values, you can
    create a fake table & do :

    SELECT MAX(col)
    FROM ( SELECT 'a' UNION
    SELECT 'b' UNION
    SELECT 'c' ...) D (col) ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  4. #4

    Default Re: More evidence of brain-damage...

    In addition to what Anith has posted, you may want to return NULL if one of
    your values is NULL. That's how XBase (at least FoxPro) works. If you need
    to propagate NULL values here's an example:

    CREATE TABLE SomeValues (X INTEGER PRIMARY KEY, Y INTEGER NULL, Z INTEGER
    NULL)

    INSERT INTO SomeValues VALUES (1,2,3)
    INSERT INTO SomeValues VALUES (2,1,3)
    INSERT INTO SomeValues VALUES (3,2,NULL)

    SELECT *,
    (SELECT MIN(v)
    FROM (SELECT X AS v UNION ALL SELECT Y UNION ALL SELECT Z) m
    HAVING COUNT(v)=COUNT(*) -- Result only if all values are non-NULL
    )
    AS minimum,
    (SELECT MAX(v)
    FROM (SELECT X AS v UNION ALL SELECT Y UNION ALL SELECT Z) m
    HAVING COUNT(v)=COUNT(*) -- Result only if all values are non-NULL
    )
    AS maximum
    FROM SomeValues

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Bill Clark" <bclark_at_lrgh.org> wrote in message
    news:net... 
    on 
    smirks 


    David Guest

  5. #5

    Default Re: More evidence of brain-damage...

    greetings - and an extra large 'Thanks' for all responses. that is just
    what i was looking for.
    bc

    "Bill Clark" <bclark_at_lrgh.org> wrote in message
    news:net... 
    on 
    smirks 


    BillC Guest

Similar Threads

  1. Fw: evidence john bokma al jazeera programmer ?
    By William Baker in forum PERL Modules
    Replies: 6
    Last Post: May 23rd, 07:48 AM
  2. File Damage
    By Tim Booth in forum FileMaker
    Replies: 1
    Last Post: July 19th, 02:26 PM
  3. [Q]How to minimize damage done by Java
    By Martin Drautzburg in forum Oracle Server
    Replies: 3
    Last Post: December 22nd, 01:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139