BETWEEN statements for DAudit and Users query

I.S._Support_(bupauser3_-_(deleted))

Dear eLinkIf I want to get a range of Users who have not logged into the system for the last 12 months I can run something likedeclare @NumOfMonths as intdeclare @DateTo as Datetimedeclare @DateFrom as Datetimeset @NumOfMonths = 12set @DateTo = GetDate()set @DateFrom = DateAdd(m, -@NumOfMonths, @DateTo) -- display the date rangeselect @DateFrom as DateFrom, @DateTo as DateTo -- get results for this date rangeSelect k.NameFromKUAF kWhere k.id not in ( Select distinct d.UserID From DAudit d Where d.AuditDate > @DateFrom and d.AuditDate <= @DateTo)Now I recall that there is something special about the way dates are stored in LL, namely DAudit table, can thereforesomeone share what needs to be modified to the above?In other words how would I replace above variables for what can be used for LiveReports in LL9.2sp1.Any suggestions are appreciated greatly.Many thanks,

Find more posts tagged with

Comments

volvostephen

Message from via eLinkSelect k.NameFromKUAF kWhere k.deleted=0 and k.type=0 and k.id not in ( Select distinct d.UserID From DAudit d Where d.AuditDate > (Select (DateAdd(m, -12, GetDate()))))This will do that. You can replace the 12 with a %1 and have a numericinput field for the LiveReport. I also added in where the user is notdeleted and it is a user not group.Note: This is SQL Server Specific________________________________________Stephen FisherE-mail stephen.fisher@astrazeneca.com-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: January 26, 2006 6:15 AMTo: eLink RecipientSubject: BETWEEN statements for DAudit and Users queryBETWEEN statements for DAudit and Users queryPosted by Support, I.S. on 01/26/2006 06:04 AMDear eLinkIf I want to get a range of Users who have not logged into the system forthe last 12 months I can run something likedeclare @NumOfMonths as intdeclare @DateTo as Datetimedeclare @DateFrom as Datetimeset @NumOfMonths = 12set @DateTo = GetDate()set @DateFrom = DateAdd(m, -@NumOfMonths, @DateTo) -- display the date rangeselect @DateFrom as DateFrom, @DateTo as DateTo -- get results for this date rangeSelect k.NameFromKUAF kWhere k.id not in ( Select distinct d.UserID From DAudit d Where d.AuditDate > @DateFrom and d.AuditDate <= @DateTo)Now I recall that there is something special about the way dates are storedin LL, namely DAudit table, can thereforesomeone share what needs to be modified to the above?In other words how would I replace above variables for what can be used forLiveReports in LL9.2sp1.Any suggestions are appreciated greatly.Many thanks,[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.______________________________________________________________________This email has been scanned by the MessageLabs Email Security System.For more information please visit http://www.messagelabs.com/email ______________________________________________________________________

I.S._Support_(bupauser3_-_(deleted))

StephenThanks so much for your SQL, strangely I get this when I try to run it -Livelink Error: Report [ORA-00923: FROM keyword not found where expected - Select k.Name From KUAF k Where k.deleted=0 and k.type=0 and k.id not in ( Select distinct d.UserID From DAudit d Where d.AuditDate > (Select (DateAdd(m, -12, GetDate()))))] I need to check the sytax once again I guess???Kind regards

Roger_Duplin

Message from Roger Duplin <rduplin@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Hi,

The third embedded select statement needs a from clause even though it's not getting data from any table. I believe Oracle has a DUAL table with just 1 record that is good for this purpose. I'm not sure about MS SQL.

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, January 27, 2006 1:21 PM
To: eLink Recipient
Subject: Syntax Analysis for the query....

Syntax Analysis for the query....		Posted by BUPAUser3 (Support, I.S.) on 01/27/2006 01:17 PM
In reply to: RE BETWEEN statements for DAudit and Users query		Posted by ASTZCA01User1 (Fisher, Stephen J) on 01/26/2006 10:11 AM

Stephen

Thanks so much for your SQL, strangely I get this when I try to run it -

Livelink Error: Report
[ORA-00923: FROM keyword not found where expected - Select k.Name From KUAF k Where k.deleted=0 and k.type=0 and k.id not in ( Select distinct d.UserID From DAudit d Where d.AuditDate > (Select (DateAdd(m, -12, GetDate()))))]

I need to check the sytax once again I guess???

Kind regards

Roger_Duplin

Message from Roger Duplin <rduplin@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Hi again,

MS SQL is fine with a select statement that has not FROM clause. It is just oracle that needs it. You can use "from DUAL" in oracle and it should work fine.

Also GetDate() is MS SQL specific. Use SYSDATE with no () for Oracle. I do not know the function to subtract 12 months from the current date off hand.

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, January 27, 2006 1:47 PM
To: eLink Recipient
Subject: RE Syntax Analysis for the query....

RE Syntax Analysis for the query....		Posted by rduplin (Duplin, Roger) on 01/27/2006 01:46 PM
In reply to: Syntax Analysis for the query....		Posted by BUPAUser3 (Support, I.S.) on 01/27/2006 01:17 PM

Message from Roger Duplin <rduplin@opentext.com> via eLink

Hi, The third embedded select statement needs a from clause even though it's not getting data from any table. I believe Oracle has a DUAL table with just 1 record that is good for this purpose. I'm not sure about MS SQL.
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, January 27, 2006 1:21 PM
To: eLink Recipient
Subject: Syntax Analysis for the query....

Syntax Analysis for the query....		Posted by BUPAUser3 (Support, I.S.) on 01/27/2006 01:17 PM
In reply to: RE BETWEEN statements for DAudit and Users query		Posted by ASTZCA01User1 (Fisher, Stephen J) on 01/26/2006 10:11 AM

Stephen

Thanks so much for your SQL, strangely I get this when I try to run it -

Livelink Error: Report
[ORA-00923: FROM keyword not found where expected - Select k.Name From KUAF k Where k.deleted=0 and k.type=0 and k.id not in ( Select distinct d.UserID From DAudit d Where d.AuditDate > (Select (DateAdd(m, -12, GetDate()))))]

I need to check the sytax once again I guess???

Kind regards

volvostephen

Message from via eLinkThe original Syntax posted was MS SQL specific so my response was SQLspecific but it is obvious that you are getting an Oracle error - sorryabout that. As stated, you just need to put in an Oracle specificexpression that will return a value 12 months less then the current date.-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: January 27, 2006 2:10 PMTo: eLink RecipientSubject: RE RE Syntax Analysis for the query....______________________________________________________________________This email has been scanned by the MessageLabs Email Security System.For more information please visit http://www.messagelabs.com/email ______________________________________________________________________

eLink User

Message from Nair, Krishnankutty N. via eLinkIt is just add_months(trunc(sysdate),12) //add 12 months from today's dateadd_months(trunc(sysdate),-12) //subtract 12 months from today's dateSelect add_months(trunc(sysdate),12 )from dual (results this 1/27/2007Select add_months(trunc(sysdate),-12 )from dual (results this 1/27/2005Haven't tested this but bound to be slow I think your inner query is sayingGive me the unique people from my audittableWho made it to the system starting from a year backYour outer is sayingC'mon give me all the users who are not deletedAnd are not groups and not in the other setSelect k.Name From KUAF k Where k.deleted=0 and k.type=0 and k.id not in( Select distinct d.UserID From DAudit d Where d.AuditDate >add_months(trunc(sysdate),-12 ))Do I have your intention right.If so the inner query can be made to look for a specific event such aslogin so I could potentailly writeAlso you can say something likeCreate table my_slacker_table as(( Select distinct d.UserID From DAudit d Whereupper(d.event)=upper('login')And d.AuditDate > add_months(trunc(sysdate),-12 )))Then the outer query asSelect k.Name From KUAF k Where k.deleted=0 and k.type=0 and k.id not in (Select UserID from my_slacker_table)Sometimes having a temp table makes it run faster.I am no oracle expertBut I do it all the time creating temp tables for my "IN" conditionAnd I do not know if it is TOAD or not. -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Friday, January 27, 2006 1:49 PMTo: eLink RecipientSubject: RE RE RE Syntax Analysis for the query....RE RE RE Syntax Analysis for the query....Posted by Fisher, Stephen J on 01/27/2006 02:49 PMMessage from via eLinkThe original Syntax posted was MS SQL specific so my response was SQLspecific but it is obvious that you are getting an Oracle error - sorryabout that. As stated, you just need to put in an Oracle specificexpression that will return a value 12 months less then the currentdate.-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: January 27, 2006 2:10 PMTo: eLink RecipientSubject: RE RE Syntax Analysis for the query....______________________________________________________________________This email has been scanned by the MessageLabs Email Security System.For more information please visit http://www.messagelabs.com/email ______________________________________________________________________[To reply to this thread, use your normal E-mail reply function.]============================================================Topic: BETWEEN statements for DAudit and Users queryhttps://knowledge.opentext.com/knowledge/livelink.exe/open/4480162Discussion: 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.

I.S._Support_(bupauser3_-_(deleted))

Dear NairWould there be something similar for days?add_months(trunc(sysdate),-12 )Let's say I want to make a query against DAudit.AuditDate field for the events in the last few days, one to five days can I use something similar to what you suggested for months?Regards

eLink User

Message from Nair, Krishnankutty N. via eLinkI do not think for day subtraction /addition you need any functionsIf you say trunc(sysdate)-7 it is 7 days less than now.Run this query and you should get it.All trunc is doing isTell oracle to omit the time part because in Oracle A date has a time part to it I believeselect sysdate,trunc(sysdate),trunc(sysdate)-7 from dual -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Friday, February 03, 2006 10:53 AMTo: eLink RecipientSubject: Syntax Analysis for the query - add_months(trunc(sysdate),-12 )Syntax Analysis for the query - add_months(trunc(sysdate),-12 )Posted by Support, I.S. on 02/03/2006 11:51 AMDear NairWould there be something similar for days?add_months(trunc(sysdate),-12 )Let's say I want to make a query against DAudit.AuditDate field for theevents in the last few days, one to five days can I use somethingsimilar to what you suggested for months?Regards[To reply to this thread, use your normal E-mail reply function.]============================================================Topic: BETWEEN statements for DAudit and Users queryhttps://knowledge.opentext.com/knowledge/livelink.exe/open/4480162Discussion: 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.