Combining two datetime fields into two separate columns in T-SQL

Question

Hello,

I'm trying to create a view that displays events with start and end time. This view should be gathered from an existing table that only has an event time field and event type field.

So the current EventTable looks like this:

EventTime       | BooleanField
------------------------------
1/1/2010 6:00AM        1
1/2/2010 6:00AM        0
1/3/2010 6:00AM        1
1/4/2010 6:00AM        1
1/5/2010 6:00AM        0

And the result set should look like this

StartTime       | EndTime
-----------------------------
1/1/2010 6:00AM   1/2/2010 6:00AM
1/3/2010 6:00AM   1/5/2010 6:00AM

So the view should display the periods that the boolean field is true.

Is there a simple solution to achieve this in SQL Server 2008?

Thank you for help!

Answer 1

Try this:

SELECT MIN(iq.StartTime), iq.EndTime
FROM 
(
    SELECT e1.EventTime AS StartTime, MIN(e2.EventTime) AS EndTime
    FROM EventTable e1
    LEFT JOIN EventTable e2 ON e2.BooleanField = 0 AND e2.EventTime > e1.EventTime
    WHERE e1.BooleanField = 1
    GROUP BY e1.EventTime 
) iq
GROUP BY iq.EndTime

This will assure you get only the widest possible intervals for every EndTime you have. I'm pretty sure it's not possible to simplify this into a single-SELECT query due to the need for two GROUP BY clauses -- even considering the other examples (which use implicit grouping by using two different aggregation functions, so are equivalent to this example).

Answer 2

What about

  Select s.EventTime StartTime,
         e.EventTime EndTime
  From EventTable s
     Left Join EventTable e 
         On s.BooleanFiels = 1
            And e.BooleanField = 0
            And e.EventTime =
               (Select Min(EventTime) 
                From EventTable 
                Where e.BooleanField = 0
                   And EventTime > s.EventTime)
  Order By s.EventTime

Answer 3

you could try something like (full example)

DECLARE @EventTable TABLE(
        EventTime DATETIME,
        BooleanField INT
)

INSERT INTO @EventTable (EventTime,BooleanField) SELECT '1/1/2010 6:00AM',1 
INSERT INTO @EventTable (EventTime,BooleanField) SELECT '1/2/2010 6:00AM',0 
INSERT INTO @EventTable (EventTime,BooleanField) SELECT '1/3/2010 6:00AM',1 
INSERT INTO @EventTable (EventTime,BooleanField) SELECT '1/4/2010 6:00AM',1 
INSERT INTO @EventTable (EventTime,BooleanField) SELECT '1/5/2010 6:00AM',0

;WITH Dates AS (
        SELECT  *,
                (SELECT MIN(EventTime) FROM @EventTable WHERE EventTime > e.EventTime AND BooleanField = 0) EndDate
        FROM    @EventTable e
        WHERE   BooleanField = 1
)
SELECT  MIN(EventTime) StartDate,
        EndDate
FROM    Dates
GROUP BY EndDate