User Login Report

Greg_Griffiths_(ggriffiths_-_(deleted))

Dear All, I've got a situation where I have an Audit table containing a vareity of events against a user id with a date, what I need to do is to give a count of LOGIN events per month for a specific subset of users, but counting only one LOGIN event per day per user. The final report needs to look something like :Jan-2000 112Feb-2000 109I've got the following SQL to work for one specific month :select max(rownum) from (select distinct to_char(auditdate,'dd-mm-yyyy'),useridfrom dauditwhere to_char(auditdate,'Mon-yyyy')='Sep-2001'and event='LOGIN'and userid in (1000,16697,152636,182421,347772,426028,858391)) and I have the following SQL to list the results in the way I need to, but not counting one event per user per day :select to_char(daudit.auditdate,'Mon-yyyy') as TimeFrame,count(userid) as Loginsfrom dauditwhere daudit.event='LOGIN'and userid in (select distinct id from kuaf where id in (select childid from kuafchildren where id in (126425)))group by to_char(daudit.auditdate,'Mon-yyyy'),to_char(daudit.auditdate,'yyyymm')order by to_char(daudit.auditdate,'yyyymm')Can someone help me merge them together ?

Chris_Jones

Message from Jones, Christopher L. \(IT\) via eLinkYou may want to try using the DATEPART function in SQL to break up thedays and if you only want one login per day try using the MAX functionon the data this wold only give you one count per user per day.Here is a sample of how I use the date part function:ChrisDATEPART(yy, mssqlsa.DAudit.AuditDate) AS [Year], DATEPART(qq,mssqlsa.DAudit.AuditDate) AS Quarter, DATEPART(mm,mssqlsa.DAudit.AuditDate) AS [Month], DATEPART(dw,mssqlsa.DAudit.AuditDate) AS WeekDay, DATEPART(dd,mssqlsa.DAudit.AuditDate) AS [Date of the Month], DATEPART(hh,mssqlsa.DAudit.AuditDate) AS [Hour] -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Thursday, November 03, 2005 5:23 AMTo: eLink RecipientSubject: User Login ReportUser Login ReportPosted by Griffiths, Greg on 11/03/2005 06:21 AMDear All, I've got a situation where I have an Audit table containing avareity of events against a user id with a date, what I need to do is togive a count of LOGIN events per month for a specific subset of users,but counting only one LOGIN event per day per user. The final reportneeds to look something like :Jan-2000 112Feb-2000 109I've got the following SQL to work for one specific month :select max(rownum) from (select distinct to_char(auditdate,'dd-mm-yyyy'),useridfrom dauditwhere to_char(auditdate,'Mon-yyyy')='Sep-2001'and event='LOGIN'and userid in (1000,16697,152636,182421,347772,426028,858391)) and I have the following SQL to list the results in the way I need to,but not counting one event per user per day :select to_char(daudit.auditdate,'Mon-yyyy') as TimeFrame,count(userid)as Logins from daudit where daudit.event='LOGIN'and userid in (select distinct id from kuaf where id in (select childidfrom kuafchildren where id in (126425))) group byto_char(daudit.auditdate,'Mon-yyyy'),to_char(daudit.auditdate,'yyyymm')order by to_char(daudit.auditdate,'yyyymm')Can someone help me merge them together ?[To reply to this thread, use your normal E-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe/open/2249677Livelink Server:https://knowledge.opentext.com/knowledge/livelink.exeTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.livereportsdiscussion@elinkkc.opentext.com.