The following is an example of a CTE we have used
WITH ProcessedTasks (PatientID, LowestTaskId)
AS
(
SELECT DISTINCT
RebatePatientSummary.PatientId,
MIN(RebatePayment.TaskEventId) AS Expr1
FROM
StatementLineAssociatedPayment
INNER JOIN
RebatePayment
ON StatementLineAssociatedPayment.RebatePaymentId = RebatePayment.Id
INNER JOIN
RebatePatientStatementLine
ON StatementLineAssociatedPayment.StatementLineId = RebatePatientStatementLine.Id
INNER JOIN
RebatePatientSummary
ON RebatePatientStatementLine.SummaryId = RebatePatientSummary.Id
INNER JOIN
TaskEvent
ON RebatePayment.TaskEventId = TaskEvent.Id
WHERE
(RebatePatientSummary.IssueStatus = 2)
AND
(RebatePayment.TaskEventId <> 0)
AND
(TaskEvent.EventStatus = 5)
AND
taskevent.rebateprocessed = 0
GROUP BY
RebatePatientSummary.PatientId
)
SELECT
t.Id,
t.PatientId,
t.RebateProcessed,
p.lowestTaskId,
t.SystemTaskId
FROM
TaskEvent as t
INNER JOIN
ProcessedTasks AS p
ON t.PatientId = p.PatientId
AND
t.Id <= p.LowestTaskId
WHERE
EventStatus = 5
AND
rebateprocessed = 0
