Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

is there away of using a computed column in another computed column formular in sql server 2005 Expand / Collapse
Author
Message
Posted Tuesday, January 12, 2010 12:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:54 AM
Points: 173, Visits: 597
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.
Post #845892
Posted Tuesday, January 12, 2010 12:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 2, 2010 6:41 AM
Points: 54, Visits: 134
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
Post #846439
Posted Tuesday, January 12, 2010 12:54 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:12 PM
Points: 4,386, Visits: 9,502
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #846472
Posted Tuesday, January 12, 2010 1:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 5,566, Visits: 24,729
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

DateEngaged YearsinService Months Dif Pseudo Years MyColumn3
1990-03-01 00:00:00.000 20 238 19 40
1990-04-01 00:00:00.000 20 237 19 40
1990-05-01 00:00:00.000 20 236 19 40
1990-05-01 00:00:00.000 20 236 19 40
2000-05-01 00:00:00.000 10 116 9 20
2009-12-01 00:00:00.000 1 1 0 2
2010-01-12 00:00:00.000 0 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.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #846525
Posted Tuesday, January 12, 2010 11:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:54 AM
Points: 173, Visits: 597
thanks.
Post #846697
Posted Tuesday, January 12, 2010 11:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:54 AM
Points: 173, Visits: 597
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
Post #846699
Posted Wednesday, January 13, 2010 8:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 5,566, Visits: 24,729
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.



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #846964
Posted Wednesday, January 13, 2010 10:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:54 AM
Points: 173, Visits: 597
bitbucket-25253 (1/13/2010)
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.



i have updated the formular to this

datediff(month,[DateEngaged],getdate())-case when datepart(day,[DateEngaged])>datepart(day,getdate()) then (1) else (0) end
Post #847338
Posted Thursday, January 14, 2010 7:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 5,566, Visits: 24,729
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?


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #847527
Posted Thursday, January 14, 2010 7:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:54 AM
Points: 173, Visits: 597
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.
Post #847543
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse