| select Statement Required [message #243439] |
Thu, 07 June 2007 05:33  |
sanammg
Messages: 31
Registered: June 2005
|
Member |
|
|
hi
i am using oracle9i DB, i need small query
i have the data in table like this or i have the query to select the data like this
(order by 'STARTDATE')
CLIENT ID_NUMBER STARTDATE ENDDATE PARTIME_PERC IND_NP EMPLOYMENT_PERIOD
N0000010002 102 1/1/2003 8/25/2003 100
N0000010002 102 8/25/2003 1/1/2004 100
N0000010002 102 1/1/2004 7/1/2004 60
N0000010002 102 7/1/2004 1/1/2005 100
N0000010002 102 1/1/2005 1/1/2006 60
N0000010002 102 1/1/2006 1/1/2007 60
N0000010002 102 1/1/2007 60
from this above data i want to select like this.
it should be grouped by 'PARTIME_PERC' And it should be min(startdate) and max(enddate) for each PARTIME_PERC
the out put should be
CLIENT ID_NUMBER STARTDATE ENDDATE PARTIME_PERC IND_NP EMPLOYMENT_PERIOD
N0000010002 102 1/1/2003 1/1/2004 100
N0000010002 102 1/1/2004 7/1/2004 60
N0000010002 102 7/1/2004 1/1/2005 100
N0000010002 102 1/1/2005 60
help required....
|
|
|
|
|
|
| Re: select Statement Required [message #243451 is a reply to message #243445] |
Thu, 07 June 2007 06:02  |
sanammg
Messages: 31
Registered: June 2005
|
Member |
|
|
i tryed like this
SELECT CLIENT
, ID_NUMBER
, MIN(STARTDATE)
, MAX(ENDDATE)
, PARTIME_PERC FROM (
SELECT SAL.CLIENT CLIENT
, EI.ID_NUMBER ID_NUMBER
, SAL.STARTDATE STARTDATE
, TO_DATE(DECODE(NVL(SAL.ENDDATE, TRUNC(SYSDATE)), TRUNC(SYSDATE), NULL, SAL.ENDDATE),'DD/MM/YY') ENDDATE
, SAL.PARTIME_PERC PARTIME_PERC
, NULL
, NULL
FROM SALARY SAL ,EXTERNAL_ID EI
WHERE SAL.SALARY_TYPE = 4
AND (SAL.NULLDATE IS NULL OR TRUNC(SAL.NULLDATE) > TRUNC(SYSDATE))
AND SAL.CLIENT = EI.CLIENT
AND SAL.CLIENT = 'N0000010002'
AND EI.ID_CODE = 1
-- GROUP BY SAL.CLIENT,SAL.PARTIME_PERC,EI.ID_NUMBER,STARTDATE,ENDDATE
-- ORDER BY CLIENT,STARTDATE
)
GROUP BY CLIENT, ID_NUMBER,PARTIME_PERC
i got the result like this
CLIENT ID_NUMBER MIN(STARTDATE) MAX(ENDDATE) PARTIME_PERC
N0000010002 102 1/1/2004 1/1/2007 60.00
N0000010002 102 1/1/2003 1/1/2005 100.00
but need the result as mentioned above
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
