Question:
I use SQL 6.5 in an industrial setting. I have an application that writes a new record into the database every time a pallet blocks a photoswitch. After 24 hours I run a report that tells me how many pallets have blocked that switch every 15-minute period.
The query looks like this:
/* Count into ASRS 15 minute intervals from d1=12:15AM-11:15PM */USE AlarmSuiteGO--************************************************************************--* 12 HOUR PERIOD BEGINNING AT 12 MIDNIGHT *--************************************************************************Print '9/29/99 12:00 AM TO 12:15 AM'SELECT Count(*) FROM AlarmLogWHERE TagName = 'sqlb920.ack'AND EventStamp >= '9/29/99 12:01 AM'AND EventStamp <= '9/29/99 12:15 AM' Print '9/29/99 12:15 AM TO 12:30 AM'SELECT Count(*) FROM AlarmLogWHERE TagName = 'sqlb920.ack'AND EventStamp >= '9/29/99 12:15 AM'AND EventStamp <= '9/29/99 12:30 AM'
My problem is that the results are formatted sequentially:
'9/29/99 12:15 AM TO 12:30 AM'---------35 rows selected'9/30/99 12:30 AM TO 12:45 AM'---------45 rows selected
and so on. How can I write this query such that it will be formatted in tabular form, like this?
12:15AM 3512:30AM 4512:45AM 381:00AM 55...
I tried to use Microsoft Query and Access to make the query but the disk starts spinning like crazy and bogging down all the other on-going queries in the process.
This data collection effort, in addition to real alarm logging, adds about 100MB to the database every week.
Answer:
If you create a temp table and wrap your select in a cursor, you can update the temp table with the counts for the time periods of the selects.
