aspose file tools*
The moose likes JDBC 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
Bookmark "Query Needed - please help" Watch "Query Needed - please help" New topic
Author

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:

Userid:
Report:
RunDate:
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
Marshal

Joined: May 26, 2003
Posts: 30944
    
158

Gurps,
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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, 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
,userid
, 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
,userid
,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
,userid
,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: http://aspose.com/file-tools
 
subject: Query Needed - please help