Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Interesting Question related to Splitting table row conditionally


Interesting Question related to Splitting table row conditionally

Author
Message
uravindarreddy
uravindarreddy
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 545
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
   Wink;
   
   --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
   Wink
   
)
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.
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3443 Visits: 32970
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

uravindarreddy
uravindarreddy
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 545
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
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3443 Visits: 32970
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

dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4231 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search