SEO

#1 (**permalink**) 02-29-2008, 02:58 AM

I am using Report Writer for Ingres II.
Is it possible to write a query in a loop?

e.g. My table is like this

time position
09:01 pos01
09:02 pos03
09:02 pos01
09:04 pos05

Can I loop a query to count the number of times each position occurs in each
30 minute period in a day?

I wish to generate a report that goes something like

09:00 09:30 10:00
----------------------------------------
pos01 3 5 3
pos02 0 6 4
pos03 4 3 1

#2 (**permalink**) 02-29-2008, 02:58 AM

This is a SQL Server group. I can't answer your question for Ingres. Here's
a solution in Standard SQL but note that the answer may depend on the data
type of your Time column. I've assumed that Time is a VARCHAR because SQL
Server doesn't have a time-only datatype but it does have DATETIME which
will implicitly cast to a VARCHAR.

SELECT position,
COUNT(CASE WHEN time >= '09:00' AND time < '09:30' THEN 1 END),
COUNT(CASE WHEN time >= '09:30' AND time < '10:00' THEN 1 END),
COUNT(CASE WHEN time >= '10:00' AND time < '10:30' THEN 1 END)
FROM SomeTable
GROUP BY position;

--
David Portas
SQL Server MVP
--

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Query in a Loop