• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query Needed - please help

 
Gobind Singh
Ranch Hand
Posts: 62
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 33674
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 425
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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';

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic