File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Query Needed - please help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Query Needed - please help" Watch "Query Needed - please help" New topic

Query Needed - please help

Gobind Singh
Ranch Hand

Joined: Aug 04, 2006
Posts: 62
I am no sql expert. I need query which i can fire using JDBC.

The table to be queried is as follows:

report varchar2(60),
rundate date, ----Time also included in this.
userid varchar2(20)

This table logs userid against a report that the user has run. It also logs the time they run the report.

I want to query this table so it gives me the following:

runs per day:
runs per week:
runs per month:

Please can someone give me some sql? I am stuck on how to derive the last 3 statistics.
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33106

Are you trying to get the average # runs per day or the number of runs each day over a time period?

Either way, you need to convert date to a day without a timestamp. Take a look at the date functions your database provides to do this.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425
Assuming you are using Oracle db, you can make use of Analytics functions to get the desired report. Below queries were sample ones I executed against single user in test data. You can make them run for all users. Instead of 3 queries you can also fuse them in one query if you use functions like ROLLUP or CUBE. For more information you can consult the Oracle Datawarehousing docs.

-- Query to find the reports, runs per day

select distinct trunc(rundate) as dat
, trim(report) report
,count(rundate) over(partition by userid,report,trunc(rundate)) as day_cnt
from report_test
where userid='SYS';

-- Query to find the reports, runs per week

select distinct to_char(rundate,'ww') as week
,trim(report) report
,count(rundate) over(partition by userid,report,to_char(rundate,'ww'))
as week_cnt
from report_test
where userid='SYS';

-- Query to find the reports, runs per month

select distinct to_char(rundate,'mm') as month
,trim(report) report
,count(rundate) over(partition by userid,report,to_char(rundate,'mm'))
as month_cnt
from report_test
where userid='SYS';

I agree. Here's the link:
subject: Query Needed - please help
It's not a secret anymore!