| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Date Comparison Blues!!!
Hello Everyone
Consider the following record set:
select rownum,a.icons,
to_char(a.admdatetime,'MM-DD-YYYY HH24:MI:SS') ADM,
to_char(a.disdate,'MM-DD-YYYY HH24:MI:SS') DIS
from inpatient a
a.admdatetime;
ROWNUM ICONS ADM DIS
--------- --------- ------------------- -------------------
1 1 07-06-1998 01:19:00 07-09-1998 00:00:00 2 4 01-09-1999 17:15:00 01-12-1999 00:00:00 3 4 02-22-1999 17:45:00 03-05-1999 00:00:00 4 5 12-19-1998 22:30:00 12-22-1998 00:00:00 5 7 06-12-1998 09:40:00 06-18-1998 00:00:00 6 9 09-23-1998 07:30:00 09-24-1998 00:00:00 7 10 08-10-1999 07:47:00 08-11-1999 00:00:00 8 10 11-08-1999 14:40:00 11-19-1999 00:00:00 9 11 12-12-1997 06:28:00 12-13-1997 00:00:00 10 11 01-28-1998 08:19:00 01-29-1998 00:00:00 11 14 05-20-1998 06:13:00 05-21-1998 00:00:00 12 17 10-19-1997 13:28:00 10-25-1997 00:00:00 13 24 11-19-1997 22:07:00 11-20-1997 00:00:00 14 29 08-06-1999 07:51:00 08-07-1999 00:00:00 15 29 09-09-1999 08:48:00 09-10-1999 00:00:00 16 30 02-17-1998 07:05:00 02-18-1998 00:00:00 17 34 05-03-1998 14:28:00 05-08-1998 00:00:00 18 45 01-16-1998 06:21:00 01-17-1998 00:00:00 19 45 02-23-1998 10:21:00 03-03-1998 00:00:00 20 45 04-14-1999 18:14:00 04-21-1999 00:00:00 21 45 04-22-1999 03:36:00 04-25-1999 00:00:00 22 46 05-06-1998 22:53:00 05-15-1998 00:00:00 23 46 05-21-1998 09:50:00 05-30-1998 00:00:00 24 46 04-26-2000 20:52:00 05-01-2000 00:00:00 25 47 04-12-2000 17:17:00 04-20-2000 00:00:00
25 rows selected.
Here is the problem:
If the next ICONS number in the sequence is the same as the previous record AND the difference between the DIS of the first line and the ADM of the second line is < 1 day, return only the first row of the sequence. Otherwise show that row. For EXAMPLE: Consider Row # 18, 19, 20, and 21. The ICONS numbers are the same in all of them but only in ROW 20 (DIS = 04-21-1999 00:00:00) and ROW 21 ( ADM = 04-22-1999 03:36:00) is the difference < 1 day. Therefore, I would want to see line 18,19,20 only. So based on the above, I would want to see the following: (ProjectedOutput)
ROWNUM ICONS ADM DIS
--------- --------- ------------------- -------------------
1 1 07-06-1998 01:19:00 07-09-1998 00:00:00 2 4 01-09-1999 17:15:00 01-12-1999 00:00:00 3 4 02-22-1999 17:45:00 03-05-1999 00:00:00 4 5 12-19-1998 22:30:00 12-22-1998 00:00:00 5 7 06-12-1998 09:40:00 06-18-1998 00:00:00 6 9 09-23-1998 07:30:00 09-24-1998 00:00:00 7 10 08-10-1999 07:47:00 08-11-1999 00:00:00 8 10 11-08-1999 14:40:00 11-19-1999 00:00:00 9 11 12-12-1997 06:28:00 12-13-1997 00:00:00 10 11 01-28-1998 08:19:00 01-29-1998 00:00:00 11 14 05-20-1998 06:13:00 05-21-1998 00:00:00 12 17 10-19-1997 13:28:00 10-25-1997 00:00:00 13 24 11-19-1997 22:07:00 11-20-1997 00:00:00 14 29 08-06-1999 07:51:00 08-07-1999 00:00:00 15 29 09-09-1999 08:48:00 09-10-1999 00:00:00 16 30 02-17-1998 07:05:00 02-18-1998 00:00:00 17 34 05-03-1998 14:28:00 05-08-1998 00:00:00 18 45 01-16-1998 06:21:00 01-17-1998 00:00:00 19 45 02-23-1998 10:21:00 03-03-1998 00:00:00 20 45 04-14-1999 18:14:00 04-21-1999 00:00:00 ***** ROW 21 should be gone!!!***** 21 45 04-22-1999 03:36:00 04-25-1999 00:00:00 ***** ROW 21 should be gone!!!***** 22 46 05-06-1998 22:53:00 05-15-1998 00:00:00 23 46 05-21-1998 09:50:00 05-30-1998 00:00:00 24 46 04-26-2000 20:52:00 05-01-2000 00:00:00 25 47 04-12-2000 17:17:00 04-20-2000 00:00:00
25 rows selected.
I have no idea how to do this and if someone could help me, I would really appreciate it. I do not know if SQL is the best or a cursor or what. This is huge for me and if you could help me out, I would be forever indebted to you!
The following code was supplied to me by someone on this list and it returns the following:
1 select t1.icons, 2 t1.admdatetime, 3 t1.disdate, 4 (t1.disdate-t2.admdatetime) diff 5 from 6 (select rownum r1,icons,admdatetime,disdate from inpatient) t1, 7 (select rownum r1,icons,admdatetime,disdate from inpatient ) t2 8 where t1.icons = t2.icons 9 --and t1.r1 = t2.r1 - 1 10 and (t1.disdate-t2.admdatetime) < 111* and t1.icons <=47
--------- --------- --------- ---------
4 09-JAN-99 12-JAN-99 -41.73958
9 23-SEP-98 24-SEP-98 .6875
10 10-AUG-99 11-AUG-99 .67569444
10 10-AUG-99 11-AUG-99 -89.61111
11 28-JAN-98 29-JAN-98 .65347222
11 12-DEC-97 13-DEC-97 -46.34653
11 12-DEC-97 13-DEC-97 .73055556
14 20-MAY-98 21-MAY-98 .74097222
24 19-NOV-97 20-NOV-97 .07847222
29 06-AUG-99 07-AUG-99 -33.36667
29 09-SEP-99 10-SEP-99 .63333333
29 06-AUG-99 07-AUG-99 .67291667
30 17-FEB-98 18-FEB-98 .70486111
45 16-JAN-98 17-JAN-98 -460.15
45 14-APR-99 21-APR-99 -1.15
45 23-FEB-98 03-MAR-98 -415.15
45 16-JAN-98 17-JAN-98 -452.7597
45 23-FEB-98 03-MAR-98 -407.7597
45 16-JAN-98 17-JAN-98 .73541667
45 16-JAN-98 17-JAN-98 -37.43125
46 21-MAY-98 30-MAY-98 -697.8694
46 06-MAY-98 15-MAY-98 -712.8694
46 06-MAY-98 15-MAY-98 -6.409722
23 rows selected.
This output is not correct but it may be close. Any suggestions?
Thank you so much.
Peter Hazelton
 |
 |