SQL Server Query to return Trustee Name, and the first occurence of an Event each day within a Date Range

Question

Current query gives me all event_type = 1, for every day after 8/1/2010 I only want the first occurrence per trustee of event_type 1 for each day after 8/1/2010.

SELECT trustee, event_time, event_type
FROM mytable
WHERE event_type = 1
AND event_time > '8/1/2010'
Order BY event_time;

Answer 1

Something like this?

SELECT
    trustee,
    CAST( event_time AS DATE ) AS event_date,
    MIN( event_time ) AS first_event,
    event_type
FROM mytable
WHERE
    event_type = 1
AND event_time > '2010-01-08'
GROUP BY
    trustee, CAST( event_time AS DATE )
ORDER BY
    event_time

The trick is to group by just the date part of the event_time, ignoring the time-of-day, as well as the trustee. Within each group, find the first time.

This won't return a record for a date if there is no data for that trustee on that date.

Update

If you're using an earlier (pre-2008?) version of SQL Server that doesn't have a built-in DATE type then you can achieve similar using CONVERT and a suitable style argument that uses only the date part of the datetime (I can't test this at present, apologies):

SELECT
    trustee,
    CONVERT( VARCHAR, event_time, 110 ) AS event_date,
    MIN( event_time ) AS first_event,
    event_type
FROM mytable
WHERE
    event_type = 1
AND event_time > '2010-01-08'
GROUP BY
    trustee, CONVERT( VARCHAR, event_time, 110 ) 
ORDER BY
    event_time

Answer 2

If you are using SQL Server 2005 and later you can use a ranking function like so:

With RankedDates As
    (
    Select T1.trustee, T1.event_time, T1.event_type
        , Row_Number() Over ( Partition By T1.Trustee 
                              Order By T1.event_time ) As Num
    From MyTable As T1
    Where T1.event_type = 1
        And T1.event_time > '20100801'
    )
Select trustee, event_time, event_type
From RankedDates
Where Num = 1   
Order By event_time;

If you are using a version of SQL Server prior to 2005, then you can do something like so:

Select T1.trustee, T1.event_time, T1.event_type
From MyTable As T1
Where T1.event_type = 1
    And T1.event_time = (
                        Select Min(T2.event_time)
                        From MyTable As T2
                        Where T2.trustee = T1.trustee
                            And T2.event_type = 1
                            And T2.event_time > '20100801'
                        )
Order By T1.event_time;