Complex calculation in column - view?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Complex calculation in column - view??

Author

Topic

alexjamesbrown
Starting Member

48 Posts

Posted - 2009-03-26 : 10:13:15

long post alert...

I have a few tables in my database, which represent data i get from a txt file, which i import every day using bulk

copy.
there are 2 important tables. these are:

Handset
Deal
Tariff

In Deal, there are the following columns:

HandsetID
TariffID
Price
MonthsFree
MonthsHalf
CashBack

in Tariff:

TariffID
Name
ContractType
AnytimeMinutes
OffpeakMinutes
Texts
LineRental
CostOffset

i have a View - vDeals which selects all the deals, and performs some validation (like only selecting ones which are

valid etc..)

What i want to do, is have a column for each row in Deal - EffectiveMonthlyCost.
This needs to be a calculated column of some kind...

The calculation is:

1) Need to get the length of the contract.
This is done by using the ContractType column in Tariff.
This could be:

1m
12m
18m
24m
pp - if it's pp, ignore, and return 0

--basically, just need to take the "m" off the ContractType

@contractLength = Replace(ContractType, 'm', '')

2)
We then need to multiply the LineRental column by the ContractType (contract length)

@totalLineRentalCost = Tariff.LineRental * @contractLength

3)
We then need to work out any half price or free line rental.
The way i've done this is:

@monthsFreeDiscountValue = Deal.MonthsFree * Tariff.LineRental
@monthsHalfDiscountValue = Deal.MonthsHalf * Tariff.LineRental

4)
Now it's just the final calculations...

Basically its:

@totalLineRentalCost
-subtract @monthsFreeDiscountValue
-subtract @monthsHalfDiscountValue
-subtract Tariff.CostOffset

Divide this by the contract length

RETURN!!!

I know my syntax isn't right, but that's just to illustrate.
This (i think) should be a UDF? or else i need some way of adding a column onto a view, that runs this calculation?

Any help is appreciated... i've been scratching my head for AGES!!
lol

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-26 : 10:53:15

try this:


create view vDeals as

select <columnList>
       ,EffectiveMonthlyCost = 
              (t.LineRental * convert(int, Replace(t.ContractType, 'm', '')))    --totalLineRentalCost
              - (d.MonthsFree * t.LineRental)                               --monthsFreeDiscountValue 
              - (d.MonthsHalf * t.LineRental)                               --monthsHalfDiscountValue 
              - t.CostOffset
from   Deal d
inner join Tarrif t
       on t.tariffid = d.tariffid

Be One with the Optimizer
TG

alexjamesbrown
Starting Member

48 Posts

Posted - 2009-03-26 : 11:05:34

wow! so close...
i'd started to get it working using a udf and expression.
Your way is much neater however.

The only thing is, if the ContractType is "pp" then obviously it fails.
Is there a way we can make EffectiveMonthlyCost = 0 if ContractType = pp??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 11:14:28

Use a CASE statement?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 11:16:51

[code]CREATE VIEW dbo.vwDeals
AS

SELECT <columnList>,
CASE ContractType
WHEN 'pp' THEN 0
ELSE t.LineRental * (convert(int, Replace(t.ContractType, 'm', '')) + d.MonthsFree - d.MonthsHalf) - t.CostOffset
END AS EffectiveMonthlyCost
FROM Deal AS d
INNER JOIN Tarrif AS t ON t.tariffid = d.tariffid[/code]

E 12°55'05.63"
N 56°04'39.26"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-26 : 11:20:50

woops - yeah, I forgot to include that condition...

Be One with the Optimizer
TG

alexjamesbrown
Starting Member

48 Posts

Posted - 2009-03-26 : 11:24:21

excellent!
thanks for your help!!

alexjamesbrown
Starting Member

48 Posts

Posted - 2009-03-26 : 11:37:24

one small mod -
had to change the monthsHalf line to:

- ((t.LineRental / 2) * d.MonthsHalf)

to reflect the half priced line rental
silly me!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources