Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
THRUST SERVICES & TOOLS
CLOUD EDITIONS
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Intelligence (Analytics)
Need help with the query - BIRT Designer Professional
Amruta29
I want to retrieve total number of web activities for each hour interval.
I am hoping to get this output
Today
hour(EST) Number of Web Activities
12am-1am
1am-2m
....
11pm -12pm
How to I write the query in BIRT designer professional. I am connected to My SQL database.
Find more posts tagged with
Comments
mwilliams
Hi Amruta29,
One thing you could do is bring in all activities and group your table by hour and use an aggregation to count each item in the group. If you're wanting to do this in a query, I'll have to test on it, but you could probably use the count and union sql functions to do it. Not 100% on that though.
Amruta29
Michael,
Thanks. It would be of great help if you could tell me the actual Query.
Thanks Again
Monma
If you are still struggling with this. Paste the table structure and I will put together a stored procedure that will do what you need. In case you cannot add an sp to the db I will write a query. It is that I usually prefer using sp you get more freedom and better performance.
Amruta29
Here you go
Name is web Actvity and Create Date is what you would need to use to get hour intervals. you can use 11/4 date
Create Date Name
11/4/2009 0:00 p_login
11/4/2009 0:00 p_login
11/4/2009 0:05 p_login
11/4/2009 0:10 p_login
11/4/2009 0:15 p_login
11/4/2009 0:20 p_login
11/4/2009 0:25 p_login
11/4/2009 0:30 p_login
11/4/2009 0:35 p_login
11/4/2009 0:40 p_login
11/4/2009 0:45 p_login
11/4/2009 0:50 p_login
11/4/2009 0:55 p_login
11/4/2009 1:00 p_login
11/4/2009 1:05 p_login
11/4/2009 1:10 p_login
11/4/2009 1:15 p_login
11/4/2009 1:20 p_login
11/4/2009 1:25 p_login
11/4/2009 1:30 p_login
11/4/2009 1:35 p_login
11/4/2009 1:40 p_login
11/4/2009 1:45 p_login
11/4/2009 1:50 p_login
11/4/2009 1:55 p_login
11/4/2009 2:00 p_login
11/4/2009 2:05 p_login
11/4/2009 2:10 p_login
11/4/2009 2:15 p_login
11/4/2009 2:20 p_login
11/4/2009 2:25 p_login
11/4/2009 2:30 p_login
11/4/2009 2:35 p_login
11/4/2009 2:35 p_login
11/4/2009 2:40 p_login
11/4/2009 2:45 p_login
11/4/2009 2:51 p_login
11/4/2009 2:56 p_login
11/4/2009 3:01 p_login
11/4/2009 3:06 p_login
11/4/2009 3:11 p_login
11/4/2009 3:16 p_login
11/4/2009 3:21 p_login
11/4/2009 3:26 p_login
11/4/2009 3:31 p_login
11/4/2009 3:36 p_login
11/4/2009 3:36 p_login
11/4/2009 3:41 p_login
11/4/2009 3:41 p_login
11/4/2009 3:46 p_login
11/4/2009 3:51 p_login
11/4/2009 3:51 p_login
11/4/2009 3:56 p_login
11/4/2009 4:01 p_login
11/4/2009 4:09 p_login
11/4/2009 4:14 p_login
11/4/2009 4:19 p_login
11/4/2009 4:24 p_login
11/4/2009 4:29 p_login
11/4/2009 4:34 p_login
11/4/2009 4:39 p_login
11/4/2009 4:44 p_login
11/4/2009 4:49 p_login
11/4/2009 4:49 p_login
11/4/2009 4:54 p_login
11/4/2009 4:59 p_login
11/4/2009 5:04 p_login
11/4/2009 5:09 p_login
11/4/2009 5:14 p_login
11/4/2009 5:19 p_login
11/4/2009 5:24 p_login
11/4/2009 5:29 p_login
11/4/2009 5:34 p_login
11/4/2009 5:34 p_login
11/4/2009 5:39 p_login
11/4/2009 5:44 p_login
11/4/2009 5:49 p_login
11/4/2009 5:54 p_login
11/4/2009 5:59 p_login
11/4/2009 6:03 p_login
11/4/2009 6:04 p_login
11/4/2009 6:10 p_login
11/4/2009 6:15 p_login
11/4/2009 6:20 p_login
11/4/2009 6:25 p_login
11/4/2009 6:25 p_login
11/4/2009 6:30 p_login
11/4/2009 6:35 p_login
11/4/2009 6:40 p_login
11/4/2009 6:45 p_login
11/4/2009 6:50 p_login
11/4/2009 6:55 p_login
11/4/2009 7:00 p_login
11/4/2009 7:05 p_login
11/4/2009 7:05 p_login
11/4/2009 7:10 p_login
11/4/2009 7:15 p_login
11/4/2009 7:20 p_login
11/4/2009 7:25 p_login
11/4/2009 7:30 p_login
11/4/2009 7:35 p_login
11/4/2009 7:35 p_login
11/4/2009 7:40 p_login
11/4/2009 7:45 p_login
11/4/2009 7:50 p_login
11/4/2009 7:55 p_login
11/4/2009 8:00 p_login
11/4/2009 8:05 p_login
11/4/2009 8:05 p_login
11/4/2009 8:10 p_login
11/4/2009 8:15 p_login
11/4/2009 8:20 p_login
11/4/2009 8:25 p_login
11/4/2009 8:30 p_login
11/4/2009 8:35 p_login
11/4/2009 8:35 p_login
11/4/2009 8:40 p_login
11/4/2009 8:45 p_login
11/4/2009 8:51 p_login
11/4/2009 8:56 p_login
11/4/2009 9:01 p_login
11/4/2009 9:06 p_login
11/4/2009 9:11 p_login
11/4/2009 9:16 p_login
11/4/2009 9:16 p_login
11/4/2009 9:21 p_login
11/4/2009 9:26 p_login
11/4/2009 9:31 p_login
11/4/2009 9:36 p_login
11/4/2009 9:41 p_login
11/4/2009 9:46 p_login
11/4/2009 9:47 p_login
11/4/2009 9:48 p_login
11/4/2009 9:48 p_login
11/4/2009 9:49 p_login
11/4/2009 9:49 a_LoginSuccess
11/4/2009 9:49 p_acctSummary
11/4/2009 9:50 a_Logout
11/4/2009 9:50 p_login
11/4/2009 9:50 p_login
11/4/2009 9:50 a_LoginSuccess
11/4/2009 9:50 p_acctSummary
11/4/2009 9:50 a_Logout
11/4/2009 9:50 p_login
11/4/2009 9:51 p_login
11/4/2009 9:52 p_login
11/4/2009 9:52 p_login
11/4/2009 9:52 p_login
11/4/2009 9:53 a_LoginFailure
11/4/2009 9:53 p_login
11/4/2009 9:53 a_LoginSuccess
11/4/2009 9:53 a_Logout
11/4/2009 9:53 p_login
11/4/2009 9:53 p_login
11/4/2009 9:53 a_LoginSuccess
11/4/2009 9:53 p_acctSummary
11/4/2009 9:53 a_Logout
11/4/2009 9:53 p_login
11/4/2009 9:55 p_login
11/4/2009 9:55 p_login
11/4/2009 9:56 p_login
11/4/2009 9:56 p_login
11/4/2009 9:56 a_LoginSuccess
11/4/2009 9:56 p_acctSummary
11/4/2009 9:56 a_LoginSuccess
11/4/2009 9:56 p_acctSummary
11/4/2009 9:56 a_Logout
11/4/2009 9:56 p_login
11/4/2009 9:57 a_Logout
11/4/2009 9:57 p_login
11/4/2009 10:01 p_login
11/4/2009 10:01 p_login
11/4/2009 10:06 p_login
11/4/2009 10:11 p_login
11/4/2009 10:14 a_LoginFailure
11/4/2009 10:14 a_LoginFailure
11/4/2009 10:15 a_LoginFailure
11/4/2009 10:15 a_LoginSuccess
11/4/2009 10:16 p_login
11/4/2009 10:21 p_login
11/4/2009 10:26 p_login
11/4/2009 10:31 p_login
11/4/2009 10:31 p_login
11/4/2009 10:36 p_login
11/4/2009 10:41 p_login
11/4/2009 10:46 p_login
11/4/2009 10:49 a_LoginSuccess
11/4/2009 10:49 p_index
11/4/2009 10:49 p_acctSummary
11/4/2009 10:49 p_programFormView
11/4/2009 10:49 p_programSurveyApptView
11/4/2009 10:51 a_LoginSuccess
11/4/2009 10:51 p_login
11/4/2009 10:51 p_login
11/4/2009 10:51 p_login
11/4/2009 10:52 a_WorkOnAccount
11/4/2009 10:56 p_login
11/4/2009 10:56 p_login
11/4/2009 11:01 p_login
11/4/2009 11:02 p_paymentFormView
11/4/2009 11:06 p_login
11/4/2009 11:12 p_login
11/4/2009 11:17 p_login
11/4/2009 11:17 p_login
11/4/2009 11:22 p_login
11/4/2009 11:23 p_login
11/4/2009 11:24 a_LoginSuccess
11/4/2009 11:24 p_contactFormView
11/4/2009 11:25 p_apptFormView
11/4/2009 11:27 p_login
11/4/2009 11:27 p_fundAcct
11/4/2009 11:28 p_index
11/4/2009 11:28 p_acctSummary
11/4/2009 11:28 a_Logout
11/4/2009 11:28 p_login
11/4/2009 11:29 a_Logout
11/4/2009 11:30 a_FinishWorkOnAccount
11/4/2009 11:31 a_Logout
11/4/2009 11:32 p_login
11/4/2009 11:37 p_login
11/4/2009 11:42 p_login
11/4/2009 11:47 p_login
11/4/2009 11:52 p_login
11/4/2009 11:57 p_login
11/4/2009 12:02 p_login
11/4/2009 12:02 p_login
11/4/2009 12:07 p_login
11/4/2009 12:12 p_login
11/4/2009 12:17 p_login
11/4/2009 12:22 p_login
11/4/2009 12:27 p_login
11/4/2009 12:32 p_login
11/4/2009 12:37 p_login
11/4/2009 12:42 p_login
11/4/2009 12:47 p_login
11/4/2009 12:52 p_login
11/4/2009 12:57 p_login
11/4/2009 13:02 p_login
11/4/2009 13:07 p_login
11/4/2009 13:12 p_login
11/4/2009 13:17 p_login
11/4/2009 13:22 p_login
11/4/2009 13:27 p_login
11/4/2009 13:32 p_login
11/4/2009 13:37 p_login
11/4/2009 13:42 p_login
11/4/2009 13:42 p_login
11/4/2009 13:47 p_login
11/4/2009 13:52 p_login
11/4/2009 13:58 p_login
11/4/2009 13:58 p_login
11/4/2009 14:03 p_login
11/4/2009 14:08 p_login
11/4/2009 14:13 p_login
11/4/2009 14:18 p_login
11/4/2009 14:23 p_login
11/4/2009 14:28 p_login
11/4/2009 14:33 p_login
11/4/2009 14:38 p_login
11/4/2009 14:43 p_login
11/4/2009 14:48 p_login
11/4/2009 14:53 p_login
11/4/2009 14:58 p_login
11/4/2009 15:03 p_login
11/4/2009 15:08 p_login
11/4/2009 15:13 p_login
11/4/2009 15:18 p_login
11/4/2009 15:23 p_login
11/4/2009 15:28 p_login
11/4/2009 15:28 p_login
11/4/2009 15:33 p_login
11/4/2009 15:38 p_login
11/4/2009 15:43 p_login
11/4/2009 15:48 p_login
11/4/2009 15:53 p_login
11/4/2009 15:53 p_login
11/4/2009 15:58 p_login
11/4/2009 16:03 p_login
11/4/2009 16:08 p_login
11/4/2009 16:13 p_login
11/4/2009 16:18 p_login
11/4/2009 16:18 p_login
11/4/2009 16:24 p_login
11/4/2009 16:29 p_login
11/4/2009 16:34 p_login
11/4/2009 16:39 p_login
11/4/2009 16:44 p_login
11/4/2009 16:49 p_login
11/4/2009 16:54 p_login
11/4/2009 16:59 p_login
11/4/2009 17:04 p_login
11/4/2009 17:09 p_login
11/4/2009 17:14 p_login
11/4/2009 17:19 p_login
11/4/2009 17:24 p_login
11/4/2009 17:24 p_login
11/4/2009 17:29 p_login
11/4/2009 17:34 p_login
11/4/2009 17:39 p_login
11/4/2009 17:44 p_login
11/4/2009 17:49 p_login
11/4/2009 17:49 p_login
11/4/2009 17:54 p_login
11/4/2009 17:54 p_login
11/4/2009 17:59 p_login
11/4/2009 17:59 p_login
11/4/2009 18:04 p_login
11/4/2009 18:09 p_login
11/4/2009 18:14 p_login
11/4/2009 18:19 p_login
11/4/2009 18:19 p_login
11/4/2009 18:24 p_login
11/4/2009 18:29 p_login
11/4/2009 18:34 p_login
11/4/2009 18:39 p_login
11/4/2009 18:44 p_login
11/4/2009 18:49 p_login
11/4/2009 18:54 p_login
11/4/2009 18:59 p_login
11/4/2009 19:04 p_login
11/4/2009 19:09 p_login
11/4/2009 19:14 p_login
11/4/2009 19:19 p_login
11/4/2009 19:24 p_login
11/4/2009 19:24 p_login
11/4/2009 19:29 p_login
11/4/2009 19:34 p_login
11/4/2009 19:39 p_login
11/4/2009 19:44 p_login
11/4/2009 19:44 p_login
11/4/2009 19:49 p_login
11/4/2009 19:49 p_login
11/4/2009 19:54 p_login
11/4/2009 19:59 p_login
11/4/2009 20:04 p_login
11/4/2009 20:09 p_login
11/4/2009 20:15 p_login
11/4/2009 20:20 p_login
11/4/2009 20:25 p_login
11/4/2009 20:30 p_login
11/4/2009 20:35 p_login
11/4/2009 20:40 p_login
11/4/2009 20:40 p_login
11/4/2009 20:45 p_login
11/4/2009 20:50 p_login
11/4/2009 20:55 p_login
11/4/2009 21:00 p_login
11/4/2009 21:05 p_login
11/4/2009 21:10 p_login
11/4/2009 21:15 p_login
11/4/2009 21:20 p_login
11/4/2009 21:25 p_login
11/4/2009 21:30 p_login
11/4/2009 21:35 p_login
11/4/2009 21:40 p_login
11/4/2009 21:45 p_login
11/4/2009 21:45 p_login
11/4/2009 21:50 p_login
11/4/2009 21:55 p_login
11/4/2009 21:55 p_login
11/4/2009 22:00 p_login
11/4/2009 22:05 p_login
11/4/2009 22:10 p_login
11/4/2009 22:15 p_login
11/4/2009 22:20 p_login
11/4/2009 22:25 p_login
11/4/2009 22:30 p_login
11/4/2009 22:30 p_login
11/4/2009 22:30 p_login
11/4/2009 22:35 p_login
11/4/2009 22:40 p_login
11/4/2009 22:45 p_login
11/4/2009 22:50 p_login
11/4/2009 22:55 p_login
11/4/2009 23:00 p_login
11/4/2009 23:05 p_login
11/4/2009 23:10 p_login
11/4/2009 23:15 p_login
11/4/2009 23:21 p_login
11/4/2009 23:26 p_login
11/4/2009 23:31 p_login
11/4/2009 23:36 p_login
11/4/2009 23:41 p_login
11/4/2009 23:46 p_login
11/4/2009 23:51 p_login
11/4/2009 23:56 p_login
mwilliams
Amruta29,
One way would be to do something like:
select count(*) as HourlyCount,
'12am-1am' as HourRange
from tableName
where HOUR(CreateDate) = 0
union
select count(*) as HourlyCount,
'1am-2am' as HourRange
from tableName
where HOUR(CreateDate) = 1
....
....
etc.
I did this with the sample database and "month", but hour should work the same, I just don't have a good way to test it.
Hope this helps. Let me know if you have questions or issues.
Monma may also have a different way.
Amruta29
Thanks. I will try this.
mwilliams
Let me know!
