I have an urgent need to find the top 10 users who added the most objects in livelink within a specif date range and the total size added by these top 10 users. While I can do small simple queries this one is killing my brain. I am a novice SQL person. Any help would be greatly appreciated. Oracle is the DB. Thanks
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
LIve Report Help
- Thread starter NGjn
- Start date
Also the structure of auditing has changed in the latest version.In 9.5 they have enhanced auditing and you might want to let us know what livelink,what db it runs on.If it is Oracle I may take a whack at it,if it is SQLserver
I don't have a clue
Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
I don't have a clue
Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
- Thread starter
- #4
I know which tables to use my problem comes from the actual structure of the query (I am not good at inner & outter joins and the dates in Oracle for me never come out right. As I mentioned, I am using a Oracle DB and running Livelink 9.2 SP1 Any help would be great.
Syntergy
Programmer
- Jun 2, 2004
- 52
I think this SQL will give you what you want. There are a couple of issues. One is this gives you the number of versions added. I think that is what you really want as opposed to documents in dtree.
The other issue is how to just bring back the top ten. Adding "having count(*) > 10" limits the report to those users that added 10 or more versions for the time period. If you remove that line it will give you everybody that added any versions.
In this example I am checking for users adding content in February 2006. I am limiting my report to those users that added at least 10 versions. The total size, sum(datasize) is in bytes.
select name, count(*), sum(datasize)
from dversdata, kuaf
where owner = id
and filecdate between to_date('02/01/2006 00:00:00','mm/dd/yyyy hh24:mi:ss') and to_date('02/28/2006 23:59:59','mm/dd/yyyy hh24:mi:ss')
group by name
having count(*) > 10
order by 2 desc;
If you are using SQL*Plus it helps the formating if you enter
column name format a30;
before the query. It shortens the name display to 30 characters so your report doesn't wrap around.
The other issue is how to just bring back the top ten. Adding "having count(*) > 10" limits the report to those users that added 10 or more versions for the time period. If you remove that line it will give you everybody that added any versions.
In this example I am checking for users adding content in February 2006. I am limiting my report to those users that added at least 10 versions. The total size, sum(datasize) is in bytes.
select name, count(*), sum(datasize)
from dversdata, kuaf
where owner = id
and filecdate between to_date('02/01/2006 00:00:00','mm/dd/yyyy hh24:mi:ss') and to_date('02/28/2006 23:59:59','mm/dd/yyyy hh24:mi:ss')
group by name
having count(*) > 10
order by 2 desc;
If you are using SQL*Plus it helps the formating if you enter
column name format a30;
before the query. It shortens the name display to 30 characters so your report doesn't wrap around.
- Status
Similar threads