;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

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 1 year, 2 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...