Darryl Collins
Darryl.Collins at coles.com.au
Mon Jul 14 19:32:54 CDT 2008
bugger... I think Outlook sent this before I had a chance to write anything. Sorry if you get a blank reply., "assumed that he was intending to store the *results* of the calculation in ordinary data fields in the table" Stuart, you assumption was 100% correct, and my original thought process was two steps removed from SQL Server. First I was thinking in Excel Mode - basically store a formula in the table that did the calculation, and then I was trying to think how I would do that in Access blah blah, before I started to see the light and think logically about it. After reading what Asger had to say I now understand that my initial concept of what "Calculated columns" are and what they can be used for was flawed to begin with. Grasshopper has learnt another valuable lesson from the masters this week. Thanks. :) -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Stuart McLachlan Sent: Tuesday, 15 July 2008 9:22 AM To: Discussion concerning MS SQL Server Subject: Re: [dba-SQLServer] Calculated Column Field in a table... You're right. I don't use "computed columns" in tables generally and didn't think of doing so in this instance. When Darryl talked about "calculated columns", I was thinking in Access mode and assumed that he was intending to store the *results* of the calculation in ordinary data fields in the table. I really have to get over the mindset that tables are just data stores - in SQL Server, you can embed much of your business logic at the table level. Stuart On 14 Jul 2008 at 13:31, Asger Blond wrote: > Stuart and Darryl, > > Calculated columns are calculated on the fly when querying the table. They > are NOT stored in the table unless you make an index on the calculated > column. > Non-indexed calculated columns are just a convenient way of having often > used calculations ready for table querying. If on the other hand you make an > index on a calculated column you actually persist the calculated results > which will speed up query performance compared to calculations in stored > procedures or views, but at the same time it will degrade performance of > insert and update operations against the table. > > Asger > > > -----Oprindelig meddelelse----- > Fra: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Stuart > McLachlan > Sendt: 14. juli 2008 08:36 > Til: Discussion concerning MS SQL Server > Emne: Re: [dba-SQLServer] Calculated Column Field in a table... > > Your last comment is right on the nail. You don't want to store calcuated > results in the table > unless you have VERY good reasons to do so. > > You can create either a View or a Stored Procedure to return Capex and Opex. > > In a Stored Procedure it would be > > Select > ....., > Case AccTreatmentId > When 1 Then Dollars*AccountingSplit > Else Dollars * (1-AccountingSplit) > End As Capex, > Case AccTreatmentId > When 2 Then Dollars*AccountingSplit > Else Dollars * (1-AccountingSplit) > End As Opex, > ..... > > > In the Design grid in a View, put > > "CASE AccTreatmentId WHEN 1 THEN Dollars * AccountingSplit ELSE Dollars * (1 > - > AccountingSplit) END" > as the Column value and "Capex" as the Alias and similarly for Opex. > > > Cheers, > Stuart > > > On 14 Jul 2008 at 15:16, Darryl Collins wrote: > > > > > Hi there, > > > > I would have thought this was pretty simple, but I have a great ability to > complicate these things it seems - heh, early days for me SQL server. > > I have three fields in the table that I want to use for a calculated > result in a couple of other fields in the table. > > > > "AccTreatmentID" > > 1: Opex > > 2: Capex > > > > "AccountingSplit" > > 0%-100% > > > > "Dollars" > > Manually entered dollar value > > > > > > Then I have two other fields (Capex and Opex) and I (think I) want to do > this. > > > > "=iif([AccTreamtentID]=1,([Dollars]*[AccountingSplit]),0)" to get the Opex > Value > > "=iif([AccTreamtentID]=2,([Dollars]*[AccountingSplit]),0)" to get the > Capex Value > > > > or If AccTreatment is Capex, then get the Capex Percentage and multiply by > the dollar value to get the capex amount. > > The actual formula is more complex as I need to determine the balance of > the percentage and then apply that to the remainder of the value (opex in > this case). anyway I am trying to keep it simple to start with. (plus I > will probably need some sort of error test to return zero if part of the > data is missing - Am not sure if I need this or not in SQL server.) > > > > The syntax in these formulae are not valid so I would like some help with > that. And I guess my other question is do I actually need this at all? > > > > Maybe I should do all the hack work in a query to report of Opex / Capex > splits. Part of me suspect I dont need to store the calculated data at all > in the table at all and this is bad practise as it takes up time and space > for no real reason. > > > > Anyone got any thoughts on this. > > > > Cheers > > Darryll > > > > This email and any attachments may contain privileged and confidential > information and are intended for the named addressee only. If you have > received this e-mail in error, please notify the sender and delete this > e-mail immediately. Any confidentiality, privilege or copyright is not > waived or lost because this e-mail has been sent to you in error. It is your > responsibility to check this e-mail and any attachments for viruses. No > warranty is made that this material is free from computer virus or any other > defect or error. Any loss/damage incurred by using this material is not the > sender's responsibility. The sender's entire liability will be limited to > resupplying the material. > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this e-mail in error, please notify the sender and delete this e-mail immediately. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. It is your responsibility to check this e-mail and any attachments for viruses. No warranty is made that this material is free from computer virus or any other defect or error. Any loss/damage incurred by using this material is not the sender's responsibility. The sender's entire liability will be limited to resupplying the material.