Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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 AlarmSuite
GO

--************************************************************************
--*              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.

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

 

 

 

 

 


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

 

 

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