根据单行的日期范围分成多行 [英] Break into multiple rows based on date range of a single row
问题描述
我有一个记录约会的表格，有些是单天约会，有些是多天约会，所以数据看起来像
I have a table which captures appointments, some are single day appointments and some are multi day appointments, so the data looks like
AppointmentId StartDate EndDate
9 2017-04-12 2017-04-12
10 2017-05-01 2017-05-03
11 2017-06-01 2017-06-01
我想将多天约会拆分为单天，所以我想要达到的结果就像
I want to split the multi day appointment as single days, so the result I am trying to achieve is like
AppointmentId StartDate EndDate
9 2017-04-12 2017-04-12
10 2017-05-01 2017-05-01
10 2017-05-02 2017-05-02
10 2017-05-03 2017-05-03
11 2017-06-01 2017-06-01
所以我将约会 ID 10 拆分为多行.我检查了其他一些问题，例如这里但那些只是基于单个开始日期和结束日期而不是基于表数据进行拆分
So I have split the appointment id 10 into multiple rows. I checked a few other questions like here but those are to split just based on a single start date and end date and not based on table data
推荐答案
对于此类事情，您可以使用日历或日期表.
You can use a Calendar or dates table for this sort of thing.
对于只有 152kb 的内存，你可以在一个表中包含 30 年的日期:
For only 152kb in memory, you can have 30 years of dates in a table with this:
/* dates table */
declare @fromdate date = '20000101';
declare @years int = 30;
/* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
[Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
into dbo.Dates
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];
create unique clustered index ix_dbo_Dates_date
on dbo.Dates([Date]);
无需执行创建表的实际步骤，您可以在 普通表表达式，仅此:
Without taking the actual step of creating a table, you can use it inside a common table expression with just this:
declare @fromdate date = '20161229';
declare @thrudate date = '20170103';
;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(date,dateadd(day,row_number() over(order by (select 1))-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]
)
select [Date]
from dates;
像这样使用:
Use either like so:
select
t.AppointmentId
, StartDate = d.date
, EndDate = d.date
from dates d
inner join appointments t
on d.date >= t.StartDate
and d.date <= t.EndDate
rextester 演示:http://rextester.com/TNWQ64342
rextester demo: http://rextester.com/TNWQ64342
返回:
+---------------+------------+------------+
| AppointmentId | StartDate | EndDate |
+---------------+------------+------------+
| 9 | 2017-04-12 | 2017-04-12 |
| 10 | 2017-05-01 | 2017-05-01 |
| 10 | 2017-05-02 | 2017-05-02 |
| 10 | 2017-05-03 | 2017-05-03 |
| 11 | 2017-06-01 | 2017-06-01 |
+---------------+------------+------------+
数字和日历表参考:
Number and Calendar table reference:
- 生成一个没有循环的集合或序列 - 1- 亚伦·伯特兰
- 生成一个没有循环的集合或序列 - 2- 亚伦·伯特兰
- 生成一个没有循环的集合或序列 - 3- 亚伦·伯特兰
- 数字"或计数"表:它是什么以及它如何替换循环 - Jeff Moden
- 创建日期表/sql Server 2008 中的维度 - David Stein
- 日历表 - 为什么需要日历表 -大卫·斯坦
- 在 sql Server 中创建日期维度或日历表 - Aaron Bertrand
- tsql 函数确定sql Server 中的假期 - Aaron Bertrand
- F_table_date - 迈克尔瓦伦丁琼斯
这篇关于根据单行的日期范围分成多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
