Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: Can this loop be replaced with a set based SQL command?

    I have been racking my brain trying to come up with a more elegant solution, here's the current brute force method:

    Code:
    CREATE TABLE myTable
    (
     Id int primary key
    ,iLow money
    ,iHigh money
    ,iClose money
    )
    -- insert data...
    INSERT myTable
    SELECT 1, 130.67, 130.75, 130.75
    UNION
    SELECT 2, 130.68, 130.76, 130.68
    UNION
    SELECT 3, 130.68, 130.76, 130.76
    UNION
    SELECT 4, 130.71, 130.79, 130.71
    UNION
    SELECT 5, 130.65, 130.73, 130.73
    UNION
    SELECT 6, 130.69, 130.77, 130.77
    UNION
    SELECT 7, 130.74, 130.84, 130.74
    UNION
    SELECT 8, 130.72, 130.82, 130.82
    UNION
    SELECT 9, 130.75, 130.85, 130.85
    
    -- find exitId which is defined as the first bar where the close is > all iHigh values prior or the close < all iLow values prior
    -- to initial range of id's 1 through 4 with minimum first bar starting at id 5
    DECLARE @startId int
    DECLARE @endId int
    DECLARE @exitId int
    DECLARE @maxiHigh money
    DECLARE @miniLow money
    DECLARE @loopiHigh money
    DECLARE @loopiLow money
    
    SET @startId = 1
    SET @endId = 4
    SET @exitId = 5
    
    SELECT @maxiHigh = MAX(iHigh) 
    ,@miniLow = MIN(iLow)
    FROM myTable
    WHERE Id BETWEEN @startId AND @endId
    
    WHILE  (1 = 1)
    BEGIN
    	IF EXISTS (SELECT 1 FROM myTable T WHERE T.Id = @exitId 
    	AND (T.iClose > @maxiHigh OR T.iClose < @miniLow))
    		BREAK;
    	ELSE
    		SELECT @maxiHigh = MAX(iHigh) 
    		,@miniLow = MIN(iLow)
    		FROM myTable
    		WHERE Id BETWEEN @startId AND @exitId
    
    	SELECT @exitId = @exitId + 1
    	IF NOT EXISTS(SELECT 1 FROM myTable T WHERE T.Id = @exitId)
    	BEGIN
    		SET @exitId = 0
    		BREAK
    	END
    END
    
    IF @exitId = 0
    	SELECT 'No match found'
    ELSE
    	SELECT * FROM myTable WHERE Id = @exitId
    Last edited by Gagnon; 04-28-12 at 13:10.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    DECLARE @startId int
    DECLARE @iniId int
    DECLARE @exitId int
    
    SET @startId = 1
    SET @iniId = 5
    SET @exitId = 0
    
    select top 1 @exitId = t.Id 
    from @myTable as t
    where (t.Id >= @iniId) and
          (exists (select 1 from @myTable as s
                   where (s.Id >= @startId) and
                         (s.Id < t.Id)
                   having (t.iClose < MIN(s.iLow)) or
                          (t.iClose > MAX(s.iHigh))))
    
    IF @exitId = 0
    	SELECT 'No match found'
    ELSE
    	SELECT * FROM @myTable WHERE Id = @exitId
    Hope this helps.

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    you can have a having clause without a group by?

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    In my testing it worked this way.
    I think that the group by clause is not necessary because there is no selected column from the table.

Posting Permissions

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