sql group problem

[steve]

Hi All

Using VB.net 2003 and ADO.Net with MS Access 2003 database

I want to retrieve records grouped by time, but grouped within 1/2 hour
increments

The program is for a gym membership and I display graphs of the number of
sign-ins by members over the day
The gym uses this info to roster their staff according to the busiest times
of the day

The sql statement below returns the data but based on actual entry times.

sql = "Select mytime, count(mynumber) as amount from [attendance] where
[mydate] = #" & Format(CDate(Freports.dg2(Freports.dg2.CurrentRowIndex, 0)),
"MMM d, yyyy") & "# group by [mytime]"


I would like to reduce this by grouping attendance into 1/2hr groups, so the
graph is not so cluttered

e.g entries of 5pm, 5:03pm, 5:10pm, 5:15pm would be grouped as say 5pm


Any ideas



Regards

Steve

 

[Rogas69]

well you should round the time before aggregation. I do not know the exact
syntax of access sql but you should be able to use subquery instead of
attendance table, something like

select roundedtime, count(mynumber) as amount from
(
select round(mytime) as roundedtime, mynumber from [attendance] where
[mydate] = #...#
) as q
group by(roundedtime)

of course you have to find out how to round time.

it may also be possible to use round function in column list an in group by
expression

Peter