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]

Management Studio very slow to startup

I'm trying to troubleshoot a strange issue. Here are some specifics...

+ just set up a brand new 64bit server (Windows 2003 R2 64 bit O/S and SQL 2005 64 bit, Std Edition); SQL is the only thing running on this server.

+ installed this new server in our AD domain; set up custom domain-level accounts for the SQL Service, Agent, and Integrations Services service accounts, following the BOL help topics "Setting up Windows Service Accounts."

+ I use RDP (remote desktop) to connect to the SQL Server server machine just fine. Once I'm RDP'd into the SQL2005 box, I fire up SQL Mgmt Studio there... it takes well over a couple of minutes before it even finishes rendering the first screen to connect to any instances. Once it finally does come up, I can connect to the (local) db using Windows Auth or the sa account w/out any delay or issue.

+ on my own workstation (an XP Pro laptop), I can open up SQL Mgmt Studio, and it opens up quickly (well, as quickly as Mgmt Studio or Visual Studio probably can these days ... <g>) and also connect to that new SQL2005 box w/out issue.

I am completely baffled as to why it SQL Mgmt studio would take so long to open up on the very server SQL is running, much less a brand new 64 bit server! Any information on what it's trying to do might be helpful... It's almost like it's trying to authenticate or connect to something and timing out, although no errors are displayed and there's nothing in the event logs. It's odd to me though, as at that point (just opening up the application), it doesn't seem like it should be attempting anything like that.

-- Margo Noreen

One issue that can cause this problem is that if the server does not have access to the internet, then the .NET framework can't access the crl.microsoft.com website to verify that the digital signatures used to sign the binaries for managed applications are valid. Each certificate check has a 15 second timeout in the .NET runtime implementation. Depending on what features are installed, this can add up to a minute of start up time for Management Studio.

There are a couple workarounds:

1) Configure a proxy server to allow access to http://crl.microsoft.com from your server
2) Configure your firewall to return a failure status quickly when it blocks access to the http://crl.microsoft.com website
3) Disable checks for certificate revocation. You can do this using Internet Explorer by opening the Interet Options dialog, going to the Advanced Page, and then unchecking the "Check for publisher's certificate revocation" checkbox. There are fraudulently signed binaries in the wild that can make virus-infected applications look like they were published by Microsoft. Disabling this check should probably not be done on machines with internet access.

Hope this helps,
Steve

Management Studio Settings?

I apologize in advance if these questions seem a bit simplistic, but I am
just starting out using the SQL 2005 Management Studio. Is there a way to
have windows that are opened up to restore back to their previous positions
and sizes? The Activity Monitor window is a good example - It always opens up
in the same size and position, no matter what I have done previously in it.
Are there sort order settings, too?
Jeff B (JeffB@.discussions.microsoft.com) writes:
> I apologize in advance if these questions seem a bit simplistic, but I
> am just starting out using the SQL 2005 Management Studio. Is there a
> way to have windows that are opened up to restore back to their previous
> positions and sizes? The Activity Monitor window is a good example - It
> always opens up in the same size and position, no matter what I have
> done previously in it. Are there sort order settings, too?
Not that I know. But it sounds like a good suggestion to submit on
http://lab.msdn.microsoft.com/produc.../default.aspx. Hey, I might
even vote for it!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx