We help IT Professionals succeed at work.

Request a query to compare date columns

jl66
jl66 used Ask the Experts™
on
Have a table T with the columns/records
GRP        NAME    T1         T2
2             BB        7/1/11    7/3/11
2             SS        7/5/11    6/6/11
3             SS        7/1/11    7/3/11
3             CC        7/5/11    7/6/11
3             DD        7/4/11    7/6/11
4             SS        8/1/11    8/2/11
4             SS        8/3/11    8/3/11
....
Select the records in each group like 2, 3,... if Name =SS and T1 > T2 (here T2 comes from any other record in the same group).

Note: T1 column for the record compares to T2 which is not in the record but in the other record in the same group.  
If T1 in the record > T2 in the other record in the same group and Name = SS, the record should be selected.
 
For example,

2       SS        7/5/11    6/6/11   -- selected because its T1=7/5/11 > T2=7/3/11

T2 comes from another record: 2             BB        7/1/11    7/3/11

However, none of records are selected from grp =3.

Similarly, the following record should be selected
4       SS        8/3/11    8/3/11

In summary, the expected results should be
2       SS        7/5/11    6/6/11
4       SS        8/3/11    8/3/11

I asked the similar questions, but I did not provide with the good test data.  I will greatly appreciate any guru's advice/queries.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
nishant joshiTechnology Development Consultant

Commented:
select * from (select * from t where t1>t2)group by GRp
jl66Consultant

Author

Commented:
Thanks for the info. However, you compare T1 to T2 in the same record, which is not what is requested.
Top Expert 2016
Commented:
1 database 3 tables
table 1
<id><NAME><t1id><t2id>

Table 2
<t1id><Time1>

Table 3
<t2id><Time2>
pseudo code
while <id> NE NULL ( variable1 = table1.t1id.Time1, variable2 = table2.t2id.Time2
                                if (variable1 > variable2) then return (table1.Name, variable1, variable2)
  )

Is this somewhat how your data is arranged?
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
There are many ways to achieve that, and one is this:
select a.*
from (select * from T a where name = 'SS') a
join (select grp, min(T2) minT2 from T group by grp) b
on a.grp = b.grp and a.T1 != b.minT2

Open in new window

The ON condition could also be written as   ... and a.T1 > b.minT2   without changing the logic, because minT2 is always the oldest date for a group, and any other record not having the same date is more recent.
jl66Consultant

Author

Commented:
Thanks a lot for working on the query.
ve3ofa: a query or pl/sql procedure is needed, then consider how to show it.

Qlemo: It did not seem the query yields what are requested:
Directly running your query, here is what came out:
       GRP NAME T1        T2
---------- ---- --------- ---------
         4 SS   01-AUG-11 02-AUG-11
         2 SS   05-JUL-11 04-JUN-11
         3 SS   01-JUL-11 03-JUL-11
         4 SS   03-AUG-11 03-AUG-11

4 rows selected.

What should come out from the query is only two records:
2 SS   05-JUL-11 04-JUN-11  -- 1st record
4 SS   03-AUG-11 03-AUG-11 -- 2nd record

The 1st record is  by comparing T1=05-JUL-11 with the other T2 in the same group (NOT itself).   Found T1 > T2 = 7/3/11 in another record in the same group  ( 2             BB        7/1/11    7/3/11).
It is selected.

For group 3,  there is no record satisfying with this condition because you can't find any T2 in the group < T1 from the record with Name=SS (3             SS        7/1/11    7/3/11)

For group 4, the above 2nd record is selected in the same reason as 1st record

All in all, after running the query, only two records below should be selected:
 2 SS   05-JUL-11 04-JUN-11
4 SS   03-AUG-11 03-AUG-11

Is it possible?
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Sorry, you need to apply the change as stated in the post below the code, I have been mixing up T1 and T2 in the original code in my mind:
select a.*
from (select * from T a where name = 'SS') a
join (select grp, min(T2) minT2 from T group by grp) b
on a.grp = b.grp and a.T1 > b.minT2

