SQL Server Performance

required suggestion about table

Discussion in 'Getting Started' started by mastersql, Aug 23, 2007.

  1. mastersql New Member

    Hello sir
    I just started working in SQL Server so please don't mind when my question is irritating please help me out for this
    I have a table having a coloumns
    Month-----------------Size----------------------Issue
    April-07---------------750----------------------2676
    April-07--------------1000--------------------1223
    April-07--------------180---------------------3439
    April-07--------------90---------------------23562
    May-07---------------750----------------------254
    May-07--------------375--------------------454
    May-07--------------180---------------------454
    May-07--------------90---------------------3434
    Something like that it is just a example o my table I have data in Thousands
    So know what I want to do is I want to add one more coloumn where i want to use if statment something like that
    Issue_cases: IIf([size]=1000,[Issue]/9,IIf([size]=750,[Issue]/12,IIf([size]=375,[Issue]/24,IIf([size]=180,[Issue]/48,IIf([size]=90,[Issue]/100)))))
    now you please tell me that for this what I have to do I have to create a view or procedure or anything else and how?
    Please tell me
    I thing I explain my question best
    I required your help immediately
    Thanks
    Ever Smiling
    Ashish
  2. martins New Member

    Hi,
    You can create a view to do this, or you can create a stored proc that will update the field. Which one you use is pretty much a question of how you would want to use it in future. If people would want to see this often, it might be better to create a permanent field on the table and update with a proc.
    You would need to use a CASE statement to do what you have described above.
    Example:
    select month
    , size
    , issue
    , case size
    when 1000 then issue/9
    when 750 then issue/12
    end as ExtraColumn
    from Table1
    Hope this helps.
  3. mastersql New Member

    Thanks for your reply Sir
    There is any other option beside that because it is bit complicated for me
    please reply when you thing that there is any other option
    please
    Thanks
    Ever smiling
    Ashish
  4. martins New Member

    Your only other option is to create a computed column. When you are in design-mode in Enterprise Manager and add a new column you will see that there is a "Formula" property for your column. You can add the case statement in there, which will then ensure that every time the size or issue is updated the value for your new field will also change.
    The case will look like this (using your info above): (case size when 1000 then issue/9 when 750 then issue/12 when 375 then issue/24 when 180 then issue/48 when 90 then issue/100 else issue end)
    Cut and paste the above into the "formula" property of your new column. It should work.
  5. mastersql New Member

    Thank you very much sir its working for me
    now one thing more that it shows a value in lot of decimal places but I required in only upto 2 decimal places so what i have to do for this
    Thanks again for your reply
    Ever smiling
    Ashish
  6. martins New Member

    Sure, no problem [:)]
    If you want only 2 decimal places, make sure your new column is of type decimal(10,2). The "10" specifies the maximum length of your field and the "2" the amount of decimal places. You can change the "10" to be either more or less, depending on the expected values.
  7. mastersql New Member

    can you tell me where is decimal option in design table because I can't find it
    Thanks
    Ever smiling
    Ashish
  8. martins New Member

    Sorry...should have explained properly.
    In design mode, the second column at the top it "Data Type". Select "decimal" from the dropdown next to your new column. The at the bottom you will see a "precision" and a "scale" property. Set "precision" to 10 and "scale" to 2.
    That will be equal to decimal(10,2)
  9. mastersql New Member

    Thanks Sir
    I got this
    Thank you very much for helping me
    you explain it to me very clearly
    I am really thankful to you for this
    Thanks
    Ever Smiling
    Ashish

Share This Page