Cost Calculation Query - 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)

Cost Calculation Query

Author

Topic

fubuki99
Starting Member

10 Posts

Posted - 2009-05-08 : 17:23:51

Greetings,

I'm trying to figure out how to write a statement that will allow me to
perform the following aggregate functions from the below table:

COS TN_Total Telco Service_fee

1 28 ACFBS NULL
4 508 ACFBS 483
5 2 ACFBS NULL
7 1 ACFBS NULL
2 4486 ACFBS 3799

1. Rename each COS to

if 1 than "Residence"
if 2 than "Business"
if 4 than "BusPBX"
if 5 than "Centrex"
if 7 than "Coin2Way"

I need to perform the following calculations based on COS:

if COS equals the following:

1 multiply .50 to the amount in the Service_Fee column, if it is null than it should use the number from the TN_Total column
2 multiply .80 to the amount in the Service_Fee column, if it is null than it should use the number from the TN_Total column
4 multiply .87 to the amount in the Service_Fee column, if it is null than it should use the number from the TN_Total column
5 multiply .87 to the amount in the Service_Fee column, if it is null than it should use the number from the TN_Total column
7 multiply ..50 to the amount in the Service_Fee column, if it is null than it should use the number from the TN_Total column

These results could go to a column called "COS Cost"

I'm not sure how to begin this as I am new to T-SQL and would appreciate the help.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-08 : 17:57:22

See if this helps:

DECLARE @temp TABLE (COS INT, TN_Total INT, Telco VARCHAR(20), Service_fee INT)

INSERT @temp
SELECT 1, 28, 'ACFBS', NULL UNION ALL
SELECT 4, 508, 'ACFBS', 483 UNION ALL
SELECT 5, 2, 'ACFBS', NULL UNION ALL
SELECT 7, 1, 'ACFBS', NULL UNION ALL
SELECT 2, 4486, 'ACFBS', NULL

SELECT [COS] = CASE
WHEN COS = 1 THEN 'Residence'
WHEN COS = 2 THEN 'Business'
WHEN COS = 4 THEN 'BusPBX'
WHEN COS = 5 THEN 'Centrex'
WHEN COS = 7 THEN 'Coin2Way'
END,
[COS Cost] = CASE
WHEN COS = 1 THEN ISNULL(Service_fee * .50, TN_Total)
WHEN COS = 2 THEN ISNULL(Service_fee * .80, TN_Total)
WHEN COS = 4 THEN ISNULL(Service_fee * .87, TN_Total)
WHEN COS = 5 THEN ISNULL(Service_fee * .87, TN_Total)
WHEN COS = 7 THEN ISNULL(Service_fee * .50, TN_Total)
END
FROM @temp

Some days you're the dog, and some days you're the fire hydrant.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 05:11:15

you could merge some of conditions in last case since calculations are same


..
[COS Cost] = CASE
WHEN COS = 1 OR COS = 7 THEN ISNULL(Service_fee * .50, TN_Total)
WHEN COS = 2 THEN ISNULL(Service_fee * .80, TN_Total)
WHEN COS = 4 OR COS = 5 THEN ISNULL(Service_fee * .87, TN_Total)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources