March 27, 2008 at 5:21 am
Hi,
I'm trying to create a User Defined Function to modularize the Case statement in the SQL statement below.
The function will pass in two parameters vCruiseShip.LineID (int) and CruiseOffers.[F/C_Fare] (money), the output will be one value (money).
The function will be used to calculate a percentage decrease on the fare based on the vCruiseShip.LineID. The percentage will change based on the cruise line but they are all the same in the example below till I get it working.
Any help on this would be greatly appreciated as I'm a bit new to Stored Procs and UDFs, I've listed my SQL statement below...
Many thanks
Mark
SELECT DISTINCT TOP 500 CruiseOffers.voyageCode, CruiseOffers.itineraryDescription, CruiseOffers.flyingDate, CruiseOffers.[F/C_Nts],
-- Would like to use a UDF for this case statement (the percentages will change) ---
CASE vCruiseShip.LineID
WHEN 1 THEN MIN(CruiseOffers.[F/C_Fare] * 0.92)
WHEN 2 THEN MIN(CruiseOffers.[F/C_Fare] * 0.92)
WHEN 3 THEN MIN(CruiseOffers.[F/C_Fare] * 0.92)
WHEN 4 THEN MIN(CruiseOffers.[F/C_Fare] * 0.92)
WHEN 5 THEN MIN(CruiseOffers.[F/C_Fare] * 0.92)
WHEN 8 THEN MIN(CruiseOffers.[F/C_Fare] * 0.92)
WHEN 9 THEN MIN(CruiseOffers.[F/C_Fare] * 0.92)
WHEN 10 THEN MIN(CruiseOffers.[F/C_Fare] * 0.92)
WHEN 12 THEN MIN(CruiseOffers.[F/C_Fare] * 0.92)
WHEN 13 THEN MIN(CruiseOffers.[F/C_Fare] * 0.92)
WHEN 14 THEN MIN(CruiseOffers.[F/C_Fare] * 0.92)
WHEN 15 THEN MIN(CruiseOffers.[F/C_Fare] * 0.92)
WHEN 20 THEN MIN(CruiseOffers.[F/C_Fare] * 0.92)
WHEN 22 THEN MIN(CruiseOffers.[F/C_Fare] * 0.92)
WHEN 24 THEN MIN(CruiseOffers.[F/C_Fare] * 0.92)
ELSE MIN(CruiseOffers.[F/C_Fare])
END AS [F/C_Fare],
-- ---
CruiseOffers.regionID, vCruiseShip.cruiseShip, vCruiseShip.cruiseLine, vCruiseShip.shipID, vCruiseShip.LineID
FROM CruiseOffers
INNER JOIN vCruiseShip ON CruiseOffers.cruiseShipID = vCruiseShip.shipID
WHERE (CruiseOffers.[F/C_Nts] > 0 AND CruiseOffers.[F/C_Fare] > .0000)
AND flyingDate <> '1900-01-01 00:00:00.000'
AND regionID <> '999'
AND flyingDate >= getdate()
GROUP BY voyageCode, itineraryDescription, flyingDate, [F/C_Nts], cruiseShip, cruiseLine, shipID, LineID, regionID
ORDER BY flyingDate ASC
March 27, 2008 at 6:15 am
CREATE FUNCTION dbo.fnFare
(
@LineID INT,
@Fare NUMERIC(18, 4)
)
RETURNS NUMERIC(18, 4)
AS
BEGIN
RETURNCASE @LineID
WHEN 1 THEN @Fare * 0.92
WHEN 2 THEN @Fare * 0.92
WHEN 3 THEN @Fare * 0.92
WHEN 4 THEN @Fare * 0.92
WHEN 5 THEN @Fare * 0.92
WHEN 8 THEN @Fare * 0.92
WHEN 9 THEN @Fare * 0.92
WHEN 10 THEN @Fare * 0.92
WHEN 12 THEN @Fare * 0.92
WHEN 13 THEN @Fare * 0.92
WHEN 14 THEN @Fare * 0.92
WHEN 15 THEN @Fare * 0.92
WHEN 20 THEN @Fare * 0.92
WHEN 22 THEN @Fare * 0.92
WHEN 24 THEN @Fare * 0.92
ELSE @Fare
END
END
GO
SELECTDISTINCT
TOP 500
CruiseOffers.voyageCode,
CruiseOffers.itineraryDescription,
CruiseOffers.flyingDate,
CruiseOffers.[F/C_Nts],
MIN(dbo.fnFare(vCruiseShip.LineID, CruiseOffers.[F/C_Fare])) AS [F/C_Fare],
CruiseOffers.regionID,
vCruiseShip.cruiseShip,
vCruiseShip.cruiseLine,
vCruiseShip.shipID,
vCruiseShip.LineID
FROMCruiseOffers
INNER JOINvCruiseShip ON CruiseOffers.cruiseShipID = vCruiseShip.shipID
WHERECruiseOffers.[F/C_Nts] > 0
AND CruiseOffers.[F/C_Fare] > 0.0
AND regionID <> '999'
AND flyingDate >= getdate()
GROUP BYvoyageCode,
itineraryDescription,
flyingDate,
[F/C_Nts],
regionID,
cruiseShip,
cruiseLine,
shipID,
LineID
ORDER BYflyingDate
N 56°04'39.16"
E 12°55'05.25"
March 27, 2008 at 7:02 am
Thanks Peso,
the function works a treat!
I noticed the function Numeric(18,4) for returning the money value.
I understand (18,4) has to do with the decimal point.
Can I ask a quick question why it uses 18,4? just to help me understand and learn from using the function correctly.
Good work and thanks again
Mark 🙂
March 27, 2008 at 7:10 am
I just grabbed something, since you didn't specify which datatype to use.
Go ahead and use MONEY if you prefer!
N 56°04'39.16"
E 12°55'05.25"
March 27, 2008 at 7:26 am
Why not store the data in a table and then it just becomes a straight calculation in your select? The following code would work and you would not have to change a function when a discount changes:
Create Table dbo.CruiseLineDiscounts
(
lineId Int Not Null,
Rate Decimal(5,4) Not Null
)
SELECT DISTINCT TOP 500
CruiseOffers.voyageCode,
CruiseOffers.itineraryDescription,
CruiseOffers.flyingDate,
CruiseOffers.[F/C_Nts],
-- replaces case statement
Case
When CruiseLineDiscounts.Rate Is Not Null ThenCruiseOffers.[F/C_Fare] * CruiseLineDiscounts.Rate
Else MIN(CruiseOffers.[F/C_Fare])
End AS [F/C_Fare],
CruiseOffers.regionID,
vCruiseShip.cruiseShip,
vCruiseShip.cruiseLine,
vCruiseShip.shipID,
vCruiseShip.LineID
FROM
CruiseOffers INNER JOIN
vCruiseShip ON
CruiseOffers.cruiseShipID = vCruiseShip.shipID LEFT OUTER JOIN
CruiseLineDiscounts On
vCruiseShip.LineId = CruiseLineDiscounts.LineId
WHERE
(CruiseOffers.[F/C_Nts] > 0 AND CruiseOffers.[F/C_Fare] > .0000) AND
flyingDate <> '1900-01-01 00:00:00.000' AND
regionID <> '999' AND
flyingDate >= getdate()
GROUP BY
voyageCode,
itineraryDescription,
flyingDate,
[F/C_Nts],
cruiseShip,
cruiseLine,
shipID,
LineID,
regionID
ORDER BY
flyingDate ASC
In this example I am assuming only Lines with a discount are stored in the table which is why I am using a Left Outer Join and checking for Is Not Null in the case statement. You could enter every Line and enter a rate of 1 for those that do not have a discount and then eliminate the case and the group by so the query would look like this:
SELECT DISTINCT TOP 500
CruiseOffers.voyageCode,
CruiseOffers.itineraryDescription,
CruiseOffers.flyingDate,
CruiseOffers.[F/C_Nts],
-- replaces case statement
CruiseOffers.[F/C_Fare] * CruiseLineDiscounts.Rate AS [F/C_Fare],
CruiseOffers.regionID,
vCruiseShip.cruiseShip,
vCruiseShip.cruiseLine,
vCruiseShip.shipID,
vCruiseShip.LineID
FROM
CruiseOffers INNER JOIN
vCruiseShip ON
CruiseOffers.cruiseShipID = vCruiseShip.shipID Inner JOIN
CruiseLineDiscounts On
vCruiseShip.LineId = CruiseLineDiscounts.LineId
WHERE
(CruiseOffers.[F/C_Nts] > 0 AND CruiseOffers.[F/C_Fare] > .0000) AND
flyingDate <> '1900-01-01 00:00:00.000' AND
regionID <> '999' AND
flyingDate >= getdate()
ORDER BY
flyingDate ASC
Some other things you could do with this is have effective and expiration dates on the discounts so that you could put changes in discounts out in the future and see the history of discounts as well.
I also think that this would offer better performance than the function as it provides set-based solution.
I know this is not a direct answer to your question, but I think ti offers flexibility and performance benefits.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 27, 2008 at 9:29 am
ok thanks again for the help and advice Peso.
Thanks Jack also for advising on other solutions and methods of achieving this.
Cheers Mark 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply