在 SQL Server 2014 中用日期划分行 [英] Divide rows with date in SQL Server 2014

查看：27 发布时间：2021/8/27 19:15:55 sql sql-server sql-server-2014

本文介绍了在 SQL Server 2014 中用日期划分行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有 SQL 问题.我有下表:

声明@t 表(START_DATE 日期时间，END_DATE 日期时间，GROSS_SALES_PRICE 十进制(10,2));插入@t值 ('2014-08-06 00:00:00.000', '2014-10-06 23:59:59.000', 29.99),('2014-09-06 00:00:00.000', '2014-09-09 23:59:59.000', 32.99),('2014-09-10 00:00:00.000', '2014-09-30 23:59:59.000', 32.99),('2014-10-07 00:00:00.000', '2049-12-31 23:59:59.000', 34.99)

我想将重叠的日期分开.例如，我在第一行 START_DATE 2014-08-06 和 END_DATE 2014-10-06.我们可以看到第二行和第三行的日期都在第一行的这段时间内.

所以我想将它们分开如下:

声明@t2 表(START_DATE 日期时间，END_DATE 日期时间，GROSS_SALES_PRICE 十进制(10,2));插入@t2值 ('2014-08-06 00:00:00.000', '2014-09-05 23:59:59.000', 29.99),('2014-09-06 00:00:00.000', '2014-09-09 23:59:59.000', 32.99),('2014-09-10 00:00:00.000', '2014-09-30 23:59:59.000', 32.99),('2014-10-01 00:00:00.000', '2014-10-06 23:59:59.000', 29.99),('2014-10-07 00:00:00.000', '2049-12-31 23:59:59.000', 34.99)

所以第二行和第三行保持不变.第一行应该有新的 END_DATE.我们也有新行.GROSS_SALES_PRICE 应保持内部期间的状态.感谢帮助.我使用的是 SQL Server 2014

解决方案

日历/日期表可以简化这一点，但我们也可以使用查询生成临时日期表，使用常用表表达式.

从那里，我们可以将其作为间隙和岛屿样式问题来解决.使用日期表并使用 outer apply() 获取 start_date 和 gross_sales_price 的最新值，我们可以确定我们想要重新分配的组 -通过使用两个 row_number()s.第一个只是按 date 排序，减去另一个按我们最新的 start_date 的值划分并按 date 排序.>

然后您可以将公共表表达式 src 的结果转储到临时表并使用该表进行插入/删除，或者您可以使用 使用 merge源代码.

/* -- 日期 --*/声明@fromdate 日期时间，@thrudate 日期时间；选择@fromdate = min(start_date), @thrudate = max(end_date) from #t;;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))t(n)), 日期为 (选择顶部 (datediff(day, @fromdate, @thrudate)+1)[Date]=convert(datetime,dateadd(day,row_number() over(order by (select 1))-1,@fromdate)), [End_Date]=convert(datetime,dateadd(millisecond,-3,dateadd(day,row_number() over(order by (select 1)),@fromdate)))从 n as deka cross join n as hecto cross join n askilo交叉连接 n 为十K 交叉连接 n 为百K按 [日期] 订购)/* -- 岛屿 -- */, cte 为 (选择开始日期 = d.date, end_date = d.end_date, x.gross_sales_price, grp = row_number() over (order by d.date)- row_number() over(按 x.start_date 分区按 d.date 排序)从日期 d外申请(选择前 1 名 l.start_date, l.gross_sales_price从#t l其中 d.date >= l.start_date和 d.date <= l.end_date按 l.start_date desc 排序) X)/* -- 聚合岛 -- */, src 为 (选择开始日期 = 分钟(开始日期), end_date = max(end_date), 总销售额_价格来自 cte按gross_sales_price, grp 分组)/* -- 合并 -- */将 #t 与 (holdlock) 合并为目标使用 src 作为源在 target.start_date = source.start_date和 target.end_date = source.end_date和 target.gross_sales_price = source.gross_sales_price当与目标不匹配时然后插入(start_date，end_date，gross_sales_price)值(start_date、end_date、gross_sales_price)当与来源不匹配时然后删除输出 $action，已插入.*，已删除.*；/*  -  结果  -  */选择开始日期， 结束日期, 总销售额_价格从T按开始日期排序

rextester 演示:http://rextester.com/MFXCQQ90933

merge 输出(你不需要输出这个，只是为了演示):

+---------+---------------------+-------------------+-------------------+--------------+---------------------+-------------------+|$动作|开始日期 |END_DATE |GROSS_SALES_PRICE |开始日期 |END_DATE |GROSS_SALES_PRICE |+---------+----------------------+---------------------+--------------------+--------------------+---------------------+-------------------+|插入 |2014-10-01 00:00:00 |2014-10-06 23:59:59 |29.99 |空 |空 |空 ||插入 |2014-08-06 00:00:00 |2014-09-05 23:59:59 |29.99 |空 |空 |空 ||删除 |空 |空 |空 |2014-08-06 00:00:00 |2014-10-06 23:59:59 |29.99 |+---------+----------------------+---------------------+--------------------+--------------------+---------------------+-------------------+

结果:

+-------------------------+--------------------+-------------------+|开始日期 |结束日期 |总销售额_价格|+---------------+----------------+-------------------+|2014-08-06 00:00:00.000 |2014-09-05 23:59:59.997 |29.99 ||2014-09-06 00:00:00.000 |2014-09-09 23:59:59.997 |32.99 ||2014-09-10 00:00:00.000 |2014-09-30 23:59:59.997 |32.99 ||2014-10-01 00:00:00.000 |2014-10-06 23:59:59.997 |29.99 ||2014-10-07 00:00:00.000 |2049-12-31 23:59:59.997 |34.99 |+---------------+----------------+-------------------+

日历和数字表参考:

merge 参考:

I have a problem with SQL. I have the following table:

declare @t table (START_DATE datetime,
                  END_DATE datetime, 
                  GROSS_SALES_PRICE decimal(10,2)
                 );

insert into @t 
values ('2014-08-06 00:00:00.000', '2014-10-06 23:59:59.000', 29.99),
       ('2014-09-06 00:00:00.000', '2014-09-09 23:59:59.000', 32.99),
       ('2014-09-10 00:00:00.000', '2014-09-30 23:59:59.000', 32.99),
       ('2014-10-07 00:00:00.000', '2049-12-31 23:59:59.000', 34.99)

I would like to separate the dates which overlaps. For example I have in the first row START_DATE 2014-08-06 and END_DATE 2014-10-06. We can see that the dates from the second and the third row are inside this period of time from first row.

So I would like to separate them as follows:

declare @t2 table (START_DATE datetime,
                   END_DATE datetime, 
                   GROSS_SALES_PRICE decimal(10,2)
                  );

insert into @t2 
values ('2014-08-06 00:00:00.000', '2014-09-05 23:59:59.000', 29.99),
       ('2014-09-06 00:00:00.000', '2014-09-09 23:59:59.000', 32.99),
       ('2014-09-10 00:00:00.000', '2014-09-30 23:59:59.000', 32.99),
       ('2014-10-01 00:00:00.000', '2014-10-06 23:59:59.000', 29.99),
       ('2014-10-07 00:00:00.000', '2049-12-31 23:59:59.000', 34.99)

So the second and the third rows remained unchanged. The first row should have new END_DATE. We also have new row. The GROSS_SALES_PRICE should remain as it is in internal period. Thanks for help. I am using SQL Server 2014

解决方案

A calendar/dates table can simplify this, but we can also use a query to generate a temporary dates table using a common table expression.

From there, we can solve this as a gaps and islands style problem. Using the dates table and using outer apply() to get the latest values for start_date and gross_sales_price we can identify the groups we want to re-aggregate by using two row_number()s. The first just ordered by date, less the other that is partitioned by the value we have as the latest start_date and ordered by date.

