单独记录分享日期范围利用单列日期
我从我的表中有以下记录称为支付。我喜欢做的是根据开始和结束服务日期,利用仅1列日期将每条记录分成单独的记录集。单独记录分享日期范围利用单列日期
DisbursementID ServiceProviderID Original CircuitID Beginning_Service_Date Ending_Service_Date Amount
-------------- ----------------- -------- ----------- ---------------------- ------------------- -----------
53562 673 0 1814 2015-12-01 2015-12-31 531
53563 673 0 1814 2015-11-01 2015-11-30 531
我的目标结果是看起来像
DisbursementID ServiceProviderID Original CircuitID Date Range Amount
-------------- ----------------- -------- ----------- ---------- -------
53562 673 0 1814 2015-12-01 531
53562 673 0 1814 2015-12-02 531
53562 673 0 1814 2015-12-03 531
53562 673 0 1814 2015-12-04 531
53562 673 0 1814 2015-12-05 531
53563 673 0 1814 2015-11-01 531
53563 673 0 1814 2015-11-02 531
53563 673 0 1814 2015-11-03 531
53563 673 0 1814 2015-11-04 531
53563 673 0 1814 2015-11-05 531
而不是我的结果看起来像
DisbursementID ServiceProviderID Original CircuitID Date Range Amount
-------------- ----------------- -------- ----------- ---------- -------
53562 673 0 1814 2015-12-01 531
53563 673 0 1814 2015-11-01 531
53563 673 0 1814 2015-11-02 531
53563 673 0 1814 2015-11-03 531
53563 673 0 1814 2015-11-04 531
53563 673 0 1814 2015-11-05 531
下面是一段代码,我发现,但适合我的需要。它几乎解决了我的问题,但我不知道如何从我的第一个记录中包含范围日期块。我知道为什么这样做,但不知道如何正确地解决它:
;With Dates as
(
Select DisbursementID, ServiceProviderID,Original,CircuitID
,Beginning_Service_Date as BeginDate, Ending_Service_Date as EndDate
,Amount From Disbursement
Union All
Select DisbursementID, ServiceProviderID, Original,CircuitID
,DATEADD(day,1,BeginDate) as CalenderDate, EndDate
,Amount
From Dates
Where DATEADD(day,1,BeginDate) <= EndDate
)
Select DisbursementID, ServiceProviderID,Original,CircuitID
,BeginDate as [Date Range], Amount from Dates
Order By CircuitID
Option (MAXRECURSION 366);
如果你没有或者不能使用理货/日历表,另一种方法是使用一个AD-临时理货表。
Declare @YourTable table (DisbursementID int, ServiceProviderID int, Original int, CircuitID int, Beginning_Service_Date date, Ending_Service_Date date, Amount int)
Insert Into @YourTable values
(53562,673,0,1814,'2015-12-01','2015-12-31',531)
,(53563,673,0,1814,'2015-11-01','2015-11-30',531)
;with cte1 as (
Select MinDate=min(Beginning_Service_Date)
,MaxDate=max(Ending_Service_Date)
From @YourTable)
,cte2 as (
Select Top (DateDiff(DD,(select MinDate from cte1),(select MaxDate from cte1))+1)
D = DateAdd(DD,-1+Row_Number() Over (Order By (Select null)),(select MinDate from cte1))
From master..spt_values A -- ,master..spt_values B -- If you need more than 6 years
)
Select A.DisbursementID
,A.ServiceProviderID
,A.Original
,A.CircuitID
,[Date Range] = B.D
,A.Amount
From @YourTable A
Join cte2 B on B.D between A.Beginning_Service_Date and A.Ending_Service_Date
使用递归cte是生成一系列日期的最糟糕方法之一。 John Cappelletti的答案是much better,用于根据需要生成日期范围,而不是使用递归cte。
如果您要在55,000多行中使用它,并且您将不止一次运行此类操作,那么最好创建一个Dates
或Calendar
表。
对于内存只有152KB,可以有30个年份的日期的表格,你可以使用它像这样:
/* 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]);
,并使用Dates
表所示:
select
t.DisbursementID
, t.ServiceProviderID
, t.Original
, t.CircuitID
, d.[date]
, t.Amount
from t
inner join dates d
--on d.date >= t.Beginning_Service_Date
--and d.date <= t.Ending_Service_Date
/* if you want to have the date range work when
Beginning_Service_Date and Ending_Service_Date are backwards
you could use between */
on d.date between t.Beginning_Service_Date
and t.Ending_Service_Date
rextester演示:http://rextester.com/WNMJW41879
号码和日历表参考:
- Generate a set or sequence without loops - 3 - Aaron Bertrand
- The "Numbers" or "Tally" Table: What it is and how it replaces a loop - Jeff Moden
- Creating a Date Table/Dimension in SQL Server 2008 - David Stein
- Calendar Tables - Why You Need One - David Stein
- Creating a date dimension or calendar table in SQL Server - Aaron Bertrand
- TSQL Function to Determine Holidays in SQL Server - Tim Cullen
- F_TABLE_DATE - Michael Valentine Jones
你是正确的,这是一个巨大的性能问题。我注意到了。他(约翰)确实问我是否有日历类型的表格,但我没有。所以他没有提供他的初步答案。另外,我不知道如何创建一个适当的使用它来对付我提供的代码。 但你做了,并给了我另一种看待这个问题的方式。我非常欣赏这一点。 –
@CharlesBernardes John的答案是一个很好的答案(我赞成这个答案),但关于使用超过55,000行的评论对我来说是一个足够的理由来提供这种选择。 – SqlZim
我给了约翰答案的要点,因为他确实给了我一个根据我的初始职位工作的解决方案。既然你跟上了一个更好的选择,我希望我也可以给你一个答案。我希望你不要生气。我也给你一个upvote。 –
谢谢。我没有一个TALLY/CALENDAR表,我不知道CROSS APPLY是如何工作的,但现在就看看它。 –
哦,我喜欢你编辑过的另一个选项解决方案。使用SQL代码的我的ETL过程遇到了使用您的第一个解决方案的问题,但是您的EDITED解决方案使其更好。 –
@CharlesBernardes奇怪的是,你有第一个问题。两人都得到了相同的结果。将CROSS APPLY放在你的后袋里。他们很有用...把它们当作一个子程序 –