Re: query: "count" each minute between start/end

Nikolas Tautenhahn wrote:
> Hi there...
>
> I have a table with access statistics with columns representing the
> [date] when a user first connected and the duration[seconds] they stayed.
>
> For a graphical report I need to create a view which shows how many
> users were online each minute of the day, i.e. say we have this raw data:
>
> user1: entered today, 9:30:17, stayed for 200 seconds (so he needs to be
> counted for 9:31, 9:32, 9:33 as he left 9:30:37).
> user2: entered today, 9:28:10, stayed for 180 seconds (so he needs to be
> counted for 9:29, 9:30, 9:31) and what I would like to get from this raw
> data is something like:
>
> 9:29, 1
> 9:30, 1
> 9:31, 2
> 9:32, 1
> 9:33, 1
> Let's name the connect date when a user entered "c_date" and the
> duration simply "duration"
> how could I do something? I can calculate the [date] when a user left by
> c_date + (1/86400)*duration and I guess I would need something like
> between, but I can't put my finger on how I could increment the counter
> for all whole minutes between c_date and the end-date...
>
> thanks for all help,
> N.

One way would be to outer join to a pipelined table function.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org