Okay ... a better example.

I am a police supervisor wondering how many crimes are committed during my officers shifts:

Officer

---------

OfficerID

ShiftID

ShiftStart

ShiftEnd

Crime

------

CrimeID

CrimeDate

SELECT

OfficerID,

ShiftStart,

ShiftEnd,

COUNT(CrimeID)

FROM

Officer o

INNER JOIN Crime c ON o.ShiftStart <= i.CrimeDate AND o.ShiftEnd >=...

Don't listen to RBAR-phobes ... there are plenty of valid uses for a comparison operator in a SQL Join.

Bad = using it to make 1,000,000's of Cartesian products to filter...

I get a different random number every time I reference the CTE ?!?!

SELECT l1.ticketID, l1.iRandom, l2.iRandom FROM lottery l1 INNER JOIN lottery l2 ON l1.TicketID = l2.TicketID

Well, I guess...

This ugly bit of code gets me frighteningly close ...

SELECT

t1.AcctID, t1.iOrder, t1.ilevel,

/* The second level is always the parent folder of the detail */

t2.AcctID Level2ID, t2.iOrder Level2Order,

CASE WHEN t1.iLevel...

Well, one of those sources (and the...

Eliza (9/16/2013)

All the information for parameters comes from three tables. All joined with inner joins.

At the moment I feel I have functions embedded with functions and its not clean...

Start with my code for finding "CTEMissingDates" ... but add a group ID for your groups (1,2, etc)

At that point you can group them into ranges using the technique from...

I'm not sure where your expected data for "2" is coming from ... but you should be able to expand this if necessary to groups

This is quick and dirty but...

I definitely agree that without knowing the scope of PCC's business requirement for aligning the "Cham" field across days, we can not present an optimal solution.

I guess the way I...

Your solution would run much faster (my code must test every previous day in order to determine the proper sort order, and you rely on a case statement) but I...

Piece of cake ... you can make it a lot more concise but this explains it easier:

WITH agg AS

(SELECT

DueDate,

ROW_NUMBER() OVER (ORDER BY DueDate) AS iRow,

SUM(CASE WHEN Cham...