manipulate field value from select statement

Hi all,

any assistance will be much appreciated on this one .... a bit clueless at the mo!

I've been trying to execute the code below in which part of my select statement is a calculated value i.e. Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE

code:

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) & "/" & SUBSTRING([SD],5,2) & "/" & Left([SD],4) AS STARTDATE,
Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE, vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

however this error message keeps coming up at runtime:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ENDDATE'.

My guess is it's happening when I try to get the date difference (DATEDIFF)....

help!!

Try this..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

|||

Sh... should have seen that one.

Cheers mate .. however I'm still having an error from that code:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_spend'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_dates'.

Is there some sort of restriction on selecting from a view in sql server?

|||

Bolugbe wrote:

My guess is it's happening when I try to get the date difference (DATEDIFF)....

You're right, the problem is in the DATEDIFF statement.
You cannot use just assigned aliases in calculations, so you should either copy/paste the formula for getting ENDDATE into DATEDIFF function or use nested select statements|||

Try this one..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), Convert(datetime,Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4))) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]