Accruing over time (non-overlapping) - technique?

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

• Adjusted (start/end) times, per record, to remove common/over-lap time,
• within subgroups
• --using straight SQL (although I find I can do CTEs)

Assume initial order-by for Start Time (and/or Group, SubGroup) and Start and End are separate fields.

A kind-a graphic example:

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.

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.

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?

The abilities of Crystal are many, but this seems like one that it's not cut out for.

Marc

I'm doing this off the top of my head...

SELECT
    CUR.subgroup,
    CUR.ticket,
    COALESCE(MAX(PARENT.end_time), CUR.start_time) AS start_time,
    CASE
        WHEN CUR.end_time < MAX(PARENT.end_time) THEN MAX(PARENT.end_time)
        ELSE CUR.end_time
    END
FROM
    My_Table CUR
LEFT OUTER JOIN My_Table PARENT ON
    PARENT.start_time <= CUR.start_time AND
    PARENT.end_time > CUR.start_time AND
    PARENT.ticket <> CUR.ticket AND
    PARENT.subgroup = CUR.subgroup
GROUP BY
    CUR.subgroup,
    CUR.ticket,
    CUR.start_time,
    CUR.end_time

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).

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'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

The result of that query is

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

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.