Home » SQL & PL/SQL » SQL & PL/SQL » cross linking in same table (oracle 10g, 10.0.0.2 , windows XP)
cross linking in same table [message #280019] Mon, 12 November 2007 05:52 Go to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
hi all,

I have a problem like this this is the data in my table


#	Scheduled  Link    Linked     Code     Observed
        Time       Status  With                Time
1	15:53				A	15:59:26
2	16:09				B	16:00:00
3	16:24				C	16:12:46
4	16:39				D	16:26:18
5	16:54				E	16:50:20
6	17:09				F	16:52:43
7	17:24				G	17:07:52
8	17:39				H	17:29:50
9	17:54				I	17:45:06
10	18:09				J	17:56:29



and i want linking of this table on the basis of these facts

Criteria Trip is:	                Status	 Scheduled 
                                                 Time is:

<2.5 mins early to =5 minutes late	On time	 Linked
>5 mins late to =15 minutes late	Late	 Linked
=2.5 mins early and =8 mins early	Early	 Linked
>15 minutes late	                Non      Not
            	                       Arrival   Linked
>8 minutes early	                Non      Not
            	                       Arrival   Linked
Missing  buses	                        Non      Not
            	                       Arrival   Linked



and the final outcome should look like this

#    Scheduled  Link 	Linked 	     Code	observed
        Time    Status  With                     time

1	15:53	Late	  A		A	15:59:26
2	16:09	On Time	  C		B	16:00:00
3	16:24	On Time	  D		C	16:12:46
4	16:39	Late	  E		D	16:26:18
5	16:54	On Time	  F		E	16:50:20
6	17:09	On Time	  G		F	16:52:43
7	17:24	Late	  H		G	17:07:52
8	17:39	Late	  I		H	17:29:50
9	17:54	On Time	  J 		I	17:45:06
10	18:09	Non                     J       17:56:29
                arrival				



please help me i am bit confused how to catch up with this
and how to compare time with other time in a single table

thanks in advance

[Updated on: Mon, 12 November 2007 06:35] by Moderator

Report message to a moderator

Re: cross linking in same table [message #280047 is a reply to message #280019] Mon, 12 November 2007 06:37 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Hi, I've added code tags to make it more readable. But check your sample data, criteria and output. They do not match. The trip with code 'B' is 9 minutes early. According to your criteria that is "Non arrival". Your sample output classifies this as "on time".

MHE
Re: cross linking in same table [message #280257 is a reply to message #280019] Tue, 13 November 2007 00:20 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
actually first we have to calculate the ontime buses then
late and then early and remaining termed as non arrival

and also these points to be considered while doing this

1) Look for buses that are ‘on-time’, i.e. the observed bus that is nearest the scheduled time. For a bus to be counted as ‘on-time’ it must be less than 2.5 minutes early and up to 5 minutes late.

2) If more than one bus fits these criteria, always use the bus that is after the scheduled time rather than before.
3) If two or more buses are observed between the scheduled time and ≤ 5 minutes late, use the bus that has the lowest time difference to the schedule.

4) Where no buses are observed between the scheduled time and ≤ 5 minutes late but there is more than one bus < 2.5 minutes early use the bus that has the lowest time difference to the schedule.

5) If no “on time” bus was found look for a bus considered as being late. Late is defined as > 5 minutes to 15 Minutes late.

6)_ If more than one bus fits the critera in 5), use the bus that has the lowest time difference to the schedule.

7) If no “on time” or “late” bus was found look for a bus considered as being early. Early is defined as 8 minutes to 2.5 Minutes early.

Cool If more than one bus fits the critera in 7) use the bus that has the lowest time difference to the schedule.

9) Any other scheduled bus that cannot be linked using the rules defined in 1 - 9 above is marked as a non arrival.

10) If two or more buses have an identical time (hh:mm:ss are exactly the same) then arbitrarily pick one to be counted for the linking status in question; from a customer’s point of view the choice is irrelevant.

11) One can imagine all linkings as represented by lines between scheduled and observed buses. Using this method an important rule can be described: links must not cross one another.


please suggest some thing how to proceed .
can we do this using a query or we need to develop a pl/sql procedure

thanks

Re: cross linking in same table [message #280277 is a reply to message #280257] Tue, 13 November 2007 01:05 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
http://www.orafaq.com/forum/fa/450/0/ Ok, I missed some points here. How could you think that we could guess all that?

MHE
Re: cross linking in same table [message #280291 is a reply to message #280277] Tue, 13 November 2007 01:48 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The closest output I got so far is:
SCH_T LINK_STATUS L B OBS_T
----- ----------- - - -----
15:53 Late        B A 16:00
16:09 On Time     C B 16:12
16:24 On Time     D C 16:26
16:39 Late        E D 16:50
16:54 On Time     F E 16:52
17:09 On Time     G F 17:07
17:24 Late        H G 17:29
17:39 Late        I H 17:45
17:54 On Time     J I 17:56
18:09 Non Arrival   J 17:45

10 rows selected.

How I got there?
SELECT sch_time
--     , secs
     , CASE 
         WHEN secs > -150 AND secs <= 300
         THEN 'On Time'
         WHEN secs > 300  AND secs <= 900
         THEN 'Late'
         WHEN secs < -150 AND secs >= -480 
         THEN 'Early'
         ELSE 'Non Arrival'
       END link_status
     , CASE WHEN secs BETWEEN -480 AND 900 THEN linked_with ELSE NULL END linked_with
     , bus_code
     , obs_time
FROM ( SELECT TO_CHAR(mfm.sch_time, 'HH24:MI') sch_time,
              EXTRACT( MINUTE FROM (mfl.obs_time-mfm.sch_time) DAY TO SECOND )*60
            + EXTRACT( SECOND FROM (mfl.obs_time-mfm.sch_time) DAY TO SECOND ) secs
            , mfl.bus_code                     linked_with
            , mfm.bus_code                     
            , TO_CHAR(mfl.obs_time, 'HH24:MI') obs_time
       FROM   mhe_foo mfm -- master table
          ,   mhe_foo mfl -- lookup table
       WHERE  ABS(mfm.sch_time-mfl.obs_time) = ( SELECT MIN(ABS(mf2.obs_time-mfm.sch_time))
                                                 FROM   mhe_foo mf2
                                                 WHERE  mf2.bus_code != mfm.bus_code
                                               )               
     )
/

MHE

PS: the creation script for others:
CREATE TABLE mhe_foo( trip_id  NUMBER
                    , sch_time DATE
                    , bus_code VARCHAR2(1)
                    , obs_time DATE
                    )
/

INSERT INTO mhe_foo VALUES ( 1, TO_DATE('15:53', 'HH24:MI'), 'A', TO_DATE('15:59:26', 'HH24:MI:SS'));
INSERT INTO mhe_foo VALUES ( 2, TO_DATE('16:09', 'HH24:MI'), 'B', TO_DATE('16:00:00', 'HH24:MI:SS'));
INSERT INTO mhe_foo VALUES ( 3, TO_DATE('16:24', 'HH24:MI'), 'C', TO_DATE('16:12:46', 'HH24:MI:SS'));
INSERT INTO mhe_foo VALUES ( 4, TO_DATE('16:39', 'HH24:MI'), 'D', TO_DATE('16:26:18', 'HH24:MI:SS'));
INSERT INTO mhe_foo VALUES ( 5, TO_DATE('16:54', 'HH24:MI'), 'E', TO_DATE('16:50:20', 'HH24:MI:SS'));
INSERT INTO mhe_foo VALUES ( 6, TO_DATE('17:09', 'HH24:MI'), 'F', TO_DATE('16:52:43', 'HH24:MI:SS'));
INSERT INTO mhe_foo VALUES ( 7, TO_DATE('17:24', 'HH24:MI'), 'G', TO_DATE('17:07:52', 'HH24:MI:SS'));
INSERT INTO mhe_foo VALUES ( 8, TO_DATE('17:39', 'HH24:MI'), 'H', TO_DATE('17:29:50', 'HH24:MI:SS'));
INSERT INTO mhe_foo VALUES ( 9, TO_DATE('17:54', 'HH24:MI'), 'I', TO_DATE('17:45:06', 'HH24:MI:SS'));
INSERT INTO mhe_foo VALUES (10, TO_DATE('18:09', 'HH24:MI'), 'J', TO_DATE('17:56:29', 'HH24:MI:SS'));

COMMIT
/
PROMPT Sample Data:
SELECT TO_CHAR(sch_time, 'HH24:MI') sch_time
     , NULL                         link_status
     , NULL                         linked_with
     , bus_code
     , TO_CHAR(obs_time, 'HH24:MI') obs_time
FROM   mhe_foo mf
/
Re: cross linking in same table [message #280462 is a reply to message #280291] Tue, 13 November 2007 13:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> DESC buses
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TRIP_ID                                            NUMBER
 SCH_TIME                                           DATE
 BUS_CODE                                           VARCHAR2(1)
 OBS_TIME                                           DATE

SCOTT@orcl_11g> COLUMN trip_id	FORMAT 99 HEADING #|
SCOTT@orcl_11g> COLUMN sch_time FORMAT A9 HEADING Scheduled|Time
SCOTT@orcl_11g> COLUMN bus_code FORMAT A4 HEADING Code|
SCOTT@orcl_11g> COLUMN obs_time FORMAT A9 HEADING Observed|Time
SCOTT@orcl_11g> SELECT trip_id,
  2  	    TO_CHAR (sch_time, 'HH24:MI') sch_time,
  3  	    bus_code,
  4  	    TO_CHAR (obs_time, 'HH24:MI:SS') obs_time
  5  FROM   buses
  6  /

  # Scheduled Code Observed
    Time           Time
--- --------- ---- ---------
  1 15:53     A    15:59:26
  2 16:09     B    16:00:00
  3 16:24     C    16:12:46
  4 16:39     D    16:26:18
  5 16:54     E    16:50:20
  6 17:09     F    16:52:43
  7 17:24     G    17:07:52
  8 17:39     H    17:29:50
  9 17:54     I    17:45:06
 10 18:09     J    17:56:29

10 rows selected.

SCOTT@orcl_11g> COLUMN link_status FORMAT A7 HEADING Link|Status WORD_WRAPPED
SCOTT@orcl_11g> COLUMN linked_with FORMAT A6 HEADING Linked|With
SCOTT@orcl_11g> SELECT trip_id, TO_CHAR (sch_time, 'HH24:MI') sch_time, link_status,
  2  	    DECODE (link_status, 'Non arrival', NULL, linked_with) linked_with,
  3  	    bus_code, TO_CHAR (obs_time, 'HH24:MI:SS') obs_time
  4  FROM   (SELECT trip_id, sch_time, linked_with, bus_code, obs_time,
  5  		    CASE
  6  		      WHEN minutes >  -2.5 AND minutes <=  5   THEN 'On Time'
  7  		      WHEN minutes >   5   AND minutes <= 15   THEN 'Late'
  8  		      WHEN minutes >= -8   AND minutes <= -2.5 THEN 'Early'
  9  		      ELSE 'Non arrival'
 10  		    END link_status,
 11  		    ROW_NUMBER () OVER
 12  		      (PARTITION BY trip_id
 13  		       ORDER BY
 14  			 CASE
 15  			   WHEN minutes >=  0	AND minutes <=	5   THEN 1
 16  			   WHEN minutes >  -2.5 AND minutes <	0   THEN 2
 17  			   WHEN minutes >   5	AND minutes <= 15   THEN 3
 18  			   WHEN minutes >= -8	AND minutes <= -2.5 THEN 4
 19  			   ELSE 5
 20  			 END, ABS (minutes), ROWNUM) link_priority
 21  	     FROM   (SELECT b1.trip_id, b1.sch_time, b2.bus_code linked_with, b1.bus_code, b1.obs_time,
 22  			    (b2.obs_time - b1.sch_time) * 24 * 60 minutes
 23  		     FROM   buses b1, buses b2))
 24  WHERE  link_priority = 1
 25  /

  # Scheduled Link    Linked Code Observed
    Time      Status  With        Time
--- --------- ------- ------ ---- ---------
  1 15:53     Late    A      A    15:59:26
  2 16:09     On Time C      B    16:00:00
  3 16:24     On Time D      C    16:12:46
  4 16:39     Late    E      D    16:26:18
  5 16:54     On Time F      E    16:50:20
  6 17:09     On Time G      F    16:52:43
  7 17:24     Late    H      G    17:07:52
  8 17:39     Late    I      H    17:29:50
  9 17:54     On Time J      I    17:45:06
 10 18:09     Non            J    17:56:29
              arrival


10 rows selected.

SCOTT@orcl_11g>  

Re: cross linking in same table [message #280886 is a reply to message #280462] Thu, 15 November 2007 01:52 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Excellent!

MHE
Previous Topic: PLS-00201: identifier 'procedure' must be declared
Next Topic: how to get this data in a one line of output
Goto Forum:
  


Current Time: Fri Dec 09 05:47:54 CST 2016

Total time taken to generate the page: 0.11203 seconds