Time Based counts

[raji2012]

Hi,

I'm trying to create a RS report which displays counts by time. Users select a date and the need is to display the counts starting at 6 AM to every 12 hours for a week. The report has 2 columns one dimension and should be grouped and the other is count of and ID based on the created datetime. So the report will have 14 columns. Can anyone suggest a better way. I'm using oracle database.

[blom0344]

You are describing the requirements , not the way you have designed the report.  How can we then suggest a 'better way' for you? Can you be more specific?

[raji2012]

Hi,

I'm trying to get counts by time when there is date crossover at midnight. When a date is selected the report should display counts for a week based on the date that is selected. All the counts should be grouped every 12 hours starting at 6 AM on the date that is selected. It's 8.4 cognos and oracle.

Let me know if you need more information.

[Lynn]

What is the problem you are having? You are still describing your requirements and not really a specific issue you have in building the report.

Seems to me you need to filter for the selected date on or after 6 am through a week later up to 5:59 am. Then create a query item in your report that has the count query item and another that decides which bucket the count belongs in. A crosstab layout would be used to display the counts. A case statement might be the easiest way to derive the bucket values.

Code Select Expand


case
when [your datetime] between {6 am on selected day} and {5:59 pm on selected day} then 'Period 1'
when [your datetime] between {6 pm on selected day} and {5:59 am on selected day+1} then 'Period 2'
when [your datetime] between {6 am on selected day+1} and {5:59 pm on selected day+1} then 'Period 3'
when [your datetime] between {6 pm on selected day+1} and {5:59 pm on selected day+2} then 'Period 4'
...
etc
...
end


Otherwise create a query item for each bucket and use some case logic to count items that fall into it.