- 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 > 0 -- if we haven't found 30 useable days then: Begin set @isonline = (select count(So2_7PCT) from Auto_SixtyMinuteEmissionRecords where SO2_7PCT > '44' and NOX_LB_Validity = 'Valid' and type = @Type and timestamp >= @daystart and TimeStamp < @dayend) While @isonline > 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 >= @daystart and TimeStamp < @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;