0
  • 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;

closed as unclear what you're asking by Erik Darling, Tom V, hot2use, LowlyDBA, McNets Feb 17 '18 at 16:12

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • You may want to include a break condition like IF @@ROWCOUNT = 0 RETURN;, but it's hard to say with your theoretical script. Why don't you give writing it a shot, and post a question if you run into any actual problems? – Erik Darling Feb 15 '18 at 21:50
  • I have edited the post with the actual code. Start over. – Thomas Covenant Feb 16 '18 at 16:59
  • Have a look at this post. I think the code will do what you want it to. – Erik Darling Feb 16 '18 at 17:02
  • Wow! That's really way over my head at this point, but I think I see what he's saying. Though that post seems to be in regards to speeding up operations rather than detecting when I've run out of rows. – Thomas Covenant Feb 16 '18 at 18:14
  • You can use the same method to figure out when you've run out of rows though. When a batch runs and affects 0 rows, you bail. – Erik Darling Feb 16 '18 at 18:15
4

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 > '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 > '44'
                        AND NOX_LB_Validity = 'Valid'
                        AND type = @Type
                      ORDER BY targetDate DESC
                    ) dates ON (    auto.timestamp >= dates.startDay
                                AND auto.timestamp < 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.

  • I see. But this only returns a range of dates. I would then need to use those dates, somehow, to pull the hourly data for those dates. I'm still learning this stuff, largely self-taught. I'm not sure how I'd do that. – Thomas Covenant Feb 16 '18 at 16:51
  • Sorry - finding the dates seemed to be your initial focus. Modified based ont he code you provided. – RDFozz Feb 16 '18 at 17:30
  • I like this. This one I can use! Thanks so much! – Thomas Covenant Feb 16 '18 at 18:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.