Open in new window

jl66Consultant

Author

Commented:
Qlemo: Thanks a lot for the revised query which yields the requested result.
However checking the logic, it still could not yield the requested result in the other test data set. If I change T2 in 1st record from  03-JUL-11 to 09-JUL-11, that is,

       GRP NAME T1        T2
---------- ---- --------- ---------
         2 BB   01-JUL-11 09-JUL-11
 
There should be no records selected from group 2.
The desired result should be only 1:
4 SS   03-AUG-11 03-AUG-11

But your query still gets the same two records.

The way to get the desired records seems
1) Locate the record with Name=SS and find its T1called Tgr  in each group
2) Compare Tgr with T2 in the other records in the same group (NOT itself). If Tgr > any one of T2s in that group. The record with this Tgr is selected.
3) Check each group.

Is it possible to achieve that?
 

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
I'm sorry for you, but I get a single record only with your changed data set, as expected. Maybe you did not commit your change, so the query was running on a (unchanged) read snapshot.
select * from your_table t where t.name='SS' where t1 >= (select max(tt.t2) from your_table tt where tt.grp=t.grp)
jl66Consultant

Author

Commented:
I greatly appreciate everyone's tips, esp. from Qlemo and aiklamha. The issue was resolved. I'll reward all points to you.

Qlemo: after committed, it still showed 2 records. However the way you approached the issue gave me a big help.

aiklamha: yours is correct for this data set. However if changing t2 in the selected record in group 4 from
4 SS   03-AUG-11 03-AUG-11
to
4 SS   03-AUG-11 04-AUG-11

Your query got nothing, but the desired result is still
4 SS   03-AUG-11 04-AUG-11

I modified your query a bit
select * from t
where t.name='SS' and
            t.t1 > (select max(tt.t2) from t tt where tt.grp=t.grp and t.rowid <> tt.rowid);

to remove comparison with itself. That yields what are really desired. Sorry for unclear stating my words.

Thank all of you very very much.
jl66Consultant

Author

Commented:
Thanks a lot for everyone's inputs. Very helpful.
Most Valuable Expert 2011
Top Expert 2012

Commented:
same idea as previous question...




SELECT groupid,
       name,
       t1,
       t2
  FROM (SELECT groupid,
               name,
               t1,
               t2,              
               MIN(t2) OVER (PARTITION BY groupid) mt2
          FROM yourtable)
 WHERE name = 'SS' AND t1 > mt2
Most Valuable Expert 2011
Top Expert 2012

Commented:
oh too slow by seconds, too bad because it's not necessary to incur the extra io of querying the table again
jl66Consultant

Author

Commented:
sdstuber:
Sorry I already rewarded the points. From interest, here is the revised test data:

select * from t;

       GRP NAME T1        T2
---------- ---- --------- ---------
         2 BB   01-JUL-11 09-JUL-11
         2 SS   05-JUL-11 04-JUN-11
         3 SS   01-JUL-11 03-JUL-11
         3 CC   05-JUL-11 06-JUL-11
         4 SS   01-AUG-11 02-AUG-11
         4 SS   03-AUG-11 04-AUG-11
         3 DD   04-JUL-11 06-JUL-11

7 rows selected.

I ran yours:
       GRP NAME T1        T2
---------- ---- --------- ---------
         2 SS   05-JUL-11 04-JUN-11
         4 SS   03-AUG-11 04-AUG-11

in which the 1st one should not be here.
Most Valuable Expert 2011
Top Expert 2012

Commented:
why should the first one not be there?

T1 > T2

I have been using sub-query method for analytic functions in oracle since forever.. Today I tried sdstuber method and I have to say this method is not only efficient .. it is clean and easy too...

Thanks sdstuber.. wish I could give you points for this..
Most Valuable Expert 2011
Top Expert 2012

Commented:
you can have the question reopened if you want