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 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply