Hi,

I have a requirement where in from my database , the records will be like below :

Plan_Code Rxr Bin ActDetails Eff_Dt
------------------------------------------------
Plan2 LPN 111 Expired 2009/10/14
Plan2 LPN 111 Expired 2009/10/14
Plan2 LPN 222 Expired 2009/10/15
Plan2 MNB 281 Expired 2009/10/14
Plan2 MNB 282 Active 2009/10/14
Plan2 MNB 282 Active 2009/10/15



The unique is Plan_Code and Rxr.So

1)if this set contains ActDetails both 'Active' and Expired then in that set if anything is expired , the BIN corresponding to Active should be displayed for the expired records
2)If in a set all are expired , then i need to take the MAX(eff_date) and the corresponding BIN needs to get dispalyed for that set.

The output should look like below:



Plan_Code Rxr Bin ActDetails Eff_Dt
------------------------------------------------
Plan2 LPN 222 Expired 2009/10/14
Plan2 LPN 222 Expired 2009/10/14
Plan2 LPN 222 Expired 2009/10/15
Plan2 MNB 282 Expired 2009/10/14
Plan2 MNB 282 Active 2009/10/14
Plan2 MNB 282 Active 2009/10/15

Please find the code below .

 
TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD AS HLD_PRC
END

TABLE FILE HLD_PRC
SUM
FST.Bin AS BIN1
BY Plan_Code
BY Rxr
WHERE ActivePlnDet EQ 'Active'
ON TABLE HOLD AS HLD1
END

TABLE FILE HLD_PRC
SUM
MAX.PlnEffDt
Bin AS BIN2
BY Plan_Code
BY Rxr
WHERE ActivePlnDet EQ 'Expired'
ON TABLE HOLD AS HLD2
END


JOIN CLEAR *
JOIN Plan_Code AND Rxr IN HLD_PRC TO Plan_Code AND Rxr IN HLD1 AS JJ2
JOIN Plan_Code AND Rxr IN HLD_PRC TO Plan_Code AND Rxr IN HLD2 AS JJ3


TABLE FILE HLD_PRC
PRINT
*
END


DEFINE FILE HLD_PRC
Fin_Bin/A6 = IF ActivePlnDet EQ 'Expired' THEN BIN1 ELSE BIN2;
END



TABLE FILE HLD_PRC
PRINT
MANG_CARE_PLN_I
Plan_Name AS 'Plan Name'
Plan_Code AS 'Plan Code'
Fin_Bin AS 'BIN'
ActivePlnDet
Prcs AS 'PCN'
GroupId AS 'Group ID'
END

With the above code ,

my output gets displayed like below :


Plan_Code Rxr Bin ActDetails Eff_Dt
------------------------------------------------
Plan2 LPN Expired 2009/10/14
Plan2 LPN Expired 2009/10/14
Plan2 LPN Expired 2009/10/15
Plan2 MNB 282 Expired 2009/10/14
Plan2 MNB 282 Active 2009/10/14
Plan2 MNB 282 Active 2009/10/15

i.e Ponint 1) gets satisfied but not 2.

Appreciate your help as this is an urgent issue that needs to get resolved.

Thanks !

Janani

Any solution?

Thanks!
Janani

Something like this perhaps?

DEFINE FILE PLAN
CA/I4 = IF ACTDETAILS EQ 'Active' THEN 1 ELSE 0;
CI/I4 = IF ACTDETAILS NE 'Active' THEN 1 ELSE 0;
CT/I4 = 1;
ACTBIN/A4 = IF ACTDETAILS EQ 'Active' THEN BIN ELSE ACTBIN;
INACTBIN/A15 = EFF_DT | '-' | BIN;
END
TABLE FILE PLAN
SUM   CT CA CI MAX.ACTBIN MAX.INACTBIN
BY    PLAN_CODE BY RXR
PRINT *
BY    PLAN_CODE BY RXR
ON    TABLE HOLD
END

DEFINE FILE HOLD
INBIN/A4 = EDIT(INACTBIN,'$$$$$$$$$$$9999');
SHOWBIN/A4 = IF CA GT 0 AND CI GT 0 THEN ACTBIN ELSE
             IF CA EQ 0 THEN INBIN ELSE BIN;
END

TABLE FILE HOLD
PRINT PLAN_CODE
      RXR
      SHOWBIN
      ACTDETAIL
      EFF_DT
END