Hi Dwain!!!

Again a lot of thanx for your time and inputs.

I think we are in same line when thinking of a solution.

Here was my solution and you posted almost the same thing.

I have never written a CURSOR in my 5 years career. This would be my first CURSOR code.

I am posting here my code. There is a little difference in the logic.

If First receipt has enough amount no need to allocate other receipts.

Also, I am not sure whether my or your solution will work if MemberPremiumDet table has millions records and

Receipt table has thousand records.

CREATE TABLE #Receipt

(ReceiptID int NOT NULL IDENTITY(1,1)

,PolicyID int NOT NULL

,ReceiptAmt NUMERIC(17,2) NOT NULL);

INSERT INTO #Receipt (PolicyID, ReceiptAmt)

SELECT 11, 3000 UNION ALL SELECT 11, 5000 UNION ALL SELECT 11, 1200;

CREATE TABLE #MemberPremiumDet

(MemberID int NOT NULL IDENTITY(1,1)

,PolicyID int NOT NULL

,PremiumAmt NUMERIC(17,2) NOT NULL

,RunningTotal NUMERIC(17,2) NULL);

INSERT INTO #MemberPremiumDet (PolicyID, PremiumAmt)

SELECT 11, 1200 UNION ALL SELECT 11, 1600 UNION ALL SELECT 11, 2200

UNION ALL SELECT 11, 2456 UNION ALL SELECT 11, 1144;

DECLARE @Total NUMERIC(17,2) , @PreviID int

UPDATE #MemberPremiumDet

SET @Total = RunningTotal = CASE

WHEN PolicyID = @PreviID

THEN @Total + PremiumAmt

ELSE PremiumAmt

END

, @PreviID = PolicyID;

--SELECT * FROM #MemberPremiumDet;

CREATE TABLE #ReceiptsMatched

(

ReceiptMatchID int NULL

, MemberID int NOT NULL

,ReceiptID int NOT NULL

,PolicyID int NOT NULL

,ReceiptAmt NUMERIC(17,2)

,ConsumedAmt NUMERIC(17,2)

,BALAmt NUMERIC(17,2)

,PRIMARY KEY (ReceiptID, MemberID)

);

; WITH Matching as

(

SELECT

ROW_NUMBER() OVER(ORDER BY MPD.PolicyID, MPD.MemberID, R.ReceiptID) as ID

, MPD.MemberID

, R.ReceiptID

, MPD.PolicyID

, R.ReceiptAmt

--, MPD.PremiumAmt

, CASE

WHEN isnull(MPD.RunningTotal,0) <= R.ReceiptAmt

THEN MPD.PremiumAmt

ELSE R.ReceiptAmt - (isnull(MPD.RunningTotal,0) - MPD.PremiumAmt)

END AS ConsumedAmt

,R.ReceiptAmt - CASE

WHEN isnull(RunningTotal,0) <= R.ReceiptAmt

THEN isnull(RunningTotal,0)

ELSE R.ReceiptAmt

END AS BalanceAmt

FROM #MemberPremiumDet MPD

INNER JOIN #Receipt R

ON MPD.PolicyID = R.PolicyID

WHERE R.ReceiptID = 1

AND MPD.PolicyID = 11

)

INSERT INTO #ReceiptsMatched

(

ReceiptMatchID

, MemberID

,ReceiptID

,PolicyID

,ReceiptAmt

,ConsumedAmt

,BALAmt

)

SELECT M.ID

, M.MemberID

, M.ReceiptID

, M.PolicyID

, CASE WHEN M1.ID IS NULL

THEN M.ReceiptAmt

ELSE M1.BalanceAmt

END AS BALAmt

, M.ConsumedAmt

, M.BalanceAmt

FROM Matching M

LEFT OUTER JOIN Matching M1

ON M.ID = M1.ID + 1

WHERE M.ConsumedAmt >= 0

IF (SELECT TOP 1 R.BALAmt FROM #ReceiptsMatched R WHERE R.PolicyID = 11 ORDER BY R.ReceiptMatchID DESC) = 0

BEGIN

--SELECT R.ReceiptID, PolicyID

--FROM #Receipt R

--WHERE R.ReceiptID > 1

--AND R.PolicyID = 11

--AND NOT EXISTS

--(SELECT TOP 1 RM.BALAmt

--FROM #ReceiptsMatched RM

--WHERE RM.PolicyID = R.PolicyID

--AND RM.ReceiptID = R.ReceiptID

--AND RM.BALAmt = 0

--ORDER BY RM.ReceiptMatchID DESC)

DECLARE @ID INT , @PolicyID INT, @BALAmt NUMERIC(17,2);

DECLARE RCPT CURSOR FOR

SELECT R.ReceiptID, PolicyID

FROM #Receipt R

WHERE R.ReceiptID > 1

AND R.PolicyID = 11

AND NOT EXISTS

(SELECT TOP 1 RM.BALAmt

FROM #ReceiptsMatched RM

WHERE RM.PolicyID = R.PolicyID

AND RM.ReceiptID = R.ReceiptID

AND RM.BALAmt = 0

ORDER BY RM.ReceiptMatchID DESC)

OPEN RCPT;

FETCH RCPT

INTO @ID, @PolicyID

WHILE (@@FETCH_STATUS = 0)

BEGIN

DECLARE @LastMemberID INT , @LastConsumedAmt NUMERIC(17,2), @LastBALAmt NUMERIC(17,2)

SELECT TOP 1

@LastMemberID = MemberID

, @LastConsumedAmt = ConsumedAmt

, @LastBALAmt = BALAmt

FROM #ReceiptsMatched

ORDER BY MemberID DESC

SET @Total = 0

SET @PreviID = 0

UPDATE #MemberPremiumDet

SET @Total = RunningTotal = CASE WHEN PolicyID = @PreviID THEN @Total + PremiumAmt

ELSE PremiumAmt - @LastConsumedAmt

end

, @PreviID = PolicyID

where MemberID NOT IN

(

SELECT MemberID

FROM #ReceiptsMatched R

WHERE R.BALAmt > 0

);

--SELECT * FROM #MemberPremiumDet

; WITH Matching as

(

SELECT

ROW_NUMBER() OVER(ORDER BY MPD.PolicyID, MPD.MemberID, R.ReceiptID) as ID

, MPD.MemberID

, R.ReceiptID

, MPD.PolicyID

, R.ReceiptAmt

--, MPD.PremiumAmt

, CASE

WHEN isnull(MPD.RunningTotal,0) <= MPD.PremiumAmt

THEN MPD.RunningTotal

WHEN isnull(MPD.RunningTotal,0) <= R.ReceiptAmt

THEN MPD.PremiumAmt

ELSE R.ReceiptAmt - (isnull(MPD.RunningTotal,0) - MPD.PremiumAmt)

END AS ConsumedAmt

,R.ReceiptAmt - CASE

WHEN isnull(RunningTotal,0) <= R.ReceiptAmt

THEN isnull(RunningTotal,0)

ELSE R.ReceiptAmt

END AS BalanceAmt

FROM #MemberPremiumDet MPD

LEFT JOIN #ReceiptsMatched RM

ON RM.PolicyID = MPD.PolicyID

AND RM.MemberID = MPD.MemberID

INNER JOIN #Receipt R

ON MPD.PolicyID = R.PolicyID

WHERE R.ReceiptID = @ID

AND MPD.PolicyID = @PolicyID

AND NOT EXISTS

(

SELECT 1

FROM #ReceiptsMatched RCT

WHERE RCT.BALAmt > 0

AND RCT.PolicyID = MPD.PolicyID

AND RCT.MemberID = MPD.MemberID

)

)

INSERT INTO #ReceiptsMatched

(

ReceiptMatchID

, MemberID

,ReceiptID

,PolicyID

,ReceiptAmt

,ConsumedAmt

,BALAmt

)

--SELECT * FROM Matching

SELECT M.ID

, M.MemberID

, M.ReceiptID

, M.PolicyID

, CASE WHEN M1.ID IS NULL

THEN M.ReceiptAmt

ELSE M1.BalanceAmt

END AS BALAmt

, M.ConsumedAmt

, M.BalanceAmt

--, M.*

--, M1.*

FROM Matching M

LEFT OUTER JOIN Matching M1

ON M.ID = M1.ID + 1

WHERE M.ConsumedAmt >= 0

FETCH NEXT FROM RCPT

INTO @ID, @PolicyID;

END

CLOSE RCPT

DEALLOCATE RCPT

END

SELECT * FROM #ReceiptsMatched;

DROP TABLE #Receipt;

DROP TABLE #MemberPremiumDet;

DROP TABLE #ReceiptsMatched;

Please review this solution and let me know the flaws.

Make the Receipt amount of first receipt to 8000/9000 and check.

But, I am disappointed as there are not much replies to this interesting question(scenario).

And another thing I have noticed is your signature quote says "NO CURSOR" but you recommended CURSOR. Such is the scenario. I request you to just go through such scenarios and eventually develop an article based on this. That would be great if you do as I religiously follow your articles.