Το παρακατω ισως φανει χρησιμο.
Σημειωση: Ο τροπος υπολογισμου της τιμης εξαρταται απο την εκαστοτε προσεγγιση/ιδιαιτεροτητα (πχ. χρεωση της ημερας αφιξης κ οχι της αναχωρησης, χρεωση κ των δυο, χρεωση της ημερας αναχωρισης κ οχι αφιξης, χρεωση της προηγουμενης ημερας οταν η αφιξη ειναι πριν τις 10 το πρωι κτλ).
Το παραδειγμα παρακατω χρεωνει την ημερα αφιξης κ οχι την ημερα αναχωρησης, ανεξαρτητα απο την ωρα αφιξης.
Αυτο που αξιζει να σημειωθει, ειναι οτι για τον υπολογισμο της τιμης, υπολογιζονται ολες οι ημερες μεταξυ checkin -checkout. Για καθε μερα, λαμβανεται η τιμη κ προστιθεται (SUM) με τις τιμες ολων των ημερων.
Υπαρχει περιπτωση για μια συγκεκριμενη ημερα, να υπαρχουν δυο χρεωσεις (overlapping price periods per room) κ το παραδειγμα θα χρεωσει "διπλη, τριπλη κοκ" τιμη αναλογα με τον αριθμο των περιοδων που αλληλο-επικαλυπτονται (σε αυτην την περιπτωση, μια πιθανη προσεγγιση θα ηταν να χρεωθει η πιο ακριβη τιμη).
Οπως και να 'χει, το παραδειγμα δειχνει την προσεγγιση κ οχι τους υπολογισμους.
--create numbers helper function, limited to smallint input
CREATE FUNCTION dbo.Numbers_test(@n AS SMALLINT)
RETURNS TABLE
AS
RETURN
(
WITH
T0 AS (SELECT 1 AS c UNION ALL SELECT 1),
T1 AS (SELECT 1 AS c FROM T0 AS A CROSS JOIN T0 AS B),
T2 AS (SELECT 1 AS c FROM T1 AS A CROSS JOIN T1 AS B),
T3 AS (SELECT 1 AS c FROM T2 AS A CROSS JOIN T2 AS B),
T4 AS (SELECT 1 AS c FROM T3 AS A CROSS JOIN T3 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM T4)
SELECT TOP (CAST(@n AS BIGINT)) n
FROM Nums
ORDER BY n
)
GO
--create customers table
CREATE TABLE dbo.customers_test(CustID INT, CustName NVARCHAR(100), RoomID INT, CheckIn_Date DATETIME, CheckOut_Date DATETIME)
GO
--fill in some customers
INSERT INTO dbo.customers_test(CustID, CustName, RoomID, CheckIn_Date, CheckOut_Date)
VALUES(1, 'pelatis 1', 100, '20110201', '20110220'),
(2, 'pelatis 2', 200, '20110301', '20110315'),
(3, 'pelatis 3', 300, '20110401', '20110410'),
(4, 'pelatis 4', 400, '20110101', '20110107'),
(5, 'pelatis 5', 100, '20110110', '20110107'), --invalid reservation (checkout < checkin)
(6, 'pelatis 6', 100, '20110115', '20110115') --invalid reservation (checkout < checkin)
GO
--create room prices table
CREATE TABLE dbo.RoomPrices_test(RoomID INT, SeasonStart DATETIME, SeasonEnd DATETIME, Price MONEY)
GO
--fill in some prices
INSERT INTO dbo.RoomPrices_test(RoomID, SeasonStart, SeasonEnd, Price)
VALUES (100, '20110101', '20110131', 50),
(100, '20110201', '20110210', 60),
(100, '20110217', '20110228', 100), --there is no price period for room 100 between 20110211 and 20110216
(200, '20110301', '20110305', 200),
(200, '20110306', '20110311', 300),
(200, '20110312', '20110316', 50),
(300, '20110101', '20110531', 80)
--no room prices for roomid = 400
GO
select * from dbo.customers_test
select * from dbo.RoomPrices_test
GO
--example of dates that will be charged
DECLARE @checkin_date DATETIME, @checkout_date DATETIME
SELECT @checkin_date = '20110101', @checkout_date = '20110120'
SELECT 'charge dates for checkin:' + CONVERT(VARCHAR(8), @checkin_date, 112) + ' and checkout:' + CONVERT(VARCHAR(8), @checkout_date, 112)
SELECT DATEADD(dd, n-1, DATEADD(dd, DATEDIFF(dd, '19000101', @checkin_date), '19000101')) AS chrg_date
FROM dbo.Numbers_test(DATEDIFF(dd, @checkin_date, @checkout_date))
GO
--inline table valued function
CREATE FUNCTION dbo.RoomPricing1(@RoomID INT, @CheckIn_Date DATETIME, @CheckOut_Date DATETIME)
RETURNS TABLE
AS
RETURN
(
SELECT SUM(rp.Price) AS TotalPrice, MAX(CASE WHEN rp.RoomID IS NULL THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END) AS missing_prices
FROM
(
SELECT DATEADD(dd, n-1, DATEADD(dd, DATEDIFF(dd, '19000101', @checkin_date), '19000101')) AS chrg_date
FROM dbo.Numbers_test(DATEDIFF(dd, @checkin_date, @checkout_date))
WHERE DATEDIFF(dd, @checkin_date, @checkout_date) > 0
UNION ALL
SELECT DATEADD(dd, DATEDIFF(dd, '19000101', @checkin_date), '19000101')
WHERE DATEDIFF(dd, @checkout_date, @checkin_date) = 0
) AS charge_dates
--pay attention: multiple price periods for one day are not handled
LEFT JOIN dbo.RoomPrices_test AS rp ON rp.RoomID = @RoomID AND charge_dates.chrg_date BETWEEN rp.SeasonStart AND rp.SeasonEnd
)
GO
--scalar function
CREATE FUNCTION dbo.RoomPricing2(@RoomID INT, @CheckIn_Date DATETIME, @CheckOut_Date DATETIME)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @price VARCHAR(20), @missing_prices TINYINT
SELECT @price = SUM(rp.Price), @missing_prices = MAX(CASE WHEN rp.RoomID IS NULL THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END)
FROM
(
SELECT DATEADD(dd, n-1, DATEADD(dd, DATEDIFF(dd, '19000101', @checkin_date), '19000101')) AS chrg_date
FROM dbo.Numbers_test(DATEDIFF(dd, @checkin_date, @checkout_date))
WHERE DATEDIFF(dd, @checkin_date, @checkout_date) > 0
UNION ALL
SELECT DATEADD(dd, DATEDIFF(dd, '19000101', @checkin_date), '19000101')
WHERE DATEDIFF(dd, @checkout_date, @checkin_date) = 0
) AS charge_dates
--pay attention: multiple price periods for one day are not handled
LEFT JOIN dbo.RoomPrices_test AS rp ON rp.RoomID = @RoomID AND charge_dates.chrg_date BETWEEN rp.SeasonStart AND rp.SeasonEnd
SET @price = CASE @missing_prices WHEN 1 THEN '!!!!' ELSE '' END + ISNULL(@price, 'no price')
RETURN(@price)
END
GO
--use inline table valued udf
SELECT c.*, rp.*
FROM dbo.customers_test AS c
CROSS APPLY dbo.RoomPricing1(c.RoomID, c.CheckIn_Date, c.CheckOut_Date) AS rp --cross apply as dbo.RoomPricing1 returns always οne row (aggregates are used)
--use scalar udf
SELECT c.*, dbo.RoomPricing2(c.RoomID, c.CheckIn_Date, c.CheckOut_Date) AS price
FROM dbo.customers_test AS c
--cleanup
DROP FUNCTION dbo.RoomPricing1
DROP FUNCTION dbo.RoomPricing2
DROP FUNCTION dbo.Numbers_test
DROP TABLE dbo.customers_test
DROP TABLE dbo.roomprices_test
--ΗΤΗ--