devxlogo

Formatting Data in Tabular Form

Formatting Data in Tabular Form

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'AND EventStamp 

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.

devx-admin

Share the Post: