Forum Stats

  • 3,749,903 Users
  • 2,250,074 Discussions
  • 7,866,685 Comments

Discussions

Change a manually entered date to a variable

2996759
2996759 Member Posts: 2
edited Jul 23, 2015 3:10AM in SQL Developer

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.

Tagged:

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,285 Red Diamond
    edited Jul 22, 2015 10:49AM

    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

  • 2996759
    2996759 Member Posts: 2
    edited Jul 22, 2015 11:26AM

    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'

  • BluShadow
    BluShadow Member, Moderator Posts: 41,285 Red Diamond
    edited Jul 23, 2015 3:10AM

    "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'
    
This discussion has been closed.