Hi,

I was given the task of adding a new table to a stored procedure that I did not write and cannot figure out where to add the code for it. Basically, I need to do the following:

1 - Add a new parameter to asp_GetRate stored procedure

2 - In asp_GetRate (see below), I already have links to tblAdPartInRateCard and tblRateCard. The fields I need are in tblMedia (AgencyDiscount and CashDiscount) and tblMedia links to tblRateCards via VehicleKey so I have to join these two tables to get these fields. Once I get these, I have to apply the discount i.e reducing the gross rate by 15%.

I would appreciate very much if someone could help me.

Cheers


asp_GetRate stored procedure

CREATE PROC asp_GetRate
(
@p_UserID int,
@p_RateCardID int,
@p_EarnedRateBW int,
@p_EarnedRateColour int,
@p_AdPageID int,
@p_PositionID int =0
)
AS
DECLARE @fltAdRate float
DECLARE @fltPosRate float

--CHECK FOR Missing Adparts
IF ( SELECT (COUNT(*))
 FROM tblAdUnitInAdPage adup,
  tblAdPartInAdUnit apau,
  tblAdParts adp,
  tblAdPartTypes adpt
 WHERE adPageID=@p_AdPageID
 AND adup.AdUnitID = apau.AdUnitID
 AND apau.AdPartID = adp.AdPartID
 AND adp.AdPartTypeID = adpt.AdPartTypeID
 AND apau.AdPartID NOT IN (
  SELECT AdPartID
  FROM tblAdPartInRateCard
  WHERE RateCardID=@p_RateCardID)
  AND
  ( adpt.AdPartTypeName IN ('B&W', 'Position', 'Insert','TCP')
 OR (adpt.AdPartTypeName = 'Color' AND adp.Code <> 'BW')
  )
 ) = 0
--No missing adparts
BEGIN

 SELECT @fltAdRate =SUM(Rate * adu.AdUnitCount)
 FROM tblAdPages adp
 INNER JOIN tblAdUnitInAdPage adup
 ON adp.AdPageID = adup.AdPageID
 INNER JOIN tblAdUnits adu
 ON adu.AdUnitID = adup.AdUnitID
 INNER JOIN tblAdPartInAdUnit adpu
 ON adup.AdUnitID = adpu.AdUnitID
 INNER JOIN tblAdParts adpt
 ON adpt.AdPartID = adpu.AdPartID
 INNER JOIN tblAdPartTypes adptt
 ON adpt.AdPartTypeID = adptt.AdPartTypeID
INNER JOIN tblAdPartInRateCard adptr
 ON adpu.AdPartID = adptr.AdPartID
INNER JOIN tblRateCards rat
 ON adptr.RateCardID = rat.RateCardID
 AND adptr.RateCardID = @p_RateCardID
 WHERE adp.AdPageID = @p_AdPageID
 AND adptr.EarnedRate
  =
  (SELECT MAX(adptr.EarnedRate)
   FROM tblAdPartInRateCard adptr
   WHERE adpt.AdPartID = adptr.AdPartID
   AND rat.RateCardID = adptr.RateCardID
   AND ISNULL(adptr.EarnedRate,0)<=
    CASE adptt.AdPartTypeName
   WHEN 'B&W' THEN @p_EarnedRateBW
   WHEN 'Color' THEN @p_EarnedRateColour
    WHEN 'Bleed' THEN 1
    WHEN 'Position' THEN @p_EarnedRateBW
    WHEN 'Insert' THEN @p_EarnedRateBW
    WHEN 'TCP' THEN @p_EarnedRateBW
    END
  )

 --Cost of position
 SELECT @fltPosRate = MIN(Rate)
 FROM tblAdPartInRateCard
 WHERE AdPartID = @p_PositionID
 AND RateCardID = @p_RateCardID
 AND EarnedRate <= @p_EarnedRateBW
 AND @p_PositionID > 0

 --Output total rate
 SELECT @fltAdRate + ISNULL(@fltPosRate,0) as Rate

END
ELSE
 --Missing adparts
 --Return error value
 SELECT -1
GO

As far adding new parameter... it should go next to On adding new table I dont understand what needs to go where, unless you explain in detail what actually is required...

Cheers


_________________________
- Vijay G
Strive for Perfection

Hi Vijay G,

Many thanks for your help.

It turned out that I got away by doing this:

select @cashDiscount = CashDiscount, @agencyDiscount=Agencydiscount from tblMedia m
inner join tblRateCards r on r.VehicleKey = m.VehicleKey where ratecardid= @p_RateCardID

And that got the value I need.

Cheers

CP