时间戳

首页 > 解决方案 > SQL Server 在表中查找序列日期并在可能的情况下创建句点

sql - SQL Server 在表中查找序列日期并在可能的情况下创建句点

问题描述

我有一个带有单个日期（并不总是按顺序）的程序，我希望它的结果在可能的情况下转换为智能时段。我正在使用 SQL Server 2008R2

这是数据示例 [编辑：请注意 8 日和 9 日之间存在差距]：

它具有OnDate、整个结果相同的RateCode、TypeCode、Charge和Adults。

我想通过排序具有相同TypeCode、Charge和Adults的OnDate来创建周期。

这是一个虚拟表以及数据脚本

CREATE TABLE SomeTable
(OnDate         datetime        null,
 RateCode       varchar(10)     null,
 TypeCode       varchar(10)     null,
 Charge         numeric(10,2)   null,
 Adults         int             null)
 go

INSERT someTable
SELECT '2014-05-01 00:00:00.000', 'RATE1','jnr',300.00,  2
INSERT someTable
SELECT '2014-05-01 00:00:00.000', 'RATE1','dbl',200.00,  2
INSERT someTable
SELECT '2014-05-02 00:00:00.000', 'RATE1','jnr',250.00 , 2
INSERT someTable
SELECT '2014-05-02 00:00:00.000', 'RATE1','dbl' ,150.00, 2
INSERT someTable
SELECT '2014-05-03 00:00:00.000', 'RATE1','jnr',250.00,  2
INSERT someTable
SELECT '2014-05-03 00:00:00.000', 'RATE1','dbl',150.00,  2
INSERT someTable
SELECT '2014-05-04 00:00:00.000', 'RATE1','jnr',250.00,  2
INSERT someTable
SELECT '2014-05-04 00:00:00.000', 'RATE1','dbl',150.00,  2
INSERT someTable
SELECT '2014-05-05 00:00:00.000', 'RATE1','jnr',300.00,  2
INSERT someTable
SELECT '2014-05-05 00:00:00.000', 'RATE1','dbl',200.00,  2
INSERT someTable
SELECT '2014-05-06 00:00:00.000', 'RATE1','jnr',250.00,  2
INSERT someTable
SELECT '2014-05-06 00:00:00.000', 'RATE1','dbl',150.00,  2
INSERT someTable
SELECT '2014-05-07 00:00:00.000', 'RATE1','jnr',250.00,  2
INSERT someTable
SELECT '2014-05-07 00:00:00.000', 'RATE1','dbl',150.00,  2
INSERT someTable
SELECT '2014-05-10 00:00:00.000', 'RATE1','jnr',250.00,  2
INSERT someTable
SELECT '2014-05-10 00:00:00.000', 'RATE1','dbl',150.00,  2

这是结果示例

2014-05-01 00:00:00.000 RATE1   jnr         300.00  2
2014-05-01 00:00:00.000 RATE1   dbl         200.00  2
2014-05-02 00:00:00.000 RATE1   jnr         250.00  2
2014-05-02 00:00:00.000 RATE1   dbl         150.00  2
2014-05-03 00:00:00.000 RATE1   jnr         250.00  2
2014-05-03 00:00:00.000 RATE1   dbl         150.00  2
2014-05-04 00:00:00.000 RATE1   jnr         250.00  2
2014-05-04 00:00:00.000 RATE1   dbl         150.00  2
2014-05-05 00:00:00.000 RATE1   jnr         300.00  2
2014-05-05 00:00:00.000 RATE1   dbl         200.00  2
2014-05-06 00:00:00.000 RATE1   jnr         250.00  2
2014-05-06 00:00:00.000 RATE1   dbl         150.00  2
2014-05-07 00:00:00.000 RATE1   jnr         250.00  2
2014-05-07 00:00:00.000 RATE1   dbl         150.00  2
2014-05-10 00:00:00.000 RATE1   jnr         250.00  2
2014-05-10 00:00:00.000 RATE1   dbl         150.00  2

我正在尝试将其转换为类似的东西：

2014-05-01 00:00:00.000 2014-05-01 00:00:00.000 RATE1   jnr         300.00  2
2014-05-01 00:00:00.000 2014-05-01 00:00:00.000 RATE1   dbl         200.00  2
2014-05-02 00:00:00.000 2014-05-04 00:00:00.000 RATE1   jnr         250.00  2
2014-05-02 00:00:00.000 2014-05-04 00:00:00.000 RATE1   dbl         150.00  2
2014-05-05 00:00:00.000 2014-05-05 00:00:00.000 RATE1   jnr         300.00  2
2014-05-05 00:00:00.000 2014-05-05 00:00:00.000 RATE1   dbl         200.00  2
2014-05-06 00:00:00.000 2014-05-07 00:00:00.000 RATE1   jnr         250.00  2
2014-05-06 00:00:00.000 2014-05-07 00:00:00.000 RATE1   dbl         150.00  2
2014-05-10 00:00:00.000 2014-05-10 00:00:00.000 RATE1   jnr         250.00  2
2014-05-10 00:00:00.000 2014-05-10 00:00:00.000 RATE1   dbl         150.00  2

任何帮助表示赞赏。

谢谢

标签： sqlsql-serverdate

解决方案

这是一种你想要的差距和孤岛问题，我认为你只想要每个///组合的下一个ondate值。ratecodetypecodechargeadults

对于这个版本，行号的差异似乎就足够了：

select RateCode, TypeCode, Charge, Adults,
       min(ondate), max(ondate)
from (select st.*,
             row_number() over (partition by RateCode, TypeCode order by ondate) as seqnum,
             row_number() over (partition by RateCode, TypeCode, Charge, adults order by ondate) as seqnum_2
      from sometable st
     ) st
group by RateCode, TypeCode, Charge, Adults, (seqnum - seqnum_2)
order by RateCode, TypeCode, min(ondate);

这是一个 db<>fiddle。

编辑：

如果您想考虑日期的差距，那么逻辑会略有不同：

select RateCode, TypeCode, Charge, Adults, dateadd(day, - seqnum, ondate),
       min(ondate), max(ondate)
from (select st.*,
             row_number() over (partition by RateCode, TypeCode, Charge, Adults order by ondate) as seqnum
      from sometable st
     ) st
group by RateCode, TypeCode, Charge, Adults, dateadd(day, - seqnum, ondate)
order by RateCode, TypeCode, min(ondate);

我用这两种解决方案更新了 SQL Fiddle。

推荐阅读