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

Question

I am trying to create a view that displays events with start and end times. This view should be compiled from an existing table in which there is only an event time field and an event type.

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 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

Thus, the view should display the periods in which the logical field is true.

Is there a simple solution for this in SQL Server 2008?

Thank you for your help!

Answer 1

you can 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

Answer 2

Try the following:

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

, , . , SELECT - GROUP BY - ( , ).

Answer 3

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