SQL Server 2008 - SQL：从这样的数据中获取周期开始和结束日期时间？我正在努力解决的棘手小难题

Question

我有一个表，它列出了一年内的每个日期。每行还包含一个周期数，从1到13，每年循环一次。它看起来像这样:

|        Date         | Period |
| 2012-12-27 00:00:00 |   12   |
| 2012-12-28 00:00:00 |   12   |
| 2012-12-29 00:00:00 |   13   |
| 2012-12-30 00:00:00 |   13   |
| 2012-12-31 00:00:00 |   13   |
| 2013-01-01 00:00:00 |   13   |
| 2013-01-02 00:00:00 |   13   |
|        . . .        |   13   | 
| 2013-02-10 00:00:00 |   01   |
|        . . .        |   01   | 
| 2013-03-14 00:00:00 |   02   |
|        . . .        |   02   | 
| 2013-05-05 00:00:00 |   03   |

如上图所示，第13期开始于2012年12月29日，结束于2013年2月09日。我通过得到第13期的第一个和最后一个日期来计算。

我需要编写一个查询，将获得当前周期的周期开始和结束日期。但是我遇到了问题，因为周期有时一年出现两次，有时像这个例子一样在年份之间重叠。

再举几个例子:

如果getDate() = '2013-02-25 13:45:00'则周期应为01，开始和结束日期应为'2013-02-10 00:00:00'和'2013-03-14 00:00:00'

如果getDate() = '2013-03-15 00:00:00'，那么周期应该是02，开始和结束日期应该是'2013-03-14 00:00:00'和'2013-05-05 00:00:00'

我真的希望这是有意义的。如果我需要澄清，请告诉我!谢谢你的帮助

Answer 1

如果您使用的是SQL Server 2012，您将使用lag或lead。相反，我将使用相关子查询:

select min(period) as period, MIN(date), MAX(date)
from (select t.*,
             (select min(date) from t t2 where t2.period <> t.period and t2.date > t.date) as nextp
      from t
     ) t
group by nextp

内部子查询获取下一个周期的日期。同一时期记录的连续记录相同。然后我可以用它来分组。

Answer 2

两个递归cte，用于在一个周期中查找开始日期和结束日期。

with CStart as
(
  select Date,
         Period
  from DateTable
  where Date = cast(getdate() as date)
  union all
  select D.Date,
         D.Period
  from DateTable as D
    inner join CStart
      on dateadd(day, -1, CStart.Date) = D.Date and
         CStart.Period = D.Period
),
CEnd as
(
  select Date,
         Period
  from DateTable
  where Date = cast(getdate() as date)
  union all
  select D.Date,
         D.Period
  from DateTable as D
    inner join CEnd
      on dateadd(day, 1, CEnd.Date) = D.Date and
         CEnd.Period = D.Period
),
CPeriod as
(
  select Period
  from DateTable
  where Date = cast(getdate() as date)
)
select Period,
       (select min(Date) from CStart) as StartDate,
       (select max(Date) from CEnd) as EndDate 
from CPeriod

SE-Data的检验

Answer 3

试试这段代码

DECLARE @TODAYSPERIOD AS VARCHAR(2)
SET @TODAYSPERIOD = (SELECT PERIOD FROM MYTABLE
                    WHERE DATE = (DATEADD(D, DATEDIFF(D, 0, GETDATE()), 0)))
SELECT MAX(DATE), MIN(DATE), @TODAYSPERIOD FROM MYTABLE
WHERE PERIOD = @TODAYSPERIOD AND DATE BETWEEN ((DATEADD(D, DATEDIFF(D, 0, GETDATE()), 0)) - 45) AND ((DATEADD(D, DATEDIFF(D, 0, GETDATE()), 0)) + 45)

本质上，我们使用你的表(在上面的代码中，它被引用为'MYTABLE')来查找周期，然后只取45天半径内的日期(在当前日期之前和之后)，这些日期被列为与今天相同的周期。

希望这对你有帮助!

Answer 4

您可以尝试(在我的测试数据中，56是给定时间段内的最大天数)

SELECT A.period
     , MIN( B.date )
     , MAX( B.date )
  FROM dates A
 INNER JOIN dates B
    ON A.date >= DATEADD( dd, -56, B.date )
   AND A.date <= DATEADD( dd, 56, B.date )
   AND A.period = B.period 
 GROUP BY A.period

Answer 5

如果-且仅当 -表中没有日期间隔并且每个日期只有一行，您可以使用:

; WITH cte AS
  ( SELECT
        a.Date AS StartDate
      , b.Date AS EndDate
      , a.Period
      , ROW_NUMBER() OVER (ORDER BY a.Date) AS rn
    FROM tableX AS a
      LEFT JOIN tableX AS b
        ON  DATEADD(day, -1, a.Date) = b.Date
    WHERE a.Period <> b.Period 
       OR b.Date IS NULL 
  )
SELECT
    a.StartDate
  , COALESCE(b.EndDate, (SELECT MAX(Date) FROM tableX)) AS EndDate
  , a.Period
FROM
    cte AS a
  LEFT JOIN 
    cte AS b
      ON a.rn + 1 = b.rn ;

test in SQL-Fiddle