In order to show ALL times, even those without data, your times would have to be in your data - usually in a data table.
I would see about creating a table with something like the following structure:
TIME_GROUPS
START_HOUR Number(2)
START_MINUTE Number(2)
END_MINUTE Number(2)
GROUP_NAME Varchar(15)
Your data in this table would look something like this:
Start Hour Start_Minute End_Minute Group_Name
8 0 10 8:00
8 10 20 8:10
...
8 50 60 8:50
...
13 0 10 1:00
You'll notice how I set up the "end minute" for the last block of the hour as 60 - this will help you do the comparison in your SQL. I also set this up to work on a 24-hour clock - also to make it easier to do the comparisons in SQL.
I work in Oracle and I'm not familiar with which commands are the same between Oracle and MS SQL and which are not, so you may need to translate some of this into something MS SQL would understand. But, I would do something like this in my SQL:
Select tg.GROUP_NAME,
t.LAST_CHANGE_DATE,
t.FIELD1,
t.FIELD2
from Table1 as t
left join Time_Groups as tg
on tg.START_HOUR = To_Number(To_Char(t.LAST_CHANGE_DATE, 'HH24'))
where To_Number(To_Char(t.LAST_CHANGE_DATE, 'MI'))
between tg.START_MINUTE and tg.END_MINUTE
In the report, I would then set up a group on the GROUP_NAME field, a Sort on the LAST_CHANGE_DATE field, and put my data in the details. This way your data will be grouped together in your 10-minute time-slots and then in time-order within those time slots.
-Dell
Edited by hilfy - 28 May 2007 at 1:17pm
IP Logged
