如下表和数据。
create table prices
(productKey int
,PriceType char(10)
,BeginDate date
,EndDate date
,price decimal(18,2))
insert into prices(productKey, PriceType,BeginDate,EndDate, price)
values
(1,'LIST','1-1-2010','1-15-2010',10),
(1,'LIST','1-16-2010','10-15-2010',20),
(1,'DISCOUNT','1-10-2010','1-15-2010',-5),
(2,'LIST','2-1-2010','10-15-2010',30),
(2,'LIST','10-16-2010','1-1-9999',35),
(2,'DISCOUNT','2-10-2010','10-25-2010',-10),
(2,'LIST','1-1-2010','1-15-2010',10),
(3,'DISCOUNT','1-12-2010','1-1-9999',-5),
(3,'LIST','1-16-2010','1-1-9999',10)
我需要记录插入计算的实际价格（列表折扣）为每个时间段相同的表。
例如，对于产品1，我应该有下面的“实际的”记录
Begin End Price
1-1-2010 1-9-2010 10
1-10-2010 1-15-2010 5
1-16-2010 10-15-2010 20
我种有它想通了，任何东西在那里打折标价跨度内开始，但我在做别的事情的损失。
谢谢您的帮助
编辑
可以有每多的ProductKey折扣，但折扣时段不重叠。 所以，你可以有一个在2010年，另一个在2012年，但在2010年而不是2。
另外，如果有人能拿出一个更好的标题，请这样做。 我的可怜的大脑在此时完全挑战。
EDIT2
这是SQL服务器2008R2。 我喜欢（这给了我一开始在这个方向或某人）美丽的一套基于答案，但将只是一个游标的解决方案，工作快乐。
Answer 1:
聪明的难题。
您需要重建所有的时间跨度。 要做到这一点，我拿出所有从价格范围的日期和重建可能的日期范围。
with alldates as (select d.*, ROW_NUMBER() over (partition by productkey order by thedate) as seqnum
from ((select productkey, BeginDate as thedate from prices)
union all
(select productkey, enddate as thedate from prices)
) d
),
datepair as (select d1.productkey, d1.thedate as BeginDate, d2.thedate as EndDate
from alldates d1 left outer join
alldates d2
on d1.seqnum = d2.seqnum - 1 and d1.productKey = d2.productKey
)
select dp.productkey, dp.BeginDate, dp.EndDate, SUM(p.price)
from datepair dp join
prices p
on dp.productkey = p.productkey and
dp.BeginDate >= p.BeginDate and
dp.EndDate <= p.EndDate
group by dp.productkey, dp.BeginDate, dp.EndDate
order by 1, 2, 3
我已经想过这个更多一些。 上面的基本思路是正确的。 其基本思路是，打破了时间维度为间隔，其中清单和折扣是在整个区间不变。 现在的问题是如何创造这些间隔，这是在datepairs别名。
这些间隔只有几个规则：
- 任何时间周期开始时一个datepair间隔可以开始。
- 任何时间段结束之后甲datepair间隔可以开始一天。
- 甲datepair间隔可以结束时任何时间段结束
- 甲datepair间隔可以结束的一天的任何时间周期开始之前
一旦我们有时间间隔，这是一个简单的事情，在适当的标价和折扣在该期间加入。 下面的查询使用这样的逻辑：
with begindates as (select distinct productKey, thedate
from ((select productkey, BeginDate as thedate from prices)
union all
(select productkey, dateadd(d, 1, enddate) as thedate from prices)
) d
),
enddates as (select distinct productKey, thedate
from ((select productkey, DATEADD(d, -1, begindate) as thedate from prices)
union all
(select productkey, enddate as thedate from prices)
) d
),
datepair as (select *
from (select d1.productkey, d1.thedate as BeginDate,
MIN(d2.thedate) as EndDate
from begindates d1 left outer join
enddates d2
on d1.productKey = d2.productKey and d1.thedate < d2.thedate
group by d1.productkey, d1.thedate
) t
where BeginDate <> EndDate
)
select dp.productkey, dp.BeginDate, dp.EndDate, SUM(p.price)
from datepair dp join
prices p
on dp.productkey = p.productkey and
dp.BeginDate >= p.BeginDate and
dp.EndDate <= p.EndDate
group by dp.productkey, dp.BeginDate, dp.EndDate
order by 1, 2, 3
Answer 2:
我相信这应该给你你在找什么。 关键是要计算所有独特的日期范围没有重叠的单品。
-- Get all end dates
SELECT ROW_NUMBER() OVER (PARTITION BY ProductKey ORDER BY EndDate) RowNum, ProductKey, EndDate
INTO #EndDates
FROM (SELECT ProductKey, EndDate
FROM prices
UNION
SELECT ProductKey, DATEADD(d, -1, BeginDate) AS EndDate
FROM prices) endDates
ORDER BY EndDate
-- Get all unique date ranges with no overlap
SELECT a.ProductKey, DATEADD(d, 1, a.EndDate) BeginDate, b.EndDate
INTO #DateRange
FROM #EndDates a
INNER JOIN #EndDates b
ON a.RowNum = b.RowNum - 1
AND a.ProductKey = b.ProductKey
ORDER BY productkey, enddate
-- Get actual price
SELECT d.ProductKey, d.BeginDate, d.EndDate, SUM(Price) ActualPrice
FROM prices p
INNER JOIN #DateRange d
ON p.ProductKey = d.ProductKey
AND p.BeginDate <= d.EndDate
AND p.EndDate >= d.BeginDate
GROUP BY d.ProductKey, d.BeginDate, d.EndDate
ORDER BY d.ProductKey, d.BeginDate, d.EndDate
-- Clean up
DROP TABLE #EndDates
DROP TABLE #DateRange
Answer 3:
为了使这更有趣一点，我添加另一部分将具有相同的价格作为较早段，以确保我把它们分开：
INSERT INTO prices(productKey, PriceType,BeginDate,EndDate, price)
VALUES (1, 'DISCOUNT', '5-2-2010', '5-8-2010', -15)
另外我有一个表称为tblDates由以下填充：
INSERT dbo.tblDates (date1)
SELECT TOP(65536) ROW_NUMBER()OVER(ORDER BY v1.number)-1
FROM master.dbo.spt_values v1 CROSS APPLY master.dbo.spt_values v2 WHERE v1.type='p' and v2.type='p'
GO
我给这里的脚本不要求。 但有它不能伤害速度并没有真正占用这么多空间。 下面是我的回答：
DECLARE @InitialBeginDate DATE
, @FinalEndDate DATE;
SELECT
@InitialBeginDate = MIN(BeginDate)
, @FinalEndDate = MAX(EndDate)
FROM prices
WHERE productKey = @ProductKey
CREATE TABLE #Dates
(
DateValue DATE NOT NULL
)
INSERT #Dates (DateValue)
SELECT
DATEADD(DAY, V.number, @InitialBeginDate)
FROM master..spt_values V
WHERE V.[type] = 'P'
AND V.number BETWEEN 0 AND DATEDIFF(DAY, @InitialBeginDate, @FinalEndDate)
;WITH MergedDays AS
(
SELECT
ListedDates.DateValue
, SUM(P.price) PriceOnDate
, DATEDIFF(DAY, @InitialBeginDate, ListedDates.DateValue) - DENSE_RANK() OVER(PARTITION BY SUM(P.price) ORDER BY ListedDates.DateValue, SUM(P.price)) DateGroup
FROM #Dates ListedDates
INNER JOIN prices P
ON P.BeginDate <= ListedDates.DateValue
AND P.EndDate >= ListedDates.DateValue
AND P.productKey = @ProductKey
GROUP BY
ListedDates.DateValue
)
SELECT
MIN(DateValue) AS SegmentBeginDate
, MAX(DateValue) AS SegmentEndDate
, MAX(PriceOnDate) AS SegmentPrice -- This is just to collapse it, it'll be the same for all records.
FROM MergedDays
GROUP BY DateGroup
ORDER BY SegmentBeginDate
DROP TABLE #Dates
有几个其他的答案了，所以这是做事情的另一种方式; 有许多。
文章来源: Dates and timespans with SQL
