Formula Field Relating To Another Formula Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Formula Field Relating To Another Formula Field

Author

Topic

Ssc456
Starting Member

2 Posts

Posted - 2011-10-20 : 11:40:22

Hi Guys,

I'm new here hoping to contribute as much as i'm asking but my first one is a question.

First off i consider myself a newbie when it comes to SQL as i've only been playing for it for a couple of months and i'm learning everyday so bear with me.

My question is when you create more than one of what i refer to as a formula field (might be wrong) can you refer to each of them within the other formula field?

Probably doesn't sound logical i know but let me give you an example.

Here is just a small section of a select statement that i plan to turn into a stored proc.

SELECT     F_TASKS.TA_TASK_ID, F_RESOURCE.RES_NAME, FLOCATE.BG_SITE, FINFILE.IN_DESCRIPTION, F_TASKS.TA_CATEGORY, 
                      F_TASKS.TA_PRIORITY, F_TASK_TIME.TT_ID, F_TASK_TIME.TT_NAME, FINFILE.IN_DEC_HOURS,
    
  'Next_SLA' = CASE

	WHEN (SELECT COUNT (*) FROM F_TASK_TIME WHERE TT_FKEY_TA_SEQ = F_TASKS.TA_SEQ 
and TT_FINISHED Is Not null ) >0 Then 'All SLAs Complete'

	WHEN (SELECT COUNT (*) FROM F_TASK_TIME WHERE TT_FKEY_TA_SEQ = F_TASKS.TA_SEQ 
and TT_CONTAINED Is Not null ) >0 Then 'Permanent Fix'

	WHEN (SELECT COUNT (*) FROM F_TASK_TIME WHERE TT_FKEY_TA_SEQ = F_TASKS.TA_SEQ 
and TT_STARTED Is Not null) >0  and F_TASKs.TA_PRIORITY = 'Priority 3' Then 'Permanent Fix'

	WHEN (SELECT COUNT (*) FROM F_TASK_TIME WHERE TT_FKEY_TA_SEQ = F_TASKS.TA_SEQ 
and TT_STARTED Is Not null) >0  and F_TASKs.TA_PRIORITY <> 'Priority 3' Then 'Temporary Fix'

         ELSE 'Investigate'
      END,

  'Next Date' = CASE

	WHEN 'Next_SLA' = 'Permanent Fix' Then (SELECT TOP 1 TaskPerfSLAFixDate From SLA_TASK_PERFORMANCE
						WHERE TASKPERF_TA_SEQ = F_TASKS.TA_SEQ)         ELSE '01/01/2001'
      END

Right the problem i have here is the 2nd "formula field" Next Date doesn't work as it's almost like it can't see the first formula field Next_SLA it just defaults to the 01/01/2001

Any ideas, i've temporarily got aroudn this but instead of testing the result of the Next_SLA field doing the complete logical test from the first field again, which is lenghy and not very nice lol.

bobmcclellan
Starting Member

46 Posts

Posted - 2011-10-20 : 15:28:49

Select x.*,
'Next Date' = CASE
WHEN 'Next_SLA' = 'Permanent Fix' Then (SELECT TOP 1 TaskPerfSLAFixDate From SLA_TASK_PERFORMANCE
WHERE TASKPERF_TA_SEQ = F_TASKS.TA_SEQ) ELSE '01/01/2001'
END
from (

SELECT F_TASKS.TA_TASK_ID, F_RESOURCE.RES_NAME, FLOCATE.BG_SITE, FINFILE.IN_DESCRIPTION, F_TASKS.TA_CATEGORY,
F_TASKS.TA_PRIORITY, F_TASK_TIME.TT_ID, F_TASK_TIME.TT_NAME, FINFILE.IN_DEC_HOURS,

'Next_SLA' = CASE

WHEN (SELECT COUNT (*) FROM F_TASK_TIME WHERE TT_FKEY_TA_SEQ = F_TASKS.TA_SEQ
and TT_FINISHED Is Not null ) >0 Then 'All SLAs Complete'

WHEN (SELECT COUNT (*) FROM F_TASK_TIME WHERE TT_FKEY_TA_SEQ = F_TASKS.TA_SEQ
and TT_CONTAINED Is Not null ) >0 Then 'Permanent Fix'

WHEN (SELECT COUNT (*) FROM F_TASK_TIME WHERE TT_FKEY_TA_SEQ = F_TASKS.TA_SEQ
and TT_STARTED Is Not null) >0 and F_TASKs.TA_PRIORITY = 'Priority 3' Then 'Permanent Fix'

WHEN (SELECT COUNT (*) FROM F_TASK_TIME WHERE TT_FKEY_TA_SEQ = F_TASKS.TA_SEQ
and TT_STARTED Is Not null) >0 and F_TASKs.TA_PRIORITY <> 'Priority 3' Then 'Temporary Fix'

ELSE 'Investigate'
END

FROM YourTable

)x

Ssc456
Starting Member

2 Posts

Posted - 2011-10-21 : 04:44:54

Hey,

thanks for the reply.

so i have to tell my formula field that it's referring to another formula field by including it in a from bracket?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources