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?
