SQL 日期查询 - 此条件成立多长时间

Question

问题是这些客户在任何给定日期 SCSS 了多长时间。

我在对抗 Sybase

对于表history_data这个简化的表结构

table: history_of_jerkinessprocessing_date  name  is_jerk---------------  ----- -------20090101         Matt  true20090101         Bob   false        20090101         Alex  true        20090101         Carol true        20090102         Matt  true        20090102         Bob   true        20090102         Alex  false        20090102         Carol true        20090103         Matt  true        20090103         Bob   true        20090103         Alex  true        20090103         Carol false        

3 号的报告应该显示 Matt 一直是个 SCSS ，Alex 刚刚成为一个 SCSS ，而 Bob 已经 SCSS 了 2 天。

name    days jerky-----   ----------Matt    3Bob     2Alex    1

我想动态地找到这些时间跨度，所以如果我运行第二次的报告，我应该会得到不同的结果:

name    days_jerky-----   ----------Matt    2Bob     1Carol   2

这里的关键是尝试仅查找早于特定日期的连续跨度。我找到了一些线索，但这似乎是一个有非常聪明的棘手解决方案的问题。

Answer 1

我的 SQL Server 解决方案 - 与 Dems 相同，但我自己设置了一个最小基线。它假设没有间隙 - 即每个人每天都有一个条目。如果那不是真的，那么我将不得不循环。

DECLARE @run_date datetime
DECLARE @min_date datetime

SET @run_date = {d '2009-01-03'}

-- get day before any entries in the table to use as a false baseline date
SELECT @min_date = DATEADD(day, -1, MIN(processing_date)) FROM history_of_jerkiness

-- get last not a jerk date for each name that is before or on the run date
-- the difference in days between the run date and the last not a jerk date is the number of days as a jerk
SELECT [name], DATEDIFF(day, MAX(processing_date), @run_date)
FROM (
     SELECT processing_date, [name], is_jerk
     FROM history_of_jerkiness
     UNION ALL
     SELECT DISTINCT @min_date, [name], 0
     FROM history_of_jerkiness ) as data
WHERE is_jerk = 0
  AND processing_date <= @run_date
GROUP BY [name]
HAVING DATEDIFF(day, MAX(processing_date), @run_date) > 0

我用以下内容创建了测试表:

CREATE TABLE history_of_jerkiness (processing_date datetime, [name] varchar(20), is_jerk bit)

INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-01'}, 'Matt', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-01'}, 'Bob', 0)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-01'}, 'Alex', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-01'}, 'Carol', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-02'}, 'Matt', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-02'}, 'Bob', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-02'}, 'Alex', 0)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-02'}, 'Carol', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-03'}, 'Matt', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-03'}, 'Bob', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-03'}, 'Alex', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-03'}, 'Carol', 0)