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
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.
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
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.
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
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.
can you tell me where is decimal option in design table because I can't find it Thanks Ever smiling Ashish
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)
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
