drew.allen (9/23/2015)

---

Perhaps I'm missing something, but if you want to look specifically at the second ticket, then COUNT is the wrong aggregate. Try the following.

WITH Ticket_Number AS (

SELECT ti.person_ID, ticket_date, ROW_NUMBER() OVER(PARTITION BY ti.person_ID ORDER BY ti.ticket_date) AS ticket_number

FROM @TicketsIssued ti

)

SELECT COUNT(tn.person_ID), tr.DateFrom, tr.DateTo

FROM Ticket_Number AS tn

INNER JOIN @TicketRange AS tr

ON tn.ticket_date BETWEEN tr.DateFrom AND tr.DateTo

WHERE tn.ticket_number = 2

GROUP BY tr.DateFrom, tr.DateTo

Drew