Group by date and time with configurable blocking

  • Thread starter Thread starter Kevin Laughridge
  • Start date Start date
K

Kevin Laughridge

Hello,
I have three fields in a table (resdate, restime, receipt) where the
resdate is a date field (e.g. data = 3/10/2004), the restime is s time
field (e.g. data = 11:45 AM), and the receipt contrains a string which
I would like to count. I would like to have the query return the count
of the receipts field grouped by resdate and restime but I would like
the grouping on time to be user definable so that it could return two
hours grouped, six hours grouped, ets. Does anyone know how to
accomplish this? I have used the respart function to return groupings
on days, weeks, and years but having it by days and a userdefinable
amount of hours escapes me at this time. Thanks for all the help!

E.g. of data which would be ideal to be returned if the user definded
blocks where set at 2 hours:

ResDate ResTime CountOFReceipt
3/10/2004 7:00:00 5
3/10/2004 9:00:00 7
3/10/2004 11:00:00 3


Kevin
 
Kevin said:
Hello,
I have three fields in a table (resdate, restime, receipt) where the
resdate is a date field (e.g. data = 3/10/2004), the restime is s time
field (e.g. data = 11:45 AM), and the receipt contrains a string which
I would like to count. I would like to have the query return the count
of the receipts field grouped by resdate and restime but I would like
the grouping on time to be user definable so that it could return two
hours grouped, six hours grouped, ets. Does anyone know how to
accomplish this? I have used the respart function to return groupings
on days, weeks, and years but having it by days and a userdefinable
amount of hours escapes me at this time. Thanks for all the help!

E.g. of data which would be ideal to be returned if the user definded
blocks where set at 2 hours:

ResDate ResTime CountOFReceipt
3/10/2004 7:00:00 5
3/10/2004 9:00:00 7
3/10/2004 11:00:00 3
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe you'd have to use the MOD operator. Try (untested):

SELECT ResDate,
Format(Hour(ResTime),"00") & ":00:00" As EndTime,
Count(Receipt) As Receipts
FROM TableName
WHERE Hour(ResTime) Mod 2 = 1
GROUP BY ResDate, Format(Hour(ResTime),"00") & ":00:00"

Use

Hour(ResTime) Mod 2 = 1 to for ODD hours

and

Hour(ResTime) Mod 2 = 0 for EVEN hours.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFe5bIechKqOuFEgEQIxVACdGfmdUJAacZ/PG/NjVZjzwqOG6+0AnRre
U1ylLIfnNruX3P1JlJzujz8E
=buu9
-----END PGP SIGNATURE-----
--
 
Back
Top