how to group time series by 4 hours (1 Viewer)

[pop71]

i have a very simple table only 3 fields
TickValue Numebr
TickTime time
tickDate date

this sample of the data

TickValue    TickDate	 TickTime
1.2957	   3/31/2005	1:33:34 PM
1.2956	   3/31/2005	1:34:22 PM
1.2957	   3/31/2005	1:34:23 PM
1.2958	   3/31/2005	1:34:33 PM
1.2955	   3/31/2005	1:34:38 PM

i would like to group them by time to appeaer like that

TickDate	     HourInterval	High	Low	open	close
3/31/2005	13	1.2963	1.2954	1.2957	1.2956
3/31/2005	14	1.2966	1.2953	1.2955	1.296
3/31/2005	15	1.2965	1.2953	1.2959	1.2957
3/31/2005	16	1.2999	1.2949	1.2958	1.2982
3/31/2005	17	1.3017	1.2981	1.2983	1.2985
3/31/2005	18	1.2995	1.2952	1.296	1.2963
3/31/2005	19	1.2973	1.296	1.2969	1.297
3/31/2005	20	1.2976	1.2964	1.2969	1.2966
3/31/2005	21	1.297	1.296	1.2964	1.2964
3/31/2005	22	1.2964	1.2957	1.2962	1.296
3/31/2005	23	1.2962	1.2953	1.2958	1.296

so i wrote this SQL to group them by hour to show the open,close, high and low price

SELECT EURUSD.TickDate, Hour(Format([TickTime],"Short Time")) AS HourInterval, Max(EURUSD.TickValue) AS High, Min(EURUSD.TickValue) AS Low, First(EURUSD.TickValue) AS [open], Last(EURUSD.TickValue) AS [close]
FROM EURUSD
GROUP BY EURUSD.TickDate, Hour(Format([TickTime],"Short Time"));

Now i would like to group them by 4 hour

the period from 0:00 tell 3:59 is the first group
the period from 4:00 tell 7:59 is the 2nd group

and so on

how can i do that

i hope my qustion is clear.
thanks in advance

 

[simongallop]

Create a new table with 2 columns and 24 rows. Hours in 1 column and group name in the other ie:

Hr GN
00 0-3
01 0-3
02 0-3
03 0-3
04 4-7
05 4-7
etc.

Now in query design link Hr with HourInterval from your current summary and use GN in your new query and group on that.

HTH