CODEWITHSUNDEEP
sqlbusiness-objects
Sami
Sami

Reputation: 3

BO 4.2 edit time value in query script

im trying to run a report that is based on 4 dominant values. it has a date and time value of which im good with the date values but am stuck with the times.

what i need to achieve is, to check all departing stocks scheduled for today between 0600-1200, whose request tickets were issued between yesterday 1800 till today 0459. this report will get automatically dispatched to a particular mailbox at 0500.

the trouble is the way out host system records the 2 time values are in different formats. the stock times are recorded in format e.g.: 0600,0700,0800 and so on and are in local time. whereas the request ticket time is in GMT and is recorded in format e.g.: 14.00.00,15.00.00,16.00.00 and so on. i dont know how to adjust the request ticket timings. even if GMT, between 14.00.00 of yesterday and 01.00.00 of today.

im sure must be a small thing but im not an expert at this. greatly appreciate any help. a sample of my query script is as follows:

'''

TKT_OWNR.BO_VW_TKT_DELIVERY.DEPARTURE_DATE  =  sysdate-1
   AND
   TKT_OWNR.BO_VW_TKT_DELIVERY.DEPARTURE_TIME  BETWEEN  '0600' AND '1200'
   AND
   TKT_OWNR.BO_VW_TKT.ISSUE_DATE  BETWEEN  sysdate-1 AND sysdate
   AND
   TKT_OWNR.BO_VW_TKT.ISSUE_TIME_GMT  BETWEEN  '14.00.00' AND '01.00.00'
  )
'''

Upvotes: 0

Views: 51

Answers (1)

Isaac
Isaac

Reputation: 3363

I understand you are likely using Oracle for your database. I am not as well versed in Oracle SQL so here is my approach using SQL Server. I hope this gives you some ideas of what to try.

Basically, you need to derive datetime columns for your DEPARTURE date and time and ISSUE date and time. In my answer I am going to do that in a derived column within a subquery.

For the DEPARTURE_DATETIME I need to convert the DEPARTURE_DATE to a string and then concatenate it with the DEPARTURE_TIME with a colon between the hours and minutes and a space between the date and time strings.

CONVERT(DATETIME, CONVERT(CHAR(10), DEPARTURE_DATE) + ' ' + 
     LEFT(DEPARTURE_TIME, 2) + ':' + 
     RIGHT(DEPARTURE_TIME, 2)) AS [DEPARTURE_DATETIME]

Coming up with the ISSUE_DATETIME_GMT was a little easier because I realized I could just replace the periods in ISSUE_TIME_GMT with colons and concatenate that to the end of ISSUE_DATE with a space in between.

CONVERT(DATETIME, CONVERT(CHAR(10), ISSUE_DATE) + ' ' + 
     REPLACE(ISSUE_TIME_GMT, '.', ':')) AS [ISSUE_DATETIME_GMT]

With those derived columns in mind we can create your datetime range variables. I based them on a reference date so my query would still work with my sample data beyond when I created it. You could set your reference date to current date (e.g. GETDATE() for SQL Server and sysdate for Oracle).

SELECT
           a.TICKET_ID
         , a.DEPARTURE_DATE
         , a.DEPARTURE_TIME
         , a.DEPARTURE_DATETIME
         , b.TICKET_ID
         , b.ISSUE_DATE
         , b.ISSUE_TIME_GMT
         , b.ISSUE_DATETIME_GMT
FROM       (
               SELECT
                    TICKET_ID
                  , DEPARTURE_DATE
                  , DEPARTURE_TIME
                  , CONVERT(DATETIME, CONVERT(CHAR(10), DEPARTURE_DATE) + ' ' + 
                         LEFT(DEPARTURE_TIME, 2) + ':' + 
                         RIGHT(DEPARTURE_TIME, 2)) AS [DEPARTURE_DATETIME]
               FROM TKT_DELIVERY
           ) a
INNER JOIN (
               SELECT
                    TICKET_ID
                  , ISSUE_DATE
                  , ISSUE_TIME_GMT
                  , CONVERT(DATETIME, CONVERT(CHAR(10), ISSUE_DATE) + ' ' + 
                         REPLACE(ISSUE_TIME_GMT, '.', ':')) AS [ISSUE_DATETIME_GMT]
               FROM TKT
           ) b ON a.TICKET_ID = b.TICKET_ID
WHERE      a.DEPARTURE_DATETIME BETWEEN @StartDepartureDateTime AND @EndDepartureDateTime
       AND b.ISSUE_DATETIME_GMT BETWEEN @StartIssueDateTime AND @EndIssueDateTime;

Here is the full demo of the code with sample data.

I used subqueries to derive and then use the datetime columns. You could derive these datetime columns in a number different ways. Your approach will depend on how much access you have to the database (or to someone that does).

  • Computed column (must have DBA access)
  • View (must have DBA access)
  • Temp table
  • Table variable

And there are probably more. Whatever you approach you choose it needs to be something you understand. That could mean going with something you already understand or taking the time and effort to learn something you don't currently understand. There advantages and disadvantages to each of them (e.g. access level, performance, simplicity, maintainability). You have to decide what works best for your situation.

I hope this helps you out.

Upvotes: 0

Related Questions