Return MIN and Max for a particular subset

[Nandarangu]

Sample data:

C_STATUS C_PHONE
---------- ----------
ACTIVE 7044649707
ACTIVE 7044640709
ACTIVE 7044649710
PENDING 7044649701
PENDING 7044649702
WORKING 3015805111
WORKING 3015805112
WORKING 3015805113
PENDING 7034640001
PENDING 7034640002
PENDING 7034640003
ACTIVE 7034640709
ACTIVE 7034640710
WORKING 7034645111
WORKING 7034645112
WORKING 7034645113
PENDING 7034649701
PENDING 7034649702

I need min and max phone numbers according to particular set of status.


My output should be ( I don’t what the phone numbers to be in that specific format it can be even 7034649707-7044640709)


C_STATUS C_PHONE

---------- ---------
ACTIVE 7034640707-0710
PENDING 7034649701-9702
WORKING 7034645111-5113
PENDING 7034640001-0003
ACTIVE 7034640709-0710
WORKING 7034645111-5113
PENDING 7034649701-9702

Any ideas ??

thank you for your help
Nanda

[pragatiswain]

Hope the following piece will help. (Not tested)

C_STATUS_TRACK VARCHAR2(20) :=NULL;
C_PHONE_TRACK VARCHAR2(25) := NULL;
C_CNT_TRACK NUMBER(20) := 0;

CURSOR PHONE_STATUS IS
SELECT C_STATUS, C_PHONE, COUNT(*) C_CNT
FROM ####
ORDER BY C_STATUS, C_PHONE

PHONE_STATUS_REC PHONE_STATUS%ROWTYPE;

FOR PHONE_STATUS_REC IN PHONE_STATUS
LOOP

C_CNT_TRACK := C_CNT_TRACK + 1;

IF (PHONE_STATUS_REC.C_STATUS <> C_STATUS_OLD) OR (C_CNT_TRACK = 1) THEN
C_STATUS_TRACK := PHONE_STATUS_REC.C_STATUS;
C_PHONE_TRACK := TO_CHAR(PHONE_STATUS_REC.C_PHONE);
ELSE
BEGIN
IF (PHONE_STATUS_REC.C_PHONE <> C_STATUS_OLD + 1) THEN
C_PHONE_TRACK := C_PHONE_TRAC || '-' || TO_CHAR(PHONE_STATUS_REC.C_PHONE);
INSERT INTO TMP_#### VALUES (C_STATUS_TRACK, C_PHONE_TRACK);
C_STATUS_TRACK := NULL;
C_PHONE_TRACK := 0;
END IF;
END;
END IF;

IF (PHONE_STATUS_REC.C_STATUS = C_CNT_TRACK) AND (C_STATUS_TRACK <> NULL) THEN
C_PHONE_TRACK := C_PHONE_TRAC || '-' || TO_CHAR(PHONE_STATUS_REC.C_PHONE);
INSERT INTO TMP_#### VALUES (C_STATUS_TRACK, C_PHONE_TRACK);
END IF;

C_STATUS_OLD := PHONE_STATUS_REC.C_STATUS;
C_PHONE_OLD := PHONE_STATUS_REC.C_PHONE;

END LOOP;

[pragatiswain]

Hope the following piece will help. (Not tested)

C_STATUS_TRACK VARCHAR2(20) :=NULL;
C_PHONE_TRACK VARCHAR2(25) := NULL;
C_CNT_TRACK NUMBER(20) := 0;

CURSOR PHONE_STATUS IS
SELECT C_STATUS, C_PHONE, COUNT(*) C_CNT
FROM ####
ORDER BY C_STATUS, C_PHONE

PHONE_STATUS_REC PHONE_STATUS%ROWTYPE;

FOR PHONE_STATUS_REC IN PHONE_STATUS
LOOP

C_CNT_TRACK := C_CNT_TRACK + 1;

IF (PHONE_STATUS_REC.C_STATUS <> C_STATUS_OLD) OR (C_CNT_TRACK = 1) THEN
C_STATUS_TRACK := PHONE_STATUS_REC.C_STATUS;
C_PHONE_TRACK := TO_CHAR(PHONE_STATUS_REC.C_PHONE);
IF (PHONE_STATUS_REC.C_STATUS <> C_STATUS_OLD) THEN
C_PHONE_TRACK := C_PHONE_TRACK || '-' || C_PHONE_OLD;
INSERT INTO TMP_#### VALUES (C_STATUS_TRACK, C_PHONE_TRACK);
C_STATUS_TRACK := NULL;
C_PHONE_TRACK := 0;
ENDIF;
ELSE
BEGIN
IF (PHONE_STATUS_REC.C_PHONE <> C_STATUS_OLD + 1) THEN
C_PHONE_TRACK := C_PHONE_TRAC || '-' || TO_CHAR(PHONE_STATUS_REC.C_PHONE);
INSERT INTO TMP_#### VALUES (C_STATUS_TRACK, C_PHONE_TRACK);
C_STATUS_TRACK := NULL;
C_PHONE_TRACK := 0;
END IF;
END;
END IF;

IF (PHONE_STATUS_REC.C_STATUS = C_CNT_TRACK) AND (C_STATUS_TRACK <> NULL) THEN
C_PHONE_TRACK := C_PHONE_TRAC || '-' || TO_CHAR(PHONE_STATUS_REC.C_PHONE);
INSERT INTO TMP_#### VALUES (C_STATUS_TRACK, C_PHONE_TRACK);
END IF;

C_STATUS_OLD := PHONE_STATUS_REC.C_STATUS;
C_PHONE_OLD := PHONE_STATUS_REC.C_PHONE;

END LOOP;[/quote]