You're making the two most common mistakes when working with intervals.

The tendency when comparing two intervals is to compare the starting values with starting values and ending values with ending...

@Drew.Allen - is there a reason you need an artificial column to aggregate on? Couldn't you  use a COUNT on the ID, assuming there are no duplicate rows and...

I think it's more likely that he's misstating what he wants, because he doesn't have enough knowledge, rather accurately stating that the solution should not use an aggregate.  Assuming that...

Yes, it can be done with PIVOT.  You'll need to create an artificial column to aggregate on.  AND you should investigate cross tabs.

Drew

Here is a solution using your sample data.  Since you didn't supply the expected results as consumable data, I didn't compare the results with the expected results.

/*...

You're doing your calculation backward.  You're calculating the duration and then dividing it up into blocks instead of dividing it up into blocks and calculating the duration in each block. ...

Now that Jonathan has given you a solution, this likely to perform better.

WITH ShiftSummary AS
(
    SELECT *, LAST_VALUE(ss.EntryDate) OVER(ORDER BY ss.EntryDate ROWS BETWEEN CURRENT...

Presumably, the 12 records weren't all entered at exactly the same time on that date, but that is what you are checking for.

Drew

Generally, an approach that uses windowed functions is going to perform better than one using CROSS/OUTER APPLY, because it's going to have to read the table fewer times.  The following...

• This reply was modified 2 years, 7 months ago by drew.allen.

This gives the same results as Ken's query.  (NOTE: I changed your permanent table to a temp table.)

WITH InOutStatuses AS
(
    SELECT ts.GroupingId, i.InOutDate, SUM(i.InOutStatus)...

This produces the same results as Phil's.  I made some assumptions that may not be warranted.

• Assumed that all fields were VARCHAR (or NVARCHAR)

  • It will work (with modifications) as...

This gives your expected results.  I partitioned and joined on the Sponsor, Pharmacy, and Drug.  You may need to change this.

WITH RunningTotals AS
(
    SELECT...

• This reply was modified 2 years, 7 months ago by drew.allen.

It doesn't appear that you've tried to incorporate the techniques that you've already been given.  Your latest post only has minor differences from your first post in this thread.  You...

I think you just need to add a partition to your ROW_NUMBER().

SELECT * FROM
(
    SELECT YEAR(OrderDate) AS OrderYear,
      ...

• This reply was modified 2 years, 7 months ago by drew.allen.
• This reply was modified 2 years, 7 months ago by drew.allen.

This seems to give your expected results despite not referring to the "dis" column at all.

WITH test_resets AS
(
    SELECT *,  SUM(CASE WHEN ompt...