I want to find the number of active sessions for a given date range grouped by 2 fields.
The keys being:
We show a zero at the start time for any GROUP1, GROUP2 that had at least one session in the given range if there are no active sessions at the start time;
And, we show a zero when we change to no sessions from an active session.

My problem is that I cannot seem to use my query in Hibernate. I even tried a named query but it complains (net.sf.hibernate.QueryException: in expected: select [<query string>];).
If I copy the query and run it in iSql it runs fine. What else? I am using Oracle 10.i and Hibernate 2.0 currently. The resultset is derived/aggregated and not stored in a permanent table.

Can someone show me how I could run this query, ideally as an HQL query?

Thanks,
James

Here are the expected output and the query.

GROUP1 , GROUP2 , CHANGE_TIME , ACTIVE_SESSIONS
1 , 1 , 1/1/2008 12:00:00 AM 0
1 , 1 , 1/1/2008 1:13:00 AM , 1
1 , 1 , 1/1/2008 7:35:00 AM , 2
1 , 1 , 1/1/2008 9:30:00 AM , 3
1 , 1 , 1/1/2008 7:55:00 PM , 5
1 , 1 , 1/2/2008 12:23:00 AM , 4
1 , 1 , 1/2/2008 1:00:00 AM , 0
1 , 1 , 1/2/2008 1:13:00 PM , 1
1 , 1 , 1/2/2008 2:54:00 PM , 2
1 , 1 , 1/2/2008 4:35:00 PM , 0
1 , 2 , 1/1/2008 12:00:00 AM 3
1 , 2 , 1/1/2008 12:21:00 PM , 4
1 , 2 , 1/1/2008 1:35:00 PM , 3
1 , 2 , 1/1/2008 2:45:00 PM , 2
1 , 2 , 1/3/2008 4:21:00 PM , 1
1 , 2 , 1/3/2008 5:30:00 PM , 0
2 , 1 , 1/1/2008 12:00:00 AM , 2
2 , 1 , 1/1/2008 4:21:00 PM , 1
2 , 1 , 1/1/2008 5:30:00 PM , 0


select
sess_time.GROUP1,
sess_time.GROUP2,
sess_time.change_time,
( select count(1)
from SESSION ts INNER JOIN USER pt
on ts.USER_ID = pt.USER_ID
where ts.GROUP2 = sess_time.GROUP2
and pt.GROUP1 = sess_time.GROUP1
and ts.SESSION_START <= sess_time.change_time
and ts.SESSION_END > sess_time.change_time ) active_sessions
from
(
-- sessions opened during range
select distinct
pt.GROUP1,
ts.GROUP2,
ts.SESSION_START change_time
from
SESSION ts,
USER pt
where
ts.USER_ID = pt.USER_ID
and ts.SESSION_START > :startdate
and ts.SESSION_START < :enddate

union
-- session completed during range
select distinct
pt.GROUP1,
ts.GROUP2,
ts.SESSION_END change_time
from
SESSION ts,
USER pt
where
ts.USER_ID = pt.USER_ID
and ts.SESSION_END > :startdate
and ts.SESSION_END < :enddate

union
-- begin date for each available GROUP1 / GROUP2. Here b/c many may be zero.
-- end date not necessary as it is the same as the final entry.
select distinct
GROUP1,
GROUP2,
to_date(:startdate)
from
SESSION ts,
USER pt
where
ts.USER_ID = pt.USER_ID
and (

( ts.SESSION_START > :startdate and ts.SESSION_START < :enddate)
or
( ts.SESSION_END > :startdate and ts.SESSION_END < :enddate )
or
( ts.SESSION_START < :startdate and (ts.SESSION_END > :enddate or ts.SESSION_END is null) )

)
) sess_time

group by sess_time.GROUP1, sess_time.GROUP2, sess_time.change_time;