> SELECT ; > d.GrpName, ; > d.GrpID, ; > d.Emp_ID, ; > d.BnftName, ; > SUM(d.bill_prem) AS Month_Billed, ; > SUM(d.cash_paid) AS Month_Collected, ; > ICASE(d.Bill_Mode="M", SUM((d.Bill_prem) * 12, ; > d.Bill_Mode="Q", SUM(d.Bill_Prem) * 4, ; > d.Bill_Mode="S", SUM(d.Bill_Prem) * 2, ; > d.Bill_Mode="A", SUM(d.Bill_Prem), ; > SUM(d.Bill_Prem) * 12) AS Annual_Prem, ; > l.Lives ; > FROM curDataDetail d; > JOIN tmpLives l ON d.GrpID = l.GrpID ; > WHERE BETWEEN(d.dBilling, ldFirstDateOfMonth, ldLastDateOfMonth) ; > GROUP BY GrpName, d.GrpID, d.Emp_ID, d.BnftName, l.Lives ; > INTO CURSOR ("tmp" + ALLTRIM(STR(lnMonth))) >Try instead:
SELECT ; d.GrpName, ; d.GrpID, ; d.Emp_ID, ; d.BnftName, ; SUM(d.bill_prem) AS Month_Billed, ; SUM(d.cash_paid) AS Month_Collected, ; SUM(ICASE(d.Bill_Mode="M", d.Bill_prem * 12, ; d.Bill_Mode="Q", d.Bill_Prem * 4, ; d.Bill_Mode="S", d.Bill_Prem * 2, ; d.Bill_Mode="A", d.Bill_Prem, ; d.Bill_Prem * 12)) AS Annual_Prem, ; l.Lives ; FROM curDataDetail d; JOIN tmpLives l ON d.GrpID = l.GrpID ; WHERE BETWEEN(d.dBilling, ldFirstDateOfMonth, ldLastDateOfMonth) ; GROUP BY GrpName, d.GrpID, d.Emp_ID, d.BnftName, l.Lives ; INTO CURSOR ("tmp" + ALLTRIM(STR(lnMonth)))SUM must be outside the ICASE for this to work properly.