Then you can dump the results of the common table expression src to a temporary table and do your inserts/deletes using that or you can use merge using src.

/* -- dates --*/
declare @fromdate datetime, @thrudate datetime;
select  @fromdate = min(start_date), @thrudate = max(end_date) from #t;
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
      [Date]=convert(datetime,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
    , [End_Date]=convert(datetime,dateadd(millisecond,-3,dateadd(day,row_number() over(order by (select 1)),@fromdate)))
  from n as deka cross join n as hecto cross join n as kilo
                 cross join n as tenK cross join n as hundredK
   order by [Date]
)
/* -- islands -- */
, cte as (
select 
    start_date = d.date
  , end_date   = d.end_date
  , x.gross_sales_price
  , grp = row_number() over (order by d.date)
        - row_number() over (partition by x.start_date order by d.date)
from dates d
  outer apply (
    select top 1 l.start_date, l.gross_sales_price
    from #t l
    where d.date >= l.start_date
      and d.date <= l.end_date
    order by l.start_date desc
    ) x
)
/* -- aggregated islands -- */
, src as (
select 
    start_date = min(start_date)
  , end_date   = max(end_date)  
  , gross_sales_price
from cte
group by gross_sales_price, grp
)
/* -- merge -- */
merge #t with (holdlock) as target
using src as source
  on target.start_date = source.start_date
 and target.end_date   = source.end_date
 and target.gross_sales_price = source.gross_sales_price
when not matched by target 
  then insert (start_date, end_date, gross_sales_price)
    values (start_date, end_date, gross_sales_price)
when not matched by source 
  then delete
output $action, inserted.*, deleted.*;
/* -- results -- */
select 
    start_date
  , end_date  
  , gross_sales_price
from #t 
order by start_date

rextester demo: http://rextester.com/MFXCQQ90933

merge output (you do not need to output this, just showing for the demo):

+---------+---------------------+---------------------+-------------------+---------------------+---------------------+-------------------+
| $action |     START_DATE      |      END_DATE       | GROSS_SALES_PRICE |     START_DATE      |      END_DATE       | GROSS_SALES_PRICE |
+---------+---------------------+---------------------+-------------------+---------------------+---------------------+-------------------+
| INSERT  | 2014-10-01 00:00:00 | 2014-10-06 23:59:59 | 29.99             | NULL                | NULL                | NULL              |
| INSERT  | 2014-08-06 00:00:00 | 2014-09-05 23:59:59 | 29.99             | NULL                | NULL                | NULL              |
| DELETE  | NULL                | NULL                | NULL              | 2014-08-06 00:00:00 | 2014-10-06 23:59:59 | 29.99             |
+---------+---------------------+---------------------+-------------------+---------------------+---------------------+-------------------+

results:

+-------------------------+-------------------------+-------------------+
|       start_date        |        end_date         | gross_sales_price |
+-------------------------+-------------------------+-------------------+
| 2014-08-06 00:00:00.000 | 2014-09-05 23:59:59.997 | 29.99             |
| 2014-09-06 00:00:00.000 | 2014-09-09 23:59:59.997 | 32.99             |
| 2014-09-10 00:00:00.000 | 2014-09-30 23:59:59.997 | 32.99             |
| 2014-10-01 00:00:00.000 | 2014-10-06 23:59:59.997 | 29.99             |
| 2014-10-07 00:00:00.000 | 2049-12-31 23:59:59.997 | 34.99             |
+-------------------------+-------------------------+-------------------+

calendar and numbers tables reference:

merge reference:

这篇关于在 SQL Server 2014 中用日期划分行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

在 SQL Server 2014 中用日期划分行 [英] Divide rows with date in SQL Server 2014

问题描述

相关文章

数据库最新文章

热门教程

热门工具

登录关闭

在 SQL Server 2014 中用日期划分行 [英] Divide rows with date in SQL Server 2014

问题描述

相关文章

数据库最新文章

热门教程

热门工具

登录 关闭

登录关闭