Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Interesting Question related to Splitting table row conditionally Expand / Collapse
Author
Message
Posted Saturday, June 22, 2013 12:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 9:38 PM
Points: 354, Visits: 322
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.


Post #1466438
Posted Saturday, June 22, 2013 6:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 1,942, Visits: 19,997
are the tables that you describe really as simple as you make them out to be?

I would have thought for these type of transactions there would have been at least one datetime column in each table to indicate when posted?

regards


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1466455
Posted Sunday, June 23, 2013 12:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 9:38 PM
Points: 354, Visits: 322
Hi Livingston,

No, the actual tables are different but the other columns are not significant. The essence of the problem is how to split the Receipt table row based on the receipt amount as per the "MemberPremiumDet" table's premiumAmt.

This is a real difficult task to do without CURSORS/LOOPS.

I am wondering whether this can be accomplished without CURSORS or LOOPS.

These kind of transactions are usual in Banking and Insurance applications.

Anyway would datetime columns in these type of scenario help a lot?

Regards
Post #1466503
Posted Sunday, June 23, 2013 7:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 1,942, Visits: 19,997
uravindarreddy (6/23/2013)
Hi Livingston,



Anyway would datetime columns in these type of scenario help a lot?

Regards


ok...thanks for update.

was thinking that maybe a running balance by date may have helped...but can't see that it would in this case


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1466524
Posted Sunday, June 23, 2013 6:52 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:53 AM
Points: 3,422, Visits: 5,368
Mr Reddy,

You are in a much better position to determine which solution performs better, as I do not have access to your "millions" of member rows and "thousands" of receipt rows.

What I suggest you do is create a test harness something like this:

DECLARE @StartTime DATETIME;

BEGIN TRANSACTION T1;

SELECT @StartTime = GETDATE();
-- Insert your code

SELECT StartTime=@StartTIme, EndTime=GETDATE()
,ElapsedTimeMS=DATEDIFF(millisecond, @StartTime, GETDATE());

ROLLBACK TRANSACTION T1;

BEGIN TRANSACTION T1;

SELECT @StartTime = GETDATE();
-- Insert my code

SELECT StartTime=@StartTIme, EndTime=GETDATE()
,ElapsedTimeMS=DATEDIFF(millisecond, @StartTime, GETDATE());

ROLLBACK TRANSACTION T1;


And then let us know which did best.

Note that I don't think either solution is going to be particularly swift if you don't have PRIMARY KEYs and/or indexes on your tables.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1466552
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse