Urgent UPDATE statement

• Dave Greeko

  Newbie

  Points: 1

  More actions

  October 20, 2006 at 11:41 am

  #174720

  I really gave up on this problem and your help will really save my job.  I have a DB with 2 tables to rate telephone calls. First Table stores the rate for each International destination and the second table stores the call records. Here is a real data from both tables:

  tblRate(3 Fieleds):

  DialCode,DestinationName,Rate

  Sample Data:

  49,Germany,0.02

  491,Germany Mobile,0.18

  4930,Germany Berlin,0.01

   

  tblCall_Log(5 Fields)

  CallId,CallDate,DialedNumber,Duration,Amount

  Sample Data:

  1,10/18/2006 10:09:45 AM,49148759654,3.5,NULL

  2,10/18/2006 10:18:06 AM,49307896445,18.1,NULL

  3,10/18/2006 10:30:31 AM,49835411472,2,NULL

   

  All I need is an UPDATE statement to populate the Amount in tblCall_Log by looking up the rate in tblRate and multiply it by Duration of the call based on the prefix of the DialedNumber for example the first call (CallId=1) should be rated at 0.18 because its DialedNumber prefix is 491, Second call (called=2) should be rated at 0.01 because its DialedNumber prefix is 4930.  As you can see the last call should be rated at 0.02

   

  Thank you in advance for your help

• Ken McKelvey

  SSCoach

  Points: 18843

  More actions

  October 20, 2006 at 4:05 pm

  #667227

  You will get a quicker response in the T-Sql forum.

  This should work but probably can be optimized.

  I do not have time to look at it further.

  -- Test Data

  DECLARE @Rate TABLE

  (

   DialCode VARCHAR(20) COLLATE DATABASE_DEFAULT NOT NULL PRIMARY KEY

   ,DestinationName VARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL

   ,Rate MONEY NOT NULL

  )

  INSERT @Rate

  SELECT '49', 'Germany', 0.02 UNION ALL

  SELECT '491', 'Germany Mobile', 0.18 UNION ALL

  SELECT '4930', 'Germany Berlin', 0.01

  DECLARE @Call_Log TABLE

  (

   CallId INT NOT NULL PRIMARY KEY

   ,CallDate DATETIME NOT NULL

   ,DialedNumber VARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL

   ,Duration DECIMAL(18,1) NOT NULL

   ,Amount MONEY NULL

  )

  INSERT @Call_Log

  SELECT 1, '20061018 10:09:45', '49148759654', 3.5, NULL UNION ALL

  SELECT 2, '20061018 10:18:06', '49307896445', 18.1 ,NULL UNION ALL

  SELECT 3, '20061018 10:30:31', '49835411472', 2 ,NULL

  -- The Update

  -- This is the bit you use with your table names.

  UPDATE C1

  SET Amount = C1.Duration * R1.Rate

  FROM @Call_Log C1

   JOIN @Rate R1 ON C1.DialedNumber LIKE R1.DialCode + '%'

   JOIN (

    SELECT C2.CallID, MAX(R2.DialCode) AS DialCode

    FROM @Call_Log C2

     JOIN @Rate R2 ON C2.DialedNumber LIKE R2.DialCode + '%'

    GROUP BY C2.CallID) D1

   ON C1.CallID = D1.CallID AND R1.DialCode = D1.DialCode

  -- Show Result

  SELECT *

  FROM @Call_Log