3 Replies Latest reply on Jul 23, 2015 7:10 AM by BluShadow

    Change a manually entered date to a variable

    2996759

      First off, I apologize this is literally my first time dealing with SQL and I just had this placed in my lap, so if I word something in a way that doesn't make sense or ask an ignorant question then forgive me.

       

      I need to change a line of code in this query from a date you have to manually enter every day, into a date variable that will set the date for the previous day, like Today - 1.

       

      where statuscode = 'WW' and statusdatetime like '2015-07-21%'

       

      So when I run this query it'll automatically assign yesterday's date.

        • 1. Re: Change a manually entered date to a variable
          BluShadow

          Use SYSDATE to get the current date/time, and truncate it if you just need the current date with the time as 00:00:00...

           

          SQL> select sysdate-1 from dual;
          SYSDATE-1
          -------------------
          21/07/2015 15:45:14

           

          1 row selected.

           

          SQL> select trunc(sysdate)-1 from dual;
          TRUNC(SYSDATE)-1
          -------------------
          21/07/2015 00:00:00

           

          1 row selected.

           

          Always remember to treat dates as DATE datatypes and not strings.  So you shouldn't be using a LIKE comparison with it.

           

          where statuscode = 'WW' and statusdatetime like '2015-07-21%'

           

          would be better as:

           

          where statuscode = 'WW'

          and   statusdatetime >= trunc(sysdate)-1

          and   statusdatetime < trunc(sysdate)

           

          (I assume statusdatetime is a DATE datatype?)

           

          Don't be tempted to do:

           

          and   trunc(statusdatetime) = trunc(sysdate)-1

          as that could prevent the use of any index on the statusdatetime column

          • 2. Re: Change a manually entered date to a variable
            2996759

            Well, I understand the concept of what you're saying, but I don't seem to be able to successfully apply it. Here's my query:

             

            with first_cur as

            (select useruid,useractivityloguid, statusdatetime,duration, TWAUS.need_work_req(nw.useractivityloguid) enroute_id from (

            select ual.* from twaus.useractivitylog ual

            where statuscode = 'WW' and statusdatetime like '2015-07-21%'

            and useruid in (select useruid from twaus.userbu where buid like 'NTX%') ) nw)

            select first_cur.useruid, first_cur.useractivityloguid, first_cur.statusdatetime need_work_time,

            ual2.reference, ual2.useractivityloguid,ual2.statusdatetime enroute_time,

            substr(ual2.reference,10) workorder_id,

            TWAUS.last_assigned(first_cur.statusdatetime, wo.workorderuid) assign_time,

            TWAUS.last_assignor(first_cur.statusdatetime, wo.workorderuid) assigned_by,

            wo.actendatetime,wo.actstdatetime,wo.acttravelduration,wo.smsjobnumber

            from twaus.useractivitylog ual2, first_cur, twaus.workorder wo

            where ual2.useractivityloguid = first_cur.enroute_id

            and substr(ual2.reference,10) = wo.workorderid

            and wo.tzi = 'CDT'

            and wo.jobstatus = 'JC'

            • 3. Re: Re: Change a manually entered date to a variable
              BluShadow

              "don't seem able to successfully apply it" doesn't help us to understand what the problem is.  We don't have your tables, so cannot test and see what's happening.  You have to be more clear in what the problem is you're seeing.

               

              First things first... get into the habit of formatting your code to make it readable (not just here on the community, but within your own code, as it will help you, and anyone else reading it in the future).

               

              with first_cur as

                (select useruid

                      ,useractivityloguid

                      ,statusdatetime

                      ,duration

                      ,TWAUS.need_work_req(nw.useractivityloguid) enroute_id

                from (select ual.*

                      from  twaus.useractivitylog ual

                      where  statuscode = 'WW'

                      and    statusdatetime >= trunc(sysdate-1)

                      and    statusdatetime < trunc(sysdate)

                      and    useruid in (select useruid

                                          from  twaus.userbu

                                          where  buid like 'NTX%'

                                        )

                      ) nw

                )

              select first_cur.useruid

                    ,first_cur.useractivityloguid

                    ,first_cur.statusdatetime need_work_time

                    ,ual2.reference

                    ,ual2.useractivityloguid

                    ,ual2.statusdatetime enroute_time

                    ,substr(ual2.reference,10) workorder_id

                    ,TWAUS.last_assigned(first_cur.statusdatetime, wo.workorderuid) assign_time

                    ,TWAUS.last_assignor(first_cur.statusdatetime, wo.workorderuid) assigned_by

                    ,wo.actendatetime

                    ,wo.actstdatetime

                    ,wo.acttravelduration

                    ,wo.smsjobnumber

              from  twaus.useractivitylog ual2

                    ,first_cur

                    ,twaus.workorder wo

              where  ual2.useractivityloguid = first_cur.enroute_id

              and    substr(ual2.reference,10) = wo.workorderid

              and    wo.tzi = 'CDT'

              and    wo.jobstatus = 'JC'