Professional Web Applications Themes

How can I use case statement in my case, if I can?? - Microsoft SQL / MS SQL Server

Table structures are: Table1 Key,Name,Description,Units..... 1,'A','X1Y1Z1',.... 2,'B','X2Y2Z2',.... 3,'C','X3Y3Z3',.... 4,'D',X4Y4Z4',... etc... Table2 Key,Datetime,Value 1,'2003-08-01 00:00:00',23.23 1,'2003-08-02 00:00:00',-3.0 1,'2003-08-03 00:00:00;,-4.34 ...... 2,'2003-08-01 00:00:00',123.45 2,'2003-08-02 00:00:00',134.32 2,'2003-08-03 00:00:00',142.23 ....... 3,'2003-08-01 00:00:00',67.89 3,'2003-08-02 00:00:00',66.53 ...... Similarly for other keys associated with names I need a query for something like For a given datetime, i.e. datetime >= '2003-08-01 00:00:00' and datetime <= '2003-08-04 23:59:00' If A > 0 then Value = 40 Else Value = B+C So the result set should have 2 columns Datetime Value 2003-08-01 00:00:00 B+C 2003-08-02 00:00:00 40 (because A < 0) 2003-08-03 00:00:00 B+C .... .... Thanks Ricky...

  1. #1

    Default How can I use case statement in my case, if I can??

    Table structures are:

    Table1

    Key,Name,Description,Units.....
    1,'A','X1Y1Z1',....
    2,'B','X2Y2Z2',....
    3,'C','X3Y3Z3',....
    4,'D',X4Y4Z4',...
    etc...

    Table2
    Key,Datetime,Value
    1,'2003-08-01 00:00:00',23.23
    1,'2003-08-02 00:00:00',-3.0
    1,'2003-08-03 00:00:00;,-4.34
    ......
    2,'2003-08-01 00:00:00',123.45
    2,'2003-08-02 00:00:00',134.32
    2,'2003-08-03 00:00:00',142.23
    .......
    3,'2003-08-01 00:00:00',67.89
    3,'2003-08-02 00:00:00',66.53
    ......
    Similarly for other keys associated with names

    I need a query for something like

    For a given datetime, i.e. datetime >= '2003-08-01
    00:00:00' and datetime <= '2003-08-04 23:59:00'
    If A > 0 then
    Value = 40
    Else
    Value = B+C

    So the result set should have 2 columns

    Datetime Value
    2003-08-01 00:00:00 B+C
    2003-08-02 00:00:00 40 (because A < 0)
    2003-08-03 00:00:00 B+C
    ....
    ....

    Thanks

    Ricky

    Ricky Guest

  2. #2

    Default Re: How can I use case statement in my case, if I can??

    select
    dtval as 'datetime',
    cast ( case when dtval >= '20030801' and dtval < '20030805' and A > 0 then b
    + c else 40 end as int) as value
    from table1 inner join table2 on table1.key = table2.key

    Note that you usually get better answers when you post actual DDL. I have
    no way of determining what A refers to in your description of the query and
    results. Looks like it refers to a value in a column in table1 but that
    column appears to be character based and therefore cannot be "> 0".

    Btw, you should consider carefully your use of datetime constants. I've
    changed the upper boundary to more accurately reflect all datetime values
    that can exist on the date of Aug 4 2003. Your use of 23:59:00 prevents
    the inclusion of any datetime values that fall in the very last minute of
    the day (e.g., 23:59:59.997 is the last possible value that can be stored as
    a datetime before the next day begins). OTOH, you might have intended this.

    "Ricky" <state.mn.us> wrote in message
    news:00af01c3627c$22d5ce90$gbl... 


    Scott Guest

  3. #3

    Default Re: How can I use case statement in my case, if I can??

    SELECT DateTime,
    CASE A
    WHEN 0 THEN 40
    ELSE B + C
    END AS MyValue
    FROM ...

    Russell Fields
    "Ricky" <state.mn.us> wrote in message
    news:00af01c3627c$22d5ce90$gbl... 


    Russell Guest

  4. #4

    Default Re: How can I use case statement in my case, if I can??

    Sorry, I missed the point originally by being too simple minded. :(

    However:

    You are not returning the key, so I don't know if it is relevant. Are you
    summing the B+C?

    In the data you show two rows for 2003-08-02. One is below 0 and the other
    above, which you interpret to be a single return row with a value of 40.

    However, for 2003-08-03 one is below 0 and the other above, but you are
    returning B+C.

    So, I am afraid that I don't understand the rules. Pardon my obtuseness.

    Russell Fields


    "Russell Fields" <com> wrote in message news:... 
    >
    >[/ref]


    Russell Guest

Similar Threads

  1. Replies: 112
    Last Post: December 9th, 05:46 PM
  2. Case Statement
    By tsetliff webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 9
    Last Post: February 5th, 11:26 PM
  3. CASE statement and CONTAINS
    By steve in forum Macromedia Director Lingo
    Replies: 2
    Last Post: July 31st, 12:12 AM
  4. Case statement help
    By Meenal in forum Microsoft SQL / MS SQL Server
    Replies: 8
    Last Post: July 24th, 12:48 AM

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