January 12, 2010 at 12:16 am
In my table i have columns named say 'DateEngaged', 'MonthsInServices' and 'MyCol3'. The "MonthsInService" column is a computed column and its formula is datediff(year,[DateEngaged],getdate()) which is supposed to return the number of months an employee has been in service.
My other column 'MyCol3' is also a computed column and its formula is supposed to be the "MonthsInService" column(which is it self a computed column) * 2 but unfortunately throws an error. the error is
"Computed column 'MonthsInService' in table 'Tmp_myTable' is not allowed to be used in another computed-column definition"
So my question, is there any other way or work around of using a computed column in another computed column's formula. I have tried using a user defined function but still throws the same error. Thanks.
January 12, 2010 at 12:11 pm
You can place the original calculation for MonthsInService into the calculation for MyCol3. It would look like this:
datediff(year,[DateEngaged],getdate()) * 2
Hope this helps,
Eli
January 12, 2010 at 12:54 pm
Just one note - for MonthsInService using that calculation are you aware that this will give you a value of 1 month even though there is only one day difference?
DATEDIFF(month, '20091231', '20100101')
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 12, 2010 at 1:54 pm
Run this query and note the anomalies in the results:
SELECT DateEngaged,MonthsInService As 'YearsinService',DATEDIFF(Month,DateEngaged,GETDATE()) AS 'Months Dif'
,DATEDIFF(Month,DateEngaged,GETDATE())/12 AS 'Pseudo Years'
,MonthsInService*2 AS 'MyColumn3' FROM dbo.MyComputedColumn ORDER BY DateEngaged
DateEngagedYearsinServiceMonths DifPseudo YearsMyColumn3
1990-03-01 00:00:00.00020 238 19 40
1990-04-01 00:00:00.00020 237 19 40
1990-05-01 00:00:00.00020 236 19 40
1990-05-01 00:00:00.00020 236 19 40
2000-05-01 00:00:00.00010 116 9 20
2009-12-01 00:00:00.0001 1 0 2
2010-01-12 00:00:00.0000 0 0 0
Without thinking of the inconsistencies in the results calculated MyColumn3 could be performed in the T-SQL select statement, no need for a second computed column.
January 12, 2010 at 11:21 pm
thanks.
January 12, 2010 at 11:23 pm
stopitallready (1/12/2010)
You can place the original calculation for MonthsInService into the calculation for MyCol3. It would look like this:datediff(year,[DateEngaged],getdate()) * 2
Hope this helps,
Eli
thanks.
true this datediff(year,[DateEngaged],getdate()) is not reliable, what is the more reliable way of getting the mumber on months between two dates
January 13, 2010 at 8:51 am
Nickson Asiimwe
Pardon me for injecting this comment, but I believe your problem to some extent currently lies outside of being a T-SQL problem. Rather it lies in the realm of business rules. For example:
Employee 1 starts work on Dec 1 2009 -- a tuesday
Employee 2 starts work on Dec 22 2009 -- a tuesday
Using the DATEDIFF(Month .... function, both would show a working period of one (1) month if the date of the calculation was a day in January 2010.
Now if I were Employee 1 that would be correct, but if some benefit were based on number of months worked, I would be upset that Employee 2 would receive the same benefit (say a pay raise or a vacation day, whatever) even though employee 2 worked less days that I did.
Do your current business rules say that this is what is desired, or do your business rules state that if the day of the calculation (Jan 21) is less than the day of engagement (Dec 22) then the employee should not be given credit for a month of work? Of course the business rules will make the calculation slightly more complex, but that is the price that must be paid to be accurate.
Articulate those rules and post as a NEW forum question and I am sure someone will attempt to assist you.
January 13, 2010 at 10:53 pm
bitbucket-25253 (1/13/2010)
Nickson AsiimwePardon me for injecting this comment, but I believe your problem to some extent currently lies outside of being a T-SQL problem. Rather it lies in the realm of business rules. For example:
Employee 1 starts work on Dec 1 2009 -- a tuesday
Employee 2 starts work on Dec 22 2009 -- a tuesday
Using the DATEDIFF(Month .... function, both would show a working period of one (1) month if the date of the calculation was a day in January 2010.
i have updated the formular to this
datediff(month,[DateEngaged],getdate())-case when datepart(day,[DateEngaged])>datepart(day,getdate()) then (1) else (0) end
January 14, 2010 at 7:23 am
Your revised T_SQL to adjust the calculated value of MonthsInService appears to do the job properly .. Go ahead and test further.
Have you considered eliminating the computed columns from the table and do all the calculations in your T-SQL select statement?
January 14, 2010 at 7:40 am
bitbucket-25253 (1/14/2010)
Have you considered eliminating the computed columns from the table and do all the calculations in your T-SQL select statement?
I haven't. are there any know serious drawbacks that i should be watching for when using computed columns?. i have to admit this is the first time i want to use computed columns in production and am not sure if its a good idea or not. am therefore more than ready to listen to and evaluate any advices.
January 14, 2010 at 10:09 am
I guess the answer to your question is "It depends"
Search TECHNET at:
http://technet.microsoft.com/en-us/default.aspx
using as the search words "SQL Server computed columns" and it will list for you a good deal of info from BOL / MSDN etc., that discuss the pros and cons of using computed columns and you can evaluate what best fits your situation.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply