Oracle - Format and structure data / query

Question

It's my first week challenging with SQL (Oracle). I've never really used SQL before. Only tries to understand the queries. Currently I am trying to outsource a query, which I have transformed so far in Excel (power query), via SQL (Oracle) on the server side to get corresponding performance improvements. So that means that I have implemented steps in power queries so far, trying to implement them using an SQL statement. I've already asked a few questions here and also received lots of help and good ideas from you. I think it makes the most sense to deal with everything within one question.The structure of the original table or database looks like this:

Name = Value example

• JDDate = 118001
• List item
• START = 63000 (for 06:30:00)
• END = 240000 (for 24:00:00)
• NR = 34000001
• Terminal = MM01X11
• Dep = XX01
• DOC = 1000800001
• Typ = 1
• Key1 = 99000000

• Key2 = 99000000

• At first I need all values with "Typ" = 1 and JDDate =>118000

WHERE "JDDate" >118000 AND "Typ" = 1

• Then I need the difference/ timestep between START and END based in the right JDDate/ Format. Unfortunately, here are some duplicates, based on JDDate, START; END; Terminal.:

.

cte (staff_nr, start_datetime, end_datetime) as (
  select distinct ltrim(substr("NR", -6), '0'),
      date '1900-01-01'
        + floor("JDDate" / 1000) * interval '1' year
        + (mod("JDDate", 1000) -1) * interval '1' day
        + floor("START" / 10000) * interval '1' hour
        + floor(mod("START", 10000) / 100) * interval '1' minute
        + mod("START", 100) * interval '1' second,
      date '1900-01-01'
        + floor("JDDate" / 1000) * interval '1' year
        + (mod("JDDate", 1000) -1) * interval '1' day
        + case when "END" = 240000 then interval '1' day
               else floor("END" / 10000) * interval '1' hour
                  + floor(mod("END", 10000) / 100) * interval '1' minute
                  + mod("END", 100) * interval '1' second
          end
FROM "POOL0101"."9909KK"
WHERE   "JDDate" >118000
    AND "Typ" = 1

)
select staff_nr,
  to_char(start_datetime, 'YYYY-MM-DD HH24:MI:SS') as end_datetime,
  to_char(end_datetime, 'YYYY-MM-DD HH24:MI:SS') as end_datetime,
  end_datetime - start_datetime as diff_in_days
from cte

• And at least I have some obsolete data/ duplicates at Key1 and Key2. So Key1 containts everytime a number. Key2 containts either a 0 or a number, which is also a number in Key1. If in Key1 and Key2 is the same number, both rows should be deletet.

Could someone help me, to get all this elements in on SQL?

Is there a way to show you an exemplary database?

Best regards Joshua

Answer 1

Is there a way to show you an exemplary database?

You can use dbfiddle.uk for creating test tables and queries. Suppose you have the following test table (called ORIGINAL) and data:

Table

create table original (
  jddate number
, starttime timestamp
, endtime timestamp
, nr number
, terminal varchar2( 100 )
, dep varchar2( 100 )
, doc number
, typ number
, key1 number
, key2 number 
) ;

INSERTs

insert into original
select 118001
, trunc( sysdate ) + ( 6/24 ) + ( 30/(24*60 ) )
, trunc( sysdate ) + (86399/86400)
, 34000001, 'MM01X11', 'XX01', 1000800001, 1, 99000000, 99000000  from dual union all 
-- duplicate
select 118001
, trunc( sysdate ) + ( 6/24 ) + ( 30/(24*60 ) )
, trunc( sysdate ) + (86399/86400)
, 34000001, 'MM01X11', 'XX01', 1000800001, 1, 99000000, 99111111  from dual union all 
select 118001
, trunc( sysdate ) + ( 6/24 ) + ( 30/(24*60 ) )
, trunc( sysdate ) + (86399/86400)
, 34000001, 'MM01X11', 'XX01', 1000800001, 1, 99000000, 99111111  from dual union all 
--
select 118001
, trunc( sysdate ) + ( 6/24 ) + ( 30/(24*60 ) )
, trunc( sysdate ) + (86399/86400)
, 34000001, 'MM01X11', 'XX01', 1000800001, 2, 99000000, 99000000  from dual union all 
select 118001
, trunc( sysdate ) + ( 6/24 ) + ( 30/(24*60 ) )
, trunc( sysdate ) + (86399/86400)
, 34000001, 'MM01X11', 'XX01', 1000800001, 3, 99000000, 99000000  from dual union all 
select 118001
, trunc( sysdate ) + ( 6/24 ) + ( 30/(24*60 ) )
, trunc( sysdate ) + (86399/86400)
, 34000001, 'MM01X11', 'XX01', 1000800001, 4, 99000000, 99000000  from dual ;

SELECT

-- select * from original;    
JDDATE  STARTTIME                     ENDTIME                       NR        TERMINAL  DEP   DOC         TYP  KEY1      KEY2      
    118001  15-DEC-18 06.30.00.000000000  15-DEC-18 23.59.59.000000000  34000001  MM01X11   XX01  1000800001  1    99000000  99000000  
    118001  15-DEC-18 06.30.00.000000000  15-DEC-18 23.59.59.000000000  34000001  MM01X11   XX01  1000800001  1    99000000  99111111  
    118001  15-DEC-18 06.30.00.000000000  15-DEC-18 23.59.59.000000000  34000001  MM01X11   XX01  1000800001  1    99000000  99111111  
    118001  15-DEC-18 06.30.00.000000000  15-DEC-18 23.59.59.000000000  34000001  MM01X11   XX01  1000800001  2    99000000  99000000  
    118001  15-DEC-18 06.30.00.000000000  15-DEC-18 23.59.59.000000000  34000001  MM01X11   XX01  1000800001  3    99000000  99000000  
    118001  15-DEC-18 06.30.00.000000000  15-DEC-18 23.59.59.000000000  34000001  MM01X11   XX01  1000800001  4    99000000  99000000  

Requirements

{1} At first I need all values with "Typ" = 1 and JDDate =>118000

{2} Then I need the difference/ timestep between START and END based in the right JDDate/ Format. Unfortunately, here are some duplicates, based on JDDate, START; END; Terminal.:

{3} And at least I have some obsolete data/ duplicates at Key1 and Key2. So Key1 containts everytime a number. Key2 containts either a 0 or a number, which is also a number in Key1. If in Key1 and Key2 is the same number, both rows should be deletet.

Example query - as a starting point (the WHERE clause will need some more work ...)

select distinct                    -- {2} remove duplicates
  jddate
, endtime - starttime as interval_ -- {2} 
, nr
, terminal
, dep
, doc
, typ
, key1
, key2
from original 
where typ = 1 and jddate > 118000  -- {1}
  and key1 <> key2                 -- {3}
; 

-- result
JDDATE  INTERVAL_            NR        TERMINAL  DEP   DOC         TYP  KEY1      KEY2      
118001  +00 17:29:59.000000  34000001  MM01X11   XX01  1000800001  1    99000000  99111111 

For converting the values in JDDATE column to Oracle DATEs, you could maybe create a small function using the code of the answer you got earlier. (You don't have to, but it would remove some "clutter" from your SELECTs) eg

--  https://stackoverflow.com/questions/53743601/sql-julien-date-cyyddd-to-date
/*
select date '1900-01-01'
  + floor(118001 / 1000) * interval '1' year
  + (mod(118001, 1000) - 1) * interval '1' day
from dual;
*/

-- this is far from perfect, needs range checking, exception handling etc
create or replace function cyyddd_to_date ( cyyddd number ) return date
is
begin
  return 
    date '1900-01-01' 
  + floor( cyyddd / 1000 ) * interval '1' year 
  + ( mod( cyyddd, 1000 ) - 1 ) * interval '1' day
  ;
end;
/

-- quick test
select 
  cyyddd_to_date( 118001 ) date_
, to_char( cyyddd_to_date( 118001 ), 'YYYY-MM-DD' ) datetime_
from dual; 

-- result
DATE_      DATETIME_   
01-JAN-18  2018-01-01

Final query

select distinct                    -- {2} remove duplicates
  to_char( cyyddd_to_date( jddate ), 'YYYY-MM-DD' ) date_
, endtime - starttime  interval_   -- {2} 
, nr
, terminal
, dep
, doc
, typ
, key1
, key2
from original 
where typ = 1 and jddate > 118000  -- {1}
  and key1 <> key2                 -- {3}
; 

-- result
DATE_       INTERVAL_            NR        TERMINAL  DEP   DOC         TYP  KEY1      KEY2      
2018-01-01  +00 17:29:59.000000  34000001  MM01X11   XX01  1000800001  1    99000000  99111111 

Tested with Oracle 12c and Oracle 11g, dbfiddle here.