help with possibly a variable - 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

SQL Server 2005 Forums

Transact-SQL (2005)

help with possibly a variable

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-07-13 : 15:08:54

Hi
I am creating a view.
Here is part of the select for two of the fields in the view;

CASE WHEN SOPS_Data.ForeignCurr < Sales_Data.Amount THEN CAST(SOPS_Data.ForeignCurr/SOPS_Data.Rate as decimal(15,2))
ELSE CAST(Sales_Data.Amount /SOPS_Data.Rate as decimal(15,2)) END as "AmountGBP",
CAST((Sales_data.Quantity * sales_data.ConvRate * sales_data.CostPrice) as decimal(15,2)) as "TotalCost"

This gives me two fields (namely "AmountGBP" and "TotalCost".)
I want my next field to be called "Profit" and to be the "AmountGBP" minus the "TotalCost" but aprt form writing out another really long sum i don't know of an easy way to do this.
i think there must be a way for me to declare "AmountGBP" and "TotalCost" just once and then to be able to use them in various calculated fields?

Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-13 : 15:18:32

try common table expression


;WITH cteSops
as
(
your select AmountGBP, TotalCost from x)

)
Select AmountGBP, 
       TotalCost , 
       (isnull(AmountGBP,0)- isnull(TotalCost,0)) as profit
from cteSops

of course you will have to tweak it so that

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-07-13 : 15:29:20

Thanks Yosiasz.
I don't really understand what you mean could you possibly explain it a bit more?

Cheers

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-13 : 15:44:59

do you know what common table expressions are?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-07-13 : 16:16:31

I've just done some research, so I know now:O)

I think i could achieve the same by creating another view.
What do you think?
Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-13 : 18:09:05

you could. would that second view be used by anything else? try common table expression, it accomplishes what you want to do and you get to learn something new and easy.

If you don't have the passion to help people, you have no passion

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-07-14 : 05:10:58

Hey man
I just did my first CTE - It's great to learn!
My CTE below is not doing much yet - but I plan to build it up a bit
========================================================
;WITH AspelExRate_CTE (Recid, AspelExrate) AS
(
SELECT Recid, CASE WHEN SOPS_Data.Currency = 'GBP' THEN 1 ELSE Currency.ExRate END
FROM SOPS_Data LEFT JOIN Currency on SOPS_Data.Currency = Currency.FromCurrency
)
Select
SOPS_Data.Recid,
SOPS_Data.InvNo,
SOPS_Data.OrderNo,
SOPS_Data.Position,
SOPS_Data.Currency,
SOPS_Data.InvDate,
SOPS_Data.[Month],
SOPS_Data.[Quarter],
SOPS_Data.DayName,
SOPS_Data.DayofMonth,
SOPS_Data.[MonthName],
SOPS_Data.[Year],
SOPS_Data.ItemCode,
SOPS_Data.ForeignCurr,
SOPS_Data.LocalCurr,
CAST(SOPS_Data.LocalCurr/SOPS_Data.ForeignCurr as decimal(15,6)) as "System ExRate",
AEX.AspelExRate
from
SOPS_Data
INNER JOIN AspelExrate_CTE AEX on SOPS_Data.Recid = AEX.Recid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources