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 Thursday, June 20, 2013 5:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:19 AM
Points: 321, Visits: 281
Hi SQL Addicts,

Please help! Its emergency

The Table setup is as follows
CREATE TABLE dbo.Receipt
(
ReceiptID int NOT NULL IDENTITY(1,1)
, PolicyID int NOT NULL
, ReceiptAmt NUMERIC(17,2) NOT NULL
)
GO
INSERT INTO dbo.Receipt
(PolicyID, ReceiptAmt)
SELECT 11, 3000
UNION ALL
SELECT 11, 5000
UNION ALL
SELECT 11, 1200


GO
CREATE TABLE dbo.MemberPremiumDet
(
MemberID int NOT NULL IDENTITY(1,1)
, PolicyID int NOT NULL
, PremiumAmt NUMERIC(17,2) NOT NULL
)
GO
INSERT INTO dbo.Receipt
(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
GO

Receipt Table Data
PolicyID ReceiptID ReceiptAmt
11 1 3000
11 2 5000
11 3 1200

Member Data
PolicyID MemberID PremiumAmt
11 1 1200
11 2 1600
11 3 2200
11 4 2456
11 5 1144

With the above given two tables the below output to be generated without using Cursors or loops
I Want the output as follows

MemberID ReceiptID ReceiptAmt ConsumedAmt BALAmt
171 11 3000 1200 1800
172 11 1800 1600 200
173 11 200 200 0
173 14 5000 2000 3000
174 14 3000 2456 544
175 14 544 544 0
175 16 1200 600 600


Thanx in advance!!!



Post #1465608
Posted Thursday, June 20, 2013 10:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:19 AM
Points: 321, Visits: 281
HI SQL biggies,

Please help me ! Still nobody replied...!
Post #1466028
Posted Thursday, June 20, 2013 10:48 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: Yesterday @ 8:30 PM
Points: 3,627, Visits: 5,273
After correcting the name of the table on your second insert and adding a couple of SELECT statements, I get these results:

SELECT * FROM dbo.Receipt;
SELECT * FROM dbo.MemberPremiumDet;


ReceiptID PolicyID ReceiptAmt
1 11 3000.00
2 11 5000.00
3 11 1200.00

MemberID PolicyID PremiumAmt
1 11 1200.00
2 11 1600.00
3 11 2200.00
4 11 2456.00
5 11 1144.00



Perhaps you'd explain where MemberIDs of 171-175 and ReceiptIDs of 11, 14 and 16 come from in your expected results?:

Since you're in a hurry and all.

uravindarreddy (6/20/2013)
Hi SQL Addicts,

I Want the output as follows

MemberID ReceiptID ReceiptAmt ConsumedAmt BALAmt
171 11 3000 1200 1800
172 11 1800 1600 200
173 11 200 200 0
173 14 5000 2000 3000
174 14 3000 2456 544
175 14 544 544 0
175 16 1200 600 600




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 #1466038
Posted Thursday, June 20, 2013 11:30 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:19 AM
Points: 321, Visits: 281
Hi Dwain!

Thanx for the reply.

Yeah as I am in hurry , I made copy-paste mistake. Sorry!

Here is the output should be
MemberID ReceiptID ReceiptAmt ConsumedAmt BALAmt
1 11 3000 1200 1800
2 11 1800 1600 200
3 11 200 200 0
3 14 5000 2000 3000
4 14 3000 2456 544
5 14 544 544 0
5 16 1200 600 600

The logic is to consume every receipt based on premium amount of every member id.

Hope you will give the optimum solution without loops!!!
Post #1466046
Posted Thursday, June 20, 2013 11:32 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:19 AM
Points: 321, Visits: 281
Hi,

I am sorry again ...
Here is the updated output

MemberID ReceiptID ReceiptAmt ConsumedAmt BALAmt
1 1 3000 1200 1800
2 1 1800 1600 200
3 1 200 200 0
3 2 5000 2000 3000
4 2 3000 2456 544
5 2 544 544 0
5 3 1200 600 600


If the premium amount of all members is greater than the total receipt amount then only the above output should display.
Post #1466048
Posted Friday, June 21, 2013 12:32 AM


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: Yesterday @ 8:30 PM
Points: 3,627, Visits: 5,273
Interesting problem!

Unfortunately I don't have the time to take it through to conclusion, however here is a start. It will allocate the first receipt.

WITH Matching AS (
SELECT MemberID, ReceiptID, ReceiptAmt=b.ReceiptAmt
,ConsumedAmt=CAST(CASE WHEN a.PremiumAmt < b.ReceiptAmt THEN a.PremiumAmt ELSE 0 END AS NUMERIC(17,2))
,BalAmt=CAST(CASE WHEN 0 < b.ReceiptAmt-a.PremiumAmt THEN b.ReceiptAmt-a.PremiumAmt ELSE 0 END AS NUMERIC(17,2))
,a.PolicyID
FROM dbo.MemberPremiumDet a
INNER JOIN dbo.Receipt b ON a.PolicyID = b.PolicyID AND b.ReceiptID = a.MemberID
WHERE b.ReceiptID = 1
UNION ALL
SELECT a.MemberID, ReceiptID, ReceiptAmt=b.BalAmt
,ConsumedAmt=CASE WHEN a.PremiumAmt < b.BalAmt THEN a.PremiumAmt ELSE b.BalAmt END
,BalAmt=CAST(CASE WHEN 0 < b.BalAmt-a.PremiumAmt THEN b.BalAmt-a.PremiumAmt ELSE 0 END AS NUMERIC(17,2))
,a.PolicyID
FROM dbo.MemberPremiumDet a
INNER JOIN Matching b ON a.PolicyID = b.PolicyID AND a.MemberID = b.MemberID + 1
WHERE BalAmt > 0
)
SELECT *
FROM Matching





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 #1466063
Posted Friday, June 21, 2013 1:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:19 AM
Points: 321, Visits: 281
HI dwain!!

Thanx for your reply !!!

In fact I did the same thing.

I want an idea to split the the second Receipt ID based on the Balance amount of receipt amount.

Its really an interesting Problem and I want to do it without any loops.
Post #1466069
Posted Friday, June 21, 2013 1:13 AM


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: Yesterday @ 8:30 PM
Points: 3,627, Visits: 5,273
I'm not sure if you can do it without a loop.

I'd probably put a CURSOR on the receipts table and allocate each receipt using the rCTE within that loop.

However I'll think further on it and if I come up with anything I'll post again.



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 #1466070
Posted Friday, June 21, 2013 1:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:19 AM
Points: 321, Visits: 281
HI dwain!!!

Could you help me in generating script with CURSOR?

Even with CURSOR it is bit tricky.

I am not able to find a perfect script for different kinds of data scenarios.

Post #1466080
Posted Friday, June 21, 2013 5:29 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: Yesterday @ 8:30 PM
Points: 3,627, Visits: 5,273
uravindarreddy (6/21/2013)
HI dwain!!!

Could you help me in generating script with CURSOR?

Even with CURSOR it is bit tricky.

I am not able to find a perfect script for different kinds of data scenarios.



I think this will do it for you with a CURSOR, and believe me when I say this is quite a challenge for me (remembering the syntax) since I rarely ever use or recommend them. Note that I'm using temp tables and I haven't tested for cases where there are multiple policy IDs for both members and receipts (because you didn't give me sample data) but I wrote the code trying to cover that case.

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);

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;

CREATE TABLE #ReceiptsMatched
(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));

DECLARE @ReceiptID INT, @PolicyID INT, @ReceiptAmt NUMERIC(17,2)
,@RemainingPremium NUMERIC(17,2) = 0
,@MemberID INT = (SELECT TOP 1 MemberID FROM #MemberPremiumDet ORDER BY PolicyID, MemberID);

DECLARE Receipts CURSOR FOR
SELECT ReceiptID, PolicyID, ReceiptAmt
FROM #Receipt
--WHERE ReceiptID = 1
ORDER BY PolicyID, ReceiptID;

OPEN Receipts;

FETCH NEXT
FROM Receipts
INTO @ReceiptID, @PolicyID, @ReceiptAmt;

WHILE @@FETCH_STATUS = 0
BEGIN

WITH Matching AS (
SELECT MemberID, ReceiptID, ReceiptAmt=b.ReceiptAmt
,ConsumedAmt=CAST(
CASE WHEN a.PremiumAmt - @RemainingPremium < b.ReceiptAmt
THEN a.PremiumAmt - @RemainingPremium
ELSE 0
END AS NUMERIC(17,2))
,BalAmt=CAST(
CASE WHEN 0 < b.ReceiptAmt - (a.PremiumAmt - @RemainingPremium)
THEN b.ReceiptAmt - (a.PremiumAmt - @RemainingPremium)
ELSE 0
END AS NUMERIC(17,2))
,a.PolicyID
FROM #MemberPremiumDet a
INNER JOIN #Receipt b ON a.PolicyID = b.PolicyID
WHERE b.ReceiptID = @ReceiptID AND a.PolicyID = @PolicyID AND a.MemberID = @MemberID
UNION ALL
SELECT a.MemberID, ReceiptID, ReceiptAmt=b.BalAmt
,ConsumedAmt=CASE WHEN a.PremiumAmt < b.BalAmt THEN a.PremiumAmt ELSE b.BalAmt END
,BalAmt=CAST(CASE WHEN 0 < b.BalAmt-a.PremiumAmt THEN b.BalAmt-a.PremiumAmt ELSE 0 END AS NUMERIC(17,2))
,a.PolicyID
FROM #MemberPremiumDet a
INNER JOIN Matching b ON a.PolicyID = b.PolicyID AND a.MemberID = b.MemberID + 1
WHERE BalAmt > 0
)
INSERT INTO #ReceiptsMatched
SELECT MemberID, ReceiptID, PolicyID, ReceiptAmt, ConsumedAmt, BalAmt
FROM Matching;

SELECT TOP 1 @RemainingPremium=ConsumedAmt
,@MemberID=MemberID
FROM #ReceiptsMatched
ORDER BY PolicyID, MemberID DESC, ReceiptID DESC;

FETCH NEXT
FROM Receipts
INTO @ReceiptID, @PolicyID, @ReceiptAmt;
END

-- Desired results
--1 1 3000 1200 1800
--2 1 1800 1600 200
--3 1 200 200 0
--3 2 5000 2000 3000
--4 2 3000 2456 544
--5 2 544 544 0
--5 3 1200 600 600
SELECT * FROM #ReceiptsMatched;
GO
DROP TABLE #Receipt;
DROP TABLE #MemberPremiumDet;
DROP TABLE #ReceiptsMatched;
CLOSE Receipts;
DEALLOCATE Receipts;


It may be possible to do this with a Quirky Update (QU) but that's gonna be really tricky. Tried a bit but couldn't get it to fully work. May try again but this solution may actually work faster than the QU would. It would probably depend on how many members get allocated an individual receipt.



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 #1466428
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse