与日期范围（经典ASP和SQL）工作 [英] Working with date ranges (Classic ASP and SQL)

问题描述

我要实现一个解决方案，两个日期范围可以相互重叠。重叠的日期内，我都数不过来有多少天互相重叠。有一次，我知道重叠的日子里，我可以计算出基于该公司每天附着的价格共图。

I have to implement a solution where two date ranges can overlap each other. within the overlapped dates, I have to count how many days overlap each other. Once I know the overlapped days I can calculate a total figure based on the price that's attached per day.

情景将是

一个客户预订酒店

客户的预订日期 - 17/02/2011到26/02/2011

Customer booking dates - 17/02/2011 to 26/02/2011

正常价格（全年） - 01/01/2011 - 31/12/2011（每天的价格：$ 30.00）为止

Normal price (All year) - 01/01/2011 - 31/12/2011 (price per day :$30.00)

特别优惠1日 - 01/01/2011至19/02/2011（每天的价格：$ 20.00美元）

Special Offer 1 dates - 01/01/2011 to 19/02/2011 (price per day :$20.00)

特别优惠2日 - 17/02/2011至24/02/2011（每天的价格：$ 10.00）

Special Offer 2 dates - 17/02/2011 to 24/02/2011 (price per day :$10.00)

在上述情况下，该算法应制定出的日期范围重叠最便宜的报价，并制定出的价格预订。如果没有可用的特价它使用正常的价格。

In the above scenario, the proposed algorithm should work out the cheapest offer that the date ranges overlap and work out the price for the booking. If there is no special offer available it uses the normal price.

因此​​，对于前两天的系统应该得到特别优惠1的价格，因为它是最便宜的价格。未来5天应特别优惠2价格，为未来两日这将是正常的价格。

So for the first two days the system should get the price from "special offer 1" as it's the cheapest available price. Next 5 days should be "Special offer 2 price" and for the next 2 days it'll be normal price.

我很感激看到两个SQL（使用MS-SQL服务器）或code基答案得到diffrenet意见。

I'd be grateful to see both SQL(using MS-SQL Server) or Code base answers to get the diffrenet views.

我希望这个问题是清楚的，并期待盼着看到答案。​​

I hope the question is clear and looking foward to see the answers.

提前感谢

推荐答案

让我们supose，对你应该申请价格最低的每一天。

Let's supose that for each day you should apply lowest price.

create function price ( @fromDate date, @toDate date) returns money
as
begin
 declare @iterator_day date
 declare @total money
 set @total = 0
 set @iterator_day = @fromDate
 WHILE @iterator_day < = @toDate
 begin
    select @total = @total + min( price )
    from offers
    where @iterator_day  between offers.fromFay and offers.toDay
    set @iterator_day = DATEADD (day , 1 , @iterator_day )
 end
 return @total
end

然后就可以调用函数在查询：

then you can call function in your query:

select 
   b.fromDay, b.toDay, dbo.price( b.fromDay, b.toDay )
from 
   booking b

这篇关于与日期范围（经典ASP和SQL）工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！