|
View previous topic :: View next topic
|
| Author |
Message |
vickey_dw
New User
Joined: 10 Jun 2015
Posts: 51
Location: India
|
|
|
|
Hi All,
Just to avoid confusion with my earlier post i am re-posting my requirement with new thread with sample input and outputs.
Table A has Registration_id and Client_No.
Table B has Last_Login_Email,Last_Login_Mobile and Last_Login_OFX timestamp of each Registration_id.
My requirement is below :-
Fetch all Registration_id's which are tied to multiple Client_no and out of these skip Registration_id with maximum time-stamp and fetch others.
Example:-
Table A
| Code: |
Registration_id Client_No
AAA 111
BBB 111
CCC 111
DDD 444
EEE 555
FFF 666
GGG 666
|
So in above table AAA,BBB,CCC are tied to same Client_no which is 111.Also FFF,GGG tied to same CLient_no which is 666.
So these Registration_id (AAA,BBB,CCC) and (FFF,GGG) qualified for first part of my condition.
Now out of 1st set of Registration_id which are tied to same Client_no i want to skip the Registration_id with maximum time-stamp and fetch other.And same for 2nd set and so on.
Example: (for typing convinicence i have used Date instead of timestamp in below example)
Table B
| Code: |
Table B DD/MM/YYYY DD/MM/YYYY DD/MM/YYYY
Registration_id Last_Login_Email Last_Login_Mobile Last_Login_OFX
AAA 01/12/2017 02/12/2017 01/11/2017
BBB 01/01/2018 02/01/2018 03/01/2018
CCC 01/11/2017 02/11/2017 03/11/2017
DDD 01/01/2018 02/01/2018 03/01/2018
EEE 21/01/2018 22/01/2018 23/01/2018
FFF 12/01/2018 13/01/2018 14/01/2018
GGG 29/01/2018 28/01/2018 31/01/2018
|
Note:- In above table B we dont have to do anything with DDD and EEE as they are not qualified under 1st part of condition.I have given in above table just for sake of completeness.
Lets take 1st set here which is AAA,BBB,CCC
| Code: |
From Table B
Maximum-Timestamp out of Last_Login_Email,Last_Login_Mobile
andLast_Login_OFX
AAA 02/12/2017
BBB 03/01/2018
CCC 03/11/2017
|
Above we can see maximum timestamp is for BBB(out of AAA,BBB,CCC) so i want to skip BBB here and fetch AAA and CCC.
Same goes to other set which is FFF,GGG
| Code: |
From Table B
Maximum-Timestamp out of Last_Login_Email,Last_Login_Mobile and
Last_Login_OFX
FFF 14/01/2018
GGG 31/01/2018
|
Above we can see maximum timestamp is for GGG so i need to skip GGG and fetch FFF.
So my overall logic should fetch AAA,CCC and FFF.
Hope i am clear with my requiremet. Please reply incase of any doubts and sorry for such a lengthy post. |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
See if this logic works.
| Code: |
select temp3.Registration_id,temp3.Client_No
from tablea temp3
where temp3.Registration_id NOT IN (
select temp1.Registration_id from
(select temp.Registration_id,
temp.Client_No,
greatest((b.email_dt),(b.mobile_dt),(b.ofx_dt)) as max_dt
from
(select a.Registration_id,
a.Client_No
from tablea a
where a.Client_No IN (
select b.Client_No
from tablea b
group by b.Client_No
having count(b.Client_No ) >1)) as temp,
tableb b
where
temp.Registration_id = b.Registration_id)) as temp1) as temp2) |
|
|
| Back to top |
|
|
vickey_dw
New User
Joined: 10 Jun 2015
Posts: 51
Location: India
|
|
|
|
Great Thanks Rohit!!!
I dont have access of Mainframe till Monday,i will check on Monday and will tell you. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
