looping record set

[tbear6602]

Hi! Running Reports 10 against Oracle9i.

I'm trying to find the most efficient way to calculate the following:

1)Pull the system ID for the systems that had outages yesterday
2) go back 30 days from yesterday and see how many of those systems had prior tickets during that time period.
3) if the system has 3 outages or more in 30 days, increment the count by 1.
4) repeat 1-3 with next system from yesterday.
5) reset the count at the beginning of the month.


Bonus:

I need to find some way of making this where I can regress historically and calculate this since the beginning of the year.


Available Data: SystemID (string), FailureEndDate(date/time), TicketNumber(string)(primary key on table)

Any suggestions? I know how to check for the 3 in 30 OVERALL, but not based on yesterday's data....

 

[Madawc]

You could use an alias, first for yesterday and then for the last 30 days. If you make it the last 30 days, including yesterday, you can be sure there will always be at least one link. (It can get tricky otherwise: left-outer fails when there is a conditional selection.)

As for resetting for months, you could group by months.

Madawc Williams (East Anglia, UK)