SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

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

Time|Button
9:00|Off
10:00|On
11:00|On
12:00|On
13:00|Off
14:00|On
15:00|On

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:

On|Off
10:00|13:00
14:00|15:00

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.

ScottPletcher
Constraint Violating Yak Guru

USA
366 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.


DECLARE @table1 TABLE (
    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

;WITH 
buttons_all AS (
    SELECT
        time, button, ROW_NUMBER() OVER(ORDER BY time) AS row_num
    FROM @table1 ons
), 
buttons_first_ons AS (
    SELECT
        time, row_num
    FROM buttons_all b_ons
    WHERE        
        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 (
    SELECT
        time, row_num
    FROM buttons_all b_offs
    WHERE        
        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')
)
SELECT 
    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  
 New 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.05 seconds. Powered By: Snitz Forums 2000