Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
Oct 11, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Formatting Data in Tabular Form

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 AlarmSuite

--*              12 HOUR PERIOD BEGINNING AT 12 MIDNIGHT                 *

Print '9/29/99 12:00 AM TO 12:15 AM'
SELECT Count(*) FROM AlarmLog

WHERE 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 AlarmLog

WHERE 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     35
12:30AM     45
12:45AM     38
1: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.

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 Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date