Hi, I need help to design a query to display the records on a 30-min interval from a table.

The table contains data with different timings (e.g 9:45:42 AM). Is it possible to query and display the count of records on a half-hourly basis, i.e. 7:00 - 7:30AM, 7:31 - 8:30AM, etc?

Thanks in advance.

I am not sure how you would do all of this with a query, but I can help you do it with code if you are interested.

How many half hour itnervals are you interested in reporting on? 48 or 16, or some other number? You could build a local table to hold the results, and then use code to count records from your main table.

Do you need to constrain the data with date ranges as well? Like count all in half hour intervals from today only, or last week, or forever?

mmcdonal

Look it up at: http://wrox.books24x7.com

Here is a query solution I came up with.

I have a table with ComputerName, and RunTime for the scan, which is in hours:mins:secs.

I queried it like this (RunMinuteBA):

SELECT dbo_tblComputer.ComputerName, DatePart("h",[RunTime]) AS RunHour, DatePart("n",[RunTime]) AS RunMinute, IIf([RunMinute]<31,"B","A") AS BA
FROM dbo_tblComputer;

This gave me data that looks like this:

ComputerName RunHour RunMinute BA
W1335524 7 40 A
W1335570 5 12 B
W76HQRD1 7 30 B
W5YQHD61 7 32 A
W1372756 7 33 A
W7BBVRD1 7 34 A
W1247789 10 55 A
W51470D1 10 40 A

Then I queried this data like this:


SELECT RunMinuteBA.RunHour, RunMinuteBA.BA, Count(RunMinuteBA.ComputerName) AS CountOfComputerName
FROM RunMinuteBA
GROUP BY RunMinuteBA.RunHour, RunMinuteBA.BA
ORDER BY RunMinuteBA.RunHour, RunMinuteBA.BA DESC;

This gave me this data:

RunHour BA CountOfComputerName
0 B 25
0 A 15
1 B 6
1 A 11
2 B 8
2 A 6
3 B 11
3 A 18

Now if I were to put this on a report, then I would use the Detail section's On Format event to add this to a label:

Me.BA.Visible = False

If Me.BA = B Then
   Me.BALabel = ":00 - :30"
Else
   Me.BALabel = ":31 - :59"
End If

Does that work for you?

Two queries instead of one, but it works.







mmcdonal

Look it up at: http://wrox.books24x7.com