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
 

 

Need help on thease SQL Queries

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

New User


Joined: 07 Mar 2005
Posts: 8
Location: Bangalore

PostPosted: Thu Jan 17, 2008 4:55 pm    Post subject: Need help on thease SQL Queries
Reply with quote

Query 1 gives me the result of the total number of hrs worked by an Employee in the week ,
And Query 2 results me all the employees worked on a particular day eg: 11/22/2007.
I need to write a query which will result me in the employee data who worked on 11/22/2007 and the total number hours worked by him for that week is >2400 mins.
i need to collate the results of both the queries below and write in a single query ...

1) SELECT "EMP_SCL_SCUTY_NO AS EMPLOYEE_SSN ,
SUM ( ACTY_INPT_QTY ) AS TOTAL
FROM DBJC.CORPD000.XPPERP0V
WHERE ("EMP_ACTY_REPTG_DAT" > '2007-11-18'
AND "EMP_ACTY_REPTG_DAT" <= '2007-11-24'
AND "EMP_REPTG_ACTY_TYP" = 'R'
AND "ACTY_INPT_QTY" <> 0 )
GROUP BY EMP_SCL_SCUTY_NO
ORDER BY TOTAL DESC
FOR FETCH ONLY WITH UR

and

SELECT "EMP_SCL_SCUTY_NO" ,
SUM ( ACTY_INPT_QTY )
FROM DBJC.CORPD000.XPPERP0V
WHERE "EMP_ACTY_REPTG_DAT" = '2007-11-22'
AND "EMP_REPTG_ACTY_TYP" = 'R'
AND "ACTY_INPT_QTY" <> 0
AND "REPTG_ACTY_STAT_CD" = '1'
GROUP BY EMP_SCL_SCUTY_NO
FOR FETCH ONLY WITH UR


Please help me ...i need it pretty urgently
thanks Anandhi
Back to top
View user's profile Send private message

mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Thu Jan 17, 2008 7:30 pm    Post subject: Hi
Reply with quote

Can u provide the complete structure of the table. then it is easy to give a solution.
Back to top
View user's profile Send private message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Thu Jan 17, 2008 7:35 pm    Post subject: Hi
Reply with quote

Hi Anandhi28,

Try these queries once.

Query # 1:

SELECT "EMP_SCL_SCUTY_NO AS EMPLOYEE_SSN ,
SUM ( ACTY_INPT_QTY ) AS TOTAL
FROM DBJC.CORPD000.XPPERP0V
WHERE ("EMP_ACTY_REPTG_DAT" > '2007-11-18'
AND "EMP_ACTY_REPTG_DAT" <= '2007-11-24'
AND "EMP_ACTY_REPTG_DAT" = '2007-11-22'
AND "EMP_REPTG_ACTY_TYP" = 'R'
AND "ACTY_INPT_QTY" <> 0 )
AND "REPTG_ACTY_STAT_CD" = '1'
GROUP BY EMP_SCL_SCUTY_NO
ORDER BY TOTAL DESC
FOR FETCH ONLY WITH UR


Query # 2:

SELECT "EMP_SCL_SCUTY_NO AS EMPLOYEE_SSN ,
SUM ( ACTY_INPT_QTY ) AS TOTAL
FROM DBJC.CORPD000.XPPERP0V
WHERE ("EMP_ACTY_REPTG_DAT" > '2007-11-18'
AND "EMP_ACTY_REPTG_DAT" <= '2007-11-24'
AND "EMP_ACTY_REPTG_DAT" = '2007-11-22'
AND "EMP_REPTG_ACTY_TYP" = 'R'
AND "ACTY_INPT_QTY" <> 0 )
GROUP BY EMP_SCL_SCUTY_NO
ORDER BY TOTAL DESC
FOR FETCH ONLY WITH UR
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 Need help with below queries ashek15 DB2 3 Thu Mar 23, 2017 1:38 am
No new posts Running queries against OLTP Database A_programmers DB2 3 Tue Jul 14, 2015 4:51 pm
No new posts Selective execution of a queries Delip DB2 9 Fri Jan 16, 2015 3:51 pm
No new posts Re-write queries to minimize the effe... Anil Khanna DB2 6 Mon Aug 04, 2014 1:49 pm
No new posts Running multiple queries in a PS file... nikhil_bansal04 JCL & VSAM 12 Wed Dec 18, 2013 12:06 pm


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