如何在sqlite中找到剩余的日期范围

Question

我有一个sqlite数据库，如

ID Fromdate Todate

1 2023-04-02 2023-04-07

2 2023-04-05 2023-04-06

3 2023-04-09 2023-04-11

2 2023-04-09 2023-04-16

易被诱惑的。

现在我得到堆栈创建查询sqlite

当我搜索日期从2023-04-02到2023-04-30时，应该填充每个id的剩余日期范围。

预期输出

ID Fromdate Todate

1 2023-04-08 2023-04-30

2 2023-04-02 2023-04-04

2 2023-04-07 2023-04-08

2 2023-04-17 2023-04-30

3 2023-04-02 2023-04-08

3 2023-04-12 2023-04-30

Please Help me

我尝试了很多事但都失败了

Answer 1

对于每个ID，您希望在所需周期内不包含原始间隔集的天数间隔。

前提是您的原始间隔在同一id内不重叠。求左、右间隔和并集

with tbl(ID, Fromdate, Todate) as (
-- sample data
select 1, julianday('2023-04-02'), julianday('2023-04-07') union all
select 2, julianday('2023-04-05'), julianday('2023-04-06') union all
select 3, julianday('2023-04-09'), julianday('2023-04-11') union all
select 2, julianday('2023-04-09'), julianday('2023-04-16') 
),
params (ds, de) as (
-- period to search within
select julianday('2023-04-02'), julianday('2023-04-30')
), 
leftintvls as (
-- the closest end of intervals relative to current interval start
select id,  
(select max(t2.Todate)+1  
from tbl t2 
where t2.id = t.id and t2.Todate < t.Fromdate ) ms, Fromdate-1 me
from tbl t
-- only itervals overlapping with the requierd period
join params p on t.Fromdate <= p.de and p.ds <= t.Todate 
--  starting later than required period starts
and t.Fromdate > p.ds
),
rightintvls as (
-- the closest start of interval relative current interval end
select id, Todate+1 ms,  
(select min(t2.Fromdate) - 1  
from tbl t2 
where t2.id = t.id and t2.Fromdate > t.Todate ) me
from tbl t
-- only itervals overlapping with the requierd period
join params p on t.Fromdate <= p.de and p.ds <= t.Todate 
--  ending earlier than required period ends
and t.Todate < p.de  
)
select id, date(ifnull(ms, p.ds)) fromd, date(me) tod
from leftintvls
cross join params p
where ifnull(ms, p.ds) <= me  

union 

select id, date(ms),  date(ifnull(me, p.de))
from rightintvls
cross join params p
where ms <= ifnull(me, p.de)
order by id, fromd

,db&lt的在小提琴