gpt4 book ai didi

sql - 查找包含开始日期和结束日期的记录未涵盖的时间间隙

转载 作者:行者123 更新时间:2023-12-03 00:04:56 25 4
gpt4 key购买 nike

我有一个费用记录表(f_fee_item)如下:

Fee_Item_ID    int
Fee_Basis_ID int
Start_Date date
End_Date date

(删除不相关的列)

假设同一 Fee_Basis_ID 的记录不会重叠。

我需要找到所提供的 @Query_Start_Date@Query_End_Date 之间每个 Fee_Basis_ID 费用记录中每个间隙的 Start_Date 和 End_Date 。我需要这些数据来计算所有未收取费用的期间的应计费用。

如果给定的 Fee_Basis_ID 根本没有费用记录,我还需要查询返回一条记录(如果有帮助的话,Fee_Basis_ID 是 D_Fee_Basis.Fee_Basis_ID 的外键)。

例如:

@Query_Start_Date = '2011-01-01'
@Query_Start_Date = '2011-09-30'

D_Fee_Basis:

F_Fee_Item
1
2
3

F_Fee_Item:

Fee_Item_ID  Fee_Basis_ID  Start_Date  End_Date
1 1 2011-01-01 2011-03-31
2 1 2011-04-01 2011-06-30
3 2 2011-01-01 2011-03-31
4 2 2011-05-01 2011-06-30

所需结果:

Fee_Basis_ID   Start_Date  End_Date
1 2011-07-01 2011-09-30
2 2011-04-01 2011-04-30
2 2011-07-01 2011-09-30
3 2011-01-01 2011-09-30

几天来我一直在尝试不同的自连接,试图让它工作,但没有成功。

请帮忙!!

最佳答案

这是一个解决方案:

declare @Query_Start_Date date= '2011-01-01' 
declare @Query_End_Date date = '2011-09-30'

declare @D_Fee_Basis table(F_Fee_Item int)
insert @D_Fee_Basis values(1)
insert @D_Fee_Basis values(2)
insert @D_Fee_Basis values(3)

declare @F_Fee_Item table(Fee_Item_ID int, Fee_Basis_ID int,Start_Date date,End_Date date)
insert @F_Fee_Item values(1,1,'2011-01-01','2011-03-31')
insert @F_Fee_Item values(2,1,'2011-04-01','2011-06-30')
insert @F_Fee_Item values(3,2,'2011-01-01','2011-03-31')
insert @F_Fee_Item values(4,2,'2011-05-01','2011-06-30')

;with a as
(-- find all days between Start_Date and End_Date
select @Query_Start_Date d
union all
select dateadd(day, 1, d)
from a
where d < @Query_end_Date
), b as
(--find all unused days
select a.d, F_Fee_Item Fee
from a, @D_Fee_Basis Fee
where not exists(select 1 from @F_Fee_Item where a.d between Start_Date and End_Date and Fee.F_Fee_Item = Fee_Basis_ID)
),
c as
(--find all start dates
select d, Fee, rn = row_number() over (order by fee, d) from b
where not exists (select 1 from b b2 where dateadd(day,1, b2.d) = b.d and b2.Fee= b.Fee)
),
e as
(--find all end dates
select d, Fee, rn = row_number() over (order by fee, d) from b
where not exists (select 1 from b b2 where dateadd(day,-1, b2.d) = b.d and b2.Fee= b.Fee)
)
--join start dates with end dates
select c.Fee Fee_Basis_ID, c.d Start_Date, e.d End_Date from c join e on c.Fee = e.Fee and c.rn = e.rn
option (maxrecursion 0)

结果链接: https://data.stackexchange.com/stackoverflow/q/114193/

关于sql - 查找包含开始日期和结束日期的记录未涵盖的时间间隙,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/7633541/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com