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
 
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 How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm
No new posts Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm
No new posts Date in where clause - Windows Karthikeyan Subbarayan DB2 9 Wed Nov 15, 2017 9:07 pm
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 4 Tue Nov 07, 2017 8:34 pm

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