Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Logic to skip Maximum time-stamp and fetch other.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vickey_dw

New User


Joined: 10 Jun 2015
Posts: 50
Location: India

PostPosted: Thu Feb 01, 2018 11:15 pm    Post subject: Logic to skip Maximum time-stamp and fetch other.
Reply with quote

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
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1883
Location: NY,USA

PostPosted: Fri Feb 02, 2018 11:24 pm    Post subject:
Reply with quote

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
View user's profile Send private message
vickey_dw

New User


Joined: 10 Jun 2015
Posts: 50
Location: India

PostPosted: Sat Feb 03, 2018 1:42 pm    Post subject:
Reply with quote

Great Thanks Rohit!!!

I dont have access of Mainframe till Monday,i will check on Monday and will tell you.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How syntax check at Bind time is diff... Poha Eater DB2 5 Wed Mar 21, 2018 9:16 pm
No new posts Merging records, but not all the time prino DFSORT/ICETOOL 17 Sat Feb 17, 2018 4:20 pm
No new posts Need to identify top 10 costly or tim... ashek15 DB2 4 Wed Feb 14, 2018 3:20 am
No new posts Query to skip Max time-stamp and fetc... vickey_dw DB2 8 Sun Jan 28, 2018 1:09 am
No new posts Skip records depends on count lakshmiibmmainframes DFSORT/ICETOOL 5 Sun Dec 24, 2017 9:51 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us