Using a While function but running out of days I have a DB which includes a datetime column (timestamp). 
A new record is written every hour, so 24 records a day.


I want my procedure to look for conditions where SO2_7PCT > 44 and NOX_LB_Validity = 'Valid'.

I want to find the most recent 30 days in which any hour meets these conditions. On days where the conditions are met by at least one hour, I want to pull all 24 hours of that day into a temp table. If 0 hours meet the conditions, skip the day.

Here is the code I wrote. The problem I have with this code is if there aren't 30 days which meet the conditions, it doesn't stop, it keeps going day after day, earlier and earlier, forever. The earliest day I have in my DB is 10/1/2017 but it blows right past that and keeps looking, even though there are no more rows to look at!

What should I do to make it stop when it runs out of rows to look at?

Here's the code:

-- daycount is how many valid days it found.
-- daystart and dayend are the 24 hour period to look at the hours.
-- isonline counts how many valid and online hours that day.
-- daycounter counts how many valid days are in the roll.

declare @daycount int, @daystart datetime, @dayend datetime, @isonline int, @daycounter int


set @daycounter = 0
set @daycount = 30
set @dayend = @EndDate
--set @dayend = dateadd(DD,1,@dayend)
set @daystart = dateadd(DD,-1,@dayend)
print '@daystart'
print @daystart
print '@dayend'
print @dayend

While @daycount &gt; 0 -- if we haven't found 30 useable days then:
Begin
  set @isonline = (select count(So2_7PCT)
                     from Auto_SixtyMinuteEmissionRecords
                    where SO2_7PCT &gt; '44'
                      and NOX_LB_Validity = 'Valid'
                      and type = @Type
                      and timestamp &gt;= @daystart and TimeStamp &lt; @dayend)

  While @isonline &gt; 0 -- Found a good day, need to pull the 24 hours into temp table, increment daycounter, decrement daycount.
  Begin
    insert into @temptable
    select @ from Auto_SixtyMinuteEmissionRecords
     where timestamp &gt;= @daystart
       and TimeStamp &lt; @dayend)

    set @isonline = 0
    set @daycount = @daycount - 1
    set @daycounter = @daycounter + 1
  END

  --Set start and end to -1 day to look at previous day.
  Set @daystart = dateadd(dd,-1,@daystart)
  set @dayend = dateadd(dd,-1,@dayend)
END;
As a general rule, in SQL, you want to accomplish things with set-based operations rather than iterative processes.

So, you want the 30 most recent days where your search criteria were true for at least one entry.

I'd try this:

SELECT TOP (30) DISTINCT CAST(timestamp as DATE) targetDate
  FROM Auto_SixtyMinuteEmissionRecords
 WHERE SO2_7PCT &gt; '44'
   AND NOX_LB_Validity = 'Valid'
   AND type = @Type
 ORDER BY targetDate DESC
;


This will locate all the records that have columnA = 1, take the unique dates from those rows' timestamps, and give you the 30 most recent.

If there aren't 30 days where this is true, you just won't get back thirty rows in the result set.

Of course, this assumes that using WHILE is not a requirement.



You could then use the above query as a derived table, and join it to your INSERT statement, as follows:

INSERT INTO #temptable
SELECT auto.*
  FROM Auto_SixtyMinuteEmissionRecords auto
         INNER JOIN (SELECT TOP (30) DISTINCT CAST(timestamp as DATE) startDay, DATEADD(day, 1, CAST(timestamp as DATE)) as endDay
                       FROM Auto_SixtyMinuteEmissionRecords
                      WHERE SO2_7PCT &gt; '44'
                        AND NOX_LB_Validity = 'Valid'
                        AND type = @Type
                      ORDER BY targetDate DESC
                    ) dates ON (    auto.timestamp &gt;= dates.startDay
                                AND auto.timestamp &lt; dates.endDay)
;


This should put (up to) 30 days' worth of hourly rows into your temp table, in one statement.

NOTE: Code above is untested.