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
 

 

Db2 Date Query

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

New User


Joined: 29 Aug 2005
Posts: 14

PostPosted: Thu Nov 06, 2008 11:39 pm    Post subject: Db2 Date Query
Reply with quote

My requirment is this ,

I have two tables 216 and 240 the have the fields like this
Code:
PASR216A_EPEPTLS
EMP_NR
EA_YEAR
EA_MONTHS_WRKD
EA_HOURS_WRKD
EA_HOURS_EQUIV
EA_SSF_HOURS_EQUIV
EA_SSG_HOURS_EQUIV
EA_PILOT_MTHS_WKD

and
Code:
PASR240A_PEPHIST
EMP_NR
PH_STRT_DTE
PH_END_DTE
PH_PEP_TYP
PH_PAS_PLN_NR
PH_WORK_GRP
PH_PORT_CDE

My requirement is I will expalin with an example

Let me try this again with an example. If you find employee 1234 on the 216 table with months worked > 0 for 1999. Thyen you go to the 240 table and you query for employee number = 1234 and PH_Pep_Typ = 'E'. It may return multiple rows. Then you need to see if any part of 1999 is included between the PH_Strt_Dte and PH_End_Dte. If it is, no further action is required for this employee. If it is now, then you need to add this employee to the output list. Include employee number and year from 216 table.

My query I have written is

Code:
 SELECT DISTINCT A.EMP_NR, B.EMP_NR,             
      A.EA_YEAR,                                 
      B.PH_STRT_DTE,B.PH_END_DTE,                 
      A.EA_MONTHS_WRKD,                           
      B.PH_PEP_TYP                               
 FROM                                             
 PASR216A_EPEPTLS A,                       
 PASR240A_PEPHIST B                       
 WHERE                                           
       A.EMP_NR = B.EMP_NR                       
  AND  A.EA_MONTHS_WRKD > 0                       
  AND  B.PH_PEP_TYP  = 'E'                       
  AND  (A.EA_YEAR   < CHAR(YEAR(B.PH_STRT_DTE))   
   OR  A.EA_YEAR   > CHAR(YEAR(B.PH_END_DTE)));   

but the probelm is

ok, there are 2 records on 240 for emp 136. One is for 1985-1986 and the other is for 1998-1999. Both records on 216 are matching to both records on 240. We are not displaying the matches to the 1998-1999.
240 records because the date range logic eliminates those. But we are displaying both matches to the 1985-1986 records because the date range logic includes those. That is the cause, Please let me know if you have any solution ofrthis

Edited: Please use BBcode when You post some code, that's rather readable...Anuj
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Nov 07, 2008 1:57 am    Post subject:
Reply with quote

Hello,

It may help if you post some sample data from the 2 tables and the result you want when your query is run. You would only need to include values for the columns you want to use for selection or in the result. Include rows that have values that would be selected and some that would not.
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 Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts SUBSTR for Date functions Shaheen Shaik DB2 4 Thu Apr 06, 2017 2:14 pm
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm


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