As of now this is a concept. I’ve not tried it, because I’m stuck with a question I can’t answer:

• I have a DB which includes a datetime column (timestamp).
• A new record is written every hour, so 24 records a day.

I want to write a procedure that will look for a column to be a certain value. Say column A has a value of 1.

I want to find the most recent 30 days in which any hourly value of column A = 1, and push all the hourly rows into a temp table, skipping days where no hourly value of column A = 1.

I thought of declaring a variable @daycount and setting it to 30. Setting a @enddate variable to midnight today and a @startdate variable to midnight yesterday. Another variable to check the count, @count.

The following is not strictly SQL code, it’s more my thoughts how it should work:

WHILE @daycount > 0 BEGIN      SELECT @count = count(datetime)      FROM   table      WHERE  A = '1'      AND    timestamp BETWEEN @startdate AND @enddate      WHILE @count > 0     BEGIN          -- Then pull the data for that day into the table,          -- since I want all the hours.          SET @count = 0         SET @daycount = @daycount -1      END      SET @startdate to @startdate -1 day AND @enddate to @enddate -1 day  END 

I want to pull only days where any hour of A = 1, and ignore days where no hour of A = 1, as I’ve said.

All this leads up to my question:

• What is going to happen if there are fewer than 30 days where A = 1?
• The @daycount will never get to 0?
• Will it run forever?
• If so, how do I avoid that?