Home » SQL & PL/SQL » SQL & PL/SQL » select Statement Required
select Statement Required [message #243439] Thu, 07 June 2007 05:33 Go to next message
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 #243445 is a reply to message #243439] Thu, 07 June 2007 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So do it.
Post what you already tried.

Please post your Oracle version (4 decimals).
Please read and apply How to format your posts.

Regards
Michel
Re: select Statement Required [message #243451 is a reply to message #243445] Thu, 07 June 2007 06:02 Go to previous messageGo to next message
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
Re: select Statement Required [message #243453 is a reply to message #243451] Thu, 07 June 2007 06:05 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please post your Oracle version (4 decimals).
Please read and apply How to format your posts.


Regards
Michel
Previous Topic: Read spreadsheet into pl/sql
Next Topic: extract program
Goto Forum:
  


Current Time: Sun Dec 04 20:54:56 CST 2016

Total time taken to generate the page: 0.15499 seconds