Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: row-by-row processing without cursor

    I have a table where each record has Date & Time columns , and status column which is either '1' or '0'. there are also other information coulmns.

    what I need is a query that return all the '0' status, but for the '1' status it should return the begining record and the ending record.

    records should be ordered by date and time.

    this picture will explain the requirement.

    http://img412.imageshack.us/img412/3...ryprobleml.jpg

  2. #2
    Join Date
    Nov 2008
    Posts
    7
    This one should work as you need it to on SQL Server 2008:
    Code:
    SELECT 
    	*
    FROM
    (
    	SELECT 
    		t1.[id],
    		t1.[date],
    		t1.[time],
    		( CASE 
    			WHEN t1.[status]=1 
    				AND (SELECT 
    						TOP 1 t2.[status] 
    					FROM 
    						dbo.nusaifs_problem t2 
    					WHERE 
    						t2.[id] < t1.[id] 
    					ORDER BY 
    						t2.[id] DESC 
    					) = 1 
    				AND (SELECT 
    						TOP 1 t3.[status] 
    					FROM 
    						dbo.nusaifs_problem t3 
    					WHERE 
    						t3.[id] > t1.[id] 
    					ORDER BY 
    						t3.[id] ASC 
    					) = 1 
    				THEN NULL
    			ELSE t1.[status]
    		END ) as [status_reduced]
    	FROM 
    		dbo.nusaifs_problem t1
    ) as t4
    WHERE 
    	t4.[status_reduced] IS NOT NULL;
    You might want to put that into a view.

    I hope this helps you.

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    thanks alot

    it works like a dream

  4. #4
    Join Date
    Nov 2008
    Posts
    7
    You're welcome

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •