Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Logical SELECT problem - do I need cursors?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 03/15/2013 :  17:00:19  Show Profile  Reply with Quote
What follows is a reduced version of a more complex real-world problem I am trying to solve.

There is a light bulb with an on button, and an off button. Over a period of time, we have kept track of each time either button was pressed. The table looks like this:


Special note here - as 15:00 is the final entry, we will assume that we stopped tracking here, and also assume that the lightbulb turned off immediately after 15:00.

The goal is to make a list of times that the light bulb was on, and report the time it turned on, and the time it turned off. The logic should be straightforward, other than the rule that the bulb turns off immediately after the final entry, regardless of when or what button the final entry was.

So for this sample table, the list contains two entries like this:


What's the best way to go about doing this? I am dimly aware of what cursors are, but have never used them before. Performance isn't as big an issue as maintainable code is.

Aged Yak Warrior

550 Posts

Posted - 03/15/2013 :  18:31:15  Show Profile  Reply with Quote
There is a sophisticated "Gaps and Islands" technique from Itzik Ben_Gan to do this.

But here is a more step-by-step way.

Most people like/understand CTEs better so I used that method.

Hopefully at the very least it will help you get going.

    time time,
    button char(3)

insert into @table1
select '9:00','Off' union all
select '10:00','On' union all
select '11:00','On' union all
select '12:00','On' union all
select '13:00','Off' union all
select '14:00','On' union all
select '15:00','On'
SELECT * FROM @table1

buttons_all AS (
        time, button, ROW_NUMBER() OVER(ORDER BY time) AS row_num
    FROM @table1 ons
buttons_first_ons AS (
        time, row_num
    FROM buttons_all b_ons
        button = 'on' AND
        EXISTS(SELECT 1 FROM buttons_all b_all WHERE b_all.row_num = b_ons.row_num - 1 AND b_all.button = 'off')
buttons_first_offs AS (
        time, row_num
    FROM buttons_all b_offs
        button = 'off' AND
        EXISTS(SELECT 1 FROM buttons_all b_all WHERE b_all.row_num = b_offs.row_num - 1 AND b_all.button = 'on')
    b_ons.time AS [On],
    ISNULL(b_offs.time, (SELECT MAX(time) FROM buttons_all)) AS [Off]
FROM buttons_first_ons b_ons
LEFT OUTER JOIN buttons_first_offs b_offs ON
    b_offs.row_num = (SELECT MIN(row_num) FROM buttons_first_offs b_offs2 WHERE b_offs2.row_num > b_ons.row_num)
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000