6 Replies Latest reply: Feb 6, 2013 6:41 AM by William Robertson RSS

    how to exclude a date from a query

    Maahjoor
      dear all,

      i have a query to audit user who are not login sice 2 days, but want to exclude those days which are holidays in calculations.

      SELECT active_directory_user FROM SYSTEM_USERS WHERE active_directory_user IN
      (SELECT DISTINCT active_directory_user FROM ADMIN.SESSION_AUDIT WHERE LOGON_TIME<SYSDATE-1);
      how to exclude sunday for example?
      my tables are below
      SQL> DESC SYSTEM_USERS
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       ACTIVE_DIRECTORY_USER                     NOT NULL VARCHAR2(30)
       PASSWORD                                           VARCHAR2(50)
       DEPT                                               VARCHAR2(20)
       ROOM                                               VARCHAR2(10)
       ROOM_PHONE                                         VARCHAR2(30)
       MOBILE#                                            VARCHAR2(20)
       TOTAL_LOGINS                                       NUMBER
       USER_STATUS                                        VARCHAR2(20)
      
      SQL> DESC SESSION_AUDIT
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       ACTIVE_DIRECTORY_USER                              VARCHAR2(30)
       LOGON_TIME                                NOT NULL DATE
       APPLICATION_USER                          NOT NULL VARCHAR2(30)
      Edited by: Maahjoor on 05-Feb-2013 22:50
        • 1. Re: how to exclude a date from a query
          jeneesh
          Maahjoor wrote:
          dear all,

          i have a query to audit user who are not login sice 2 days, but want to exclude those days which are holidays in calculations.

          SELECT active_directory_user FROM SYSTEM_USERS WHERE active_directory_user IN
          (SELECT DISTINCT active_directory_user FROM ADMIN.SESSION_AUDIT WHERE LOGON_TIME<SYSDATE-1);
          how to exclude sunday for example?
          select active_directory_user
          from system_users
          where active_directory_user in
            (
              select distinct active_directory_user
              from admin.session_audit
              where logon_time
                           <sysdate-decode(to_char(sysdate,'fmDAY'),'MONDAY',2,'SUNDAY',2,1)
            );
          • 2. Re: how to exclude a date from a query
            Maahjoor
            i dont know how to choose words which properly and fully thanks you.
            somehow, please accept the common words...THANK YOU SO MUCH.
            • 3. Re: how to exclude a date from a query
              Gurjeet
              function seen by first time "sysdate-decode".

              can you please provide the documentation for this function "sysdate-decode"


              SORRy for confusion
              • 4. Re: how to exclude a date from a query
                Rahul_India
                Mr. singh wrote:
                function seen by first time "sysdate-decode".


                can you please provide the documentation for this function "sysdate-decode"
                Its not a function .he is subtracting value returned by decode function from sysdate. :p

                Edited by: Rahul India on Feb 6, 2013 2:35 PM
                • 5. Re: how to exclude a date from a query
                  jeneesh
                  Mr. singh wrote:
                  function seen by first time "sysdate-decode".


                  can you please provide the documentation for this function "sysdate-decode"
                  It is like this: Subtract 2 from SYSDATE if the day is MONDAY or SUNDAY. Else subtract 1.
                  where logon_time
                                   <
                         sysdate  -   
                                   decode
                                      (
                                        to_char(sysdate,'fmDAY'),
                                                      'MONDAY',2,
                                                       'SUNDAY',2,1
                                      )
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                  • 6. Re: how to exclude a date from a query
                    William Robertson
                    Just to be extra-safe, I would specify the language inline, e.g:
                    to_char(sysdate,'DY','nls_date_language=English')