Query Needed - please help

[Gobind Singh]

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]

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.

[Purushoth Thambu]

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';