|
View previous topic :: View next topic
|
| Author |
Message |
anandhi28
New User
Joined: 07 Mar 2005
Posts: 8
Location: Bangalore
|
|
|
|
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 |
|
 |
mkk157
Active User
Joined: 17 May 2006
Posts: 310
|
|
|
|
| Can u provide the complete structure of the table. then it is easy to give a solution. |
|
| Back to top |
|
|
mkk157
Active User
Joined: 17 May 2006
Posts: 310
|
|
|
|
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 |
|
|
|
|
