Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Display the emp of eath claims where the date < 10 YRs

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

New User


Joined: 18 Apr 2005
Posts: 67

PostPosted: Sat Nov 18, 2006 3:59 pm    Post subject: Display the emp of eath claims where the date < 10 YRs
Reply with quote

I want to display the emp names of death claims where the date of death is within 10 years of the policy start date. Details:


crD-tnc,(Date of Death) column belongs r_deth table

Crd_PLCY_start ,(Policystart Date)column belongs r-plcy table
emp name belongs to r_edetails table
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Sat Nov 18, 2006 8:41 pm    Post subject:
Reply with quote

Hey Rambabu,

Please tell that how we relate r_edetails ,r_deth ,r-plcy table. means what 's the common column between these table.

Hope u understood what I m asking means the relationship between this table like the foreign key ...
Back to top
View user's profile Send private message
rambabu
Currently Banned

New User


Joined: 18 Apr 2005
Posts: 67

PostPosted: Sun Nov 19, 2006 6:14 pm    Post subject: Re: Display the emp of eath claims where the date < 10 Y
Reply with quote

claim id is cmmun field u will give the query
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Sun Nov 19, 2006 7:58 pm    Post subject:
Reply with quote

Hi there,


Code:
Select A.crD-tnc,
          B.Crd_PLCY_start
          C.emp_name

From r_deth A,r-plcy B,r_edetails C
where A.claim_id = B.claim_id
    and B.claim_id = C.claim_id
    and Year ( date(A.crD-tnc) - Date(B.Crd_PLCY_start ))<= 10;

Hope It will helpful
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Mon Nov 20, 2006 10:59 am    Post subject: Re: Display the emp of eath claims where the date < 10 Y
Reply with quote

Rambabu,

Try this... It'll compare differences of dates for exactly 10 years...
Code:
SELECT   A.CRD-TNC,
        B.CRD_PLCY_START,
        C.EMP_NAME
FROM    R_DEATH A,
   R_PLCY B,
   R_EDETAILS C
WHERE    B.CLM_ID = A.CLM_ID
    AND B.CLM_ID = C.CLM_ID
    AND A.CRD-TNC - B.CRD_PLCY_START <= 100000;


Note: Here "100000" correspond to 10 Yrs, 00 Months, 00 Days.
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 Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm
No new posts Display FTP Session Count within TSO Yolanda Harvey TSO/ISPF 3 Fri Mar 10, 2017 10:31 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts How to get current date -1 and curren... murali.andaluri DFSORT/ICETOOL 3 Wed Mar 08, 2017 2:09 pm
No new posts Overlay the system date Danielle.Filteau SYNCSORT 4 Fri Mar 03, 2017 1:16 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us