Calculate with an alias - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Calculate with an alias

Author

Topic

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-10-28 : 16:02:00

Hello,

I use a alias in a query like " AS Total ".
No I want to calculate a field with Total but this isn't possible.

How can I calculate with an alias field?

Thanks in advance,
Sven.

mfemenel
Professor Frink

1421 Posts

Posted - 2009-10-28 : 16:15:27

What makes you say that isn't possible?
select count(*) 'As Total' from sys.objects

Mike
"oh, that monkey is going to pay"

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-10-29 : 02:30:10

Hello,

this is an example that I cant't get to work:

SELECT startdate, Enddate, Price, CASE WHEN startdate > '01 / 08 / 2005' THEN DATEDIFF(week, startdatum, Enddate) ELSE '' END AS Weeks, Price * Weeks AS Total
FROM dbo.Prices

The error I see is that Weeks isn't a valid column ?

How can I solve this problem?

Thanks in advance,
Sven.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-29 : 02:35:11

[code]
SELECT startdate,
Enddate,
Price,
CASE WHEN startdate > '01 / 08 / 2005' THEN DATEDIFF(week, startdatum, Enddate) ELSE '' END AS Weeks,
Price * ~~Weeks~~ CASE WHEN startdate > '01 / 08 / 2005' THEN DATEDIFF(week, startdatum, Enddate) ELSE '' END AS Total
FROM dbo.Prices

OR

SELECT startdate,
Enddate,
Price,
Weeks,
Price * Weeks AS Total
FROM
(
SELECT startdate,
Enddate,
Price,
CASE WHEN startdate > '01 / 08 / 2005' THEN DATEDIFF(week, startdatum, Enddate) ELSE '' END AS Weeks
FROM dbo.Prices
) AS P
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-29 : 02:37:36

why are you returning empty string in your case statement ?

you should return same data type for you case statement. Since datediff() will return an integer, you should return 0 instead of '' in the ELSE part.


CASE WHEN startdate > '01/08/2005' THEN DATEDIFF(week, startdatum, Enddate) ELSE 0 END

KH
[spoiler]Time is always against us[/spoiler]

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-10-29 : 03:11:28

Hello,

ok, i understand what you are writing but when I change the '' to 0 I get still an error about converting nchar to numeric ...

the datatype of price is decimal
Weeks is integer

Can that be the problem?

Sven.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-29 : 04:31:51

decimal and integer is fine. The error is probably comming from somewhere else. .

KH
[spoiler]Time is always against us[/spoiler]

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-10-29 : 04:44:27

Hello,

I got it working with example 2 ...

But why is the word DERIVEDTBL added in my SQL ?

Is this a problem?

Sven.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-29 : 04:55:18

Derived table. The section in blue is a derived table


SELECT startdate,
       Enddate, 
       Price,
       Weeks,
       Price * Weeks AS Total
FROM
(
    SELECT startdate, 
           Enddate, 
           Price, 
           CASE WHEN startdate > '01 / 08 / 2005' THEN DATEDIFF(week, startdatum, Enddate) ELSE '' END AS Weeks
    FROM   dbo.Prices
) AS P

KH
[spoiler]Time is always against us[/spoiler]

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-10-29 : 06:20:32

Hello,

last question about this topic ... is it also possible to add a WHERE component in this type of query ?

I tried this but I always get an error:

SELECT startdate,
Enddate,
Price,
Weeks,
Price * Weeks AS Total
FROM
(
SELECT startdate,
Enddate,
Price,
CASE WHEN startdate > '01 / 08 / 2005' THEN DATEDIFF(week, startdatum, Enddate) ELSE '' END AS Weeks
FROM dbo.Prices
)
WHERE startdate > '01/5/1972' AND Enddate < '01/01/2014'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-29 : 06:40:15

you need to name the derived table


SELECT startdate,
Enddate,
Price,
Weeks,
Price * Weeks AS Total
FROM
(
SELECT startdate,
Enddate,
Price,
CASE WHEN startdate > '01 / 08 / 2005' THEN DATEDIFF(week, startdatum, Enddate) ELSE '' END AS Weeks
FROM dbo.Prices
)AS P
WHERE startdate > '01/5/1972' AND Enddate < '01/01/2014'

KH
[spoiler]Time is always against us[/spoiler]

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-10-29 : 14:29:43

Hello,

I have it all working now ...

Thanks,
Sven.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources