For future reference, many people are wary of opening random files from the Internet.  If you want help, please post a script to create a TEMP table and insert the...

You can't generally define aliases INSIDE expressions.

Drew

PS: You're dates don't match.

Yes, that's a bit shorter and generally more efficient. I'm wondering if there were a lot more that 12 items per customer and the right indexes on the table...

;WITH CTE AS
(
    SELECT DISTINCT CustomerCode
      FROM myTable
)
SELECT B.*
  FROM CTE A
 CROSS APPLY(SELECT TOP(12) *
 ...

Nested CASE expressions are very hard to read, because it quickly becomes difficult to tell exactly where you are in the nesting.  Here is an example that uses a single...

00000 is an integer, so you are explicitly converting your value to char, but then implicitly converting it back to integer when you add it to 00000.  You want to...

LEAD is a windowed function - what makes you think it is going to...

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