随着时间的推移（不重叠）-技术？ [英] Accruing over time (non-overlapping) - technique?
问题描述
我正在尝试寻找一种更好的方式来制作Crystal Report（其他人）...将各个小组的非重叠时间加起来。
这显然是一个古老的问题...
是否有获得
I'm trying to find a better way of doing a Crystal Report (Someone Else's)... Add up non-overlapping time in groups. This is evidently an age-old problem... Is there a technique of getting
- 调整（开始/ end）次的记录，以消除普通/重叠时间
- 在子组中
- –使用直接SQL（尽管我发现我可以做CTE）
假设开始时间（和/或组，子组）的初始顺序由开始和结束分开
Assume initial order-by for Start Time (and/or Group, SubGroup) and Start and End are separate fields.
一种图形示例：
Group 1
SubGroup A
Tkt 1 |--start&end---| "16"
Tkt 2 |----tart&end----| "18"
Tkt 3 |--art&end---| "14"
Tkt 4 |--S & E -| "11"
SubGroup B
Tkt 5 |-S&E-| "7"
Tkt 6 |-S&E-| "7"
Tkt 7 |-S&E-| "7"
...
(equiv adjusted start/end w/in sub-group):
Group 1
SubGroup A ( w/ "elapsed time" of "33" )
Tkt 1 |--start&end---| <- Regular "16"
Tkt 2 |-----| <- Adjusted "start" "6"
Tkt 3 | <- Adjusted "start" & "end" "0"
Tkt 4 |--S & E -| <- Regular "11"
SubGroup B ( w/ "elapsed time" of "17" )
Tkt 5 |-S&E-| <- Regular "7"
Tkt 6 |-S&E-| <- Regular (no overlap) "7"
Tkt 7 |-| <- Adjusted "Start" "3"
...
我不是谈论在这一点上求和，只是说明根据上一条记录调整开始/结束。
I'm not talking about getting sums at this point, just illustrating adjusting start/end based on the previous record.
在报告中，他们为每条记录执行多个公式，而不是两个在组中的第一个记录上设置的变量，然后根据当前记录设置/更新AdjustedStart，AdjustedEnd的值，并将AdjustedStart，AdjustedEnd传递给另一个公式以计算时间差，以供以后求和。当前的技术很慢，我无法执行所需的漂亮的交叉表。
In the report, they are doing multiple formulas for each record, against two variables which are set on first record in group, then setting/updating values for AdjustedStart, AdjustedEnd based on current record, and passing the AdjustedStart, AdjustedEnd out to another formula to calculate the time difference, to be summed later. The current technique is slow, and I can't do a nifty cross-tab that is desired.
我在想/希望这已经在仅SQL中解决了，因为我们不能在数据库服务器上放置任何proc，临时表等。我正在尝试使用Group / SubGroup作为父项，并使用CASE比较当前值和最后一个父级值，并使用CTE和（重新）递归。
I am thinking/hoping that this has been solved in SQL-only, as we can't put any proc's, temp tables, etc. on the DB server. I'm trying to figure out a way w/ CTE and (re-)cursing, using the Group/SubGroup as parentage, and CASE to compare current values w/ last-parent values. Does this ring a bell or sound plausible?
Crystal的功能很多，但这似乎并不适合。
The abilities of Crystal are many, but this seems like one that it's not cut out for.
马克（Marc）
推荐答案
很感谢您很久以前问过这个问题，但是它使我感兴趣，所以我做了一些研究，找到了杰夫·摩登（Jeff Moden）的解决方案。他写了一篇有关对日期孤岛进行分组的文章，您可以在这里找到：对连续日期进行分组孤岛（需要登录但免费注册）。
I appreciate you asked this question a long time ago but it interested me, so I did some research and found a solution by Jeff Moden; he wrote an article about grouping islands of dates which you can find here: Group Islands of Contiguous Dates (login required but free to register).
我假设您有一个表，该表中的子组每天都有行，因此有16行对于 Tkt1，则为18；对于 Tkt2，则为18，等等。如果不是这种情况，并且只有每个 Tkt的开始日期和结束日期，则必须使用日历表以推断每个范围的行。
I have assumed you have a table with rows for each day in a sub group, so there are 16 rows for "Tkt1", 18 for "Tkt2" etc. If that is not the case, and you only have start and end dates for each "Tkt", you will have to use a Calendar table to extrapolate the rows for each range.
Jeff的解决方案使用 ROW_NUMBER 和 DATEDIFF 技巧将日期岛分组。
Jeff's solution uses a ROW_NUMBER and DATEDIFF trick to group islands of dates.
WITH Grouped_Dates AS
( -- Find the unique dates and assign them to a group.
-- The group looks like a date but the date means nothing except that adjacent
-- dates will be a part of the same group.
SELECT group_name,
unique_date = tkt_date,
date_group = DATEADD(dd, -ROW_NUMBER() OVER (PARTITION BY group_name ORDER BY group_name, tkt_date), tkt_date)
FROM t
GROUP BY group_name, tkt_date
)
-- Now, if we find the MIN and MAX date for each date_group, we'll have the
-- Start and End dates of each group of contiguous daes. While we're at it,
-- we can also figure out how many days are in each range of days.
SELECT group_name,
start_date = MIN(unique_date),
end_date = MAX(unique_date),
days = DATEDIFF(dd,MIN(unique_date),MAX(unique_date))+1
FROM Grouped_Dates
GROUP BY group_name, date_group
ORDER BY group_name, start_date
该查询的结果是
group_name start_date end_date days
---------- ---------- ---------- ----
Group1 2012-01-01 2012-01-22 22
Group1 2012-01-24 2012-02-03 11
Group2 2012-01-09 2012-01-15 7
Group2 2012-01-18 2012-01-27 10
我用示例创建了 SQL提琴根据您的问题的数据。
I've created a SQL Fiddle with sample data based on your question.
然后您可以对每个组求和以得出总计花费的时间。
You can then sum each Group to give the grand total time spent.
这篇关于随着时间的推移（不重叠）-技术？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
