Sunday, October 5, 2008

Time Intervals


This request came from the ITToolBox. Based on the following data
ID DATETIME
-- -----------
1 10/03 09:00
2 10/03 09:01
3 10/03 09:20
4 10/03 09:31
5 10/03 10:00
6 10/03 10:10
7 10/03 10:40

They wanted a total count in half-hour intervals. Tom Falconer put together the logic for this. I made a few refinements, and fixed a minor error, but had to keep this for future reference. The ID field shown in the table, is not necessary or used in this code.
Define Date_Time = "To_Char(Trunc(Datetime)+(Trunc((Datetime-Trunc(Datetime))*48)/48),'dd/Mm/Yy hh:Mi')"

Select &Date_Time As Date_Time, Count(*)
From Ext_Ittbl1
Group By &Date_Time
Order By &Date_Time;

No comments:

Post a Comment