June 20, 2013 at 5:47 am
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
PolicyIDReceiptIDReceiptAmt
1113000
1125000
1131200
Member Data
PolicyIDMemberIDPremiumAmt
1111200
1121600
1132200
1142456
1151144
With the above given two tables the below output to be generated without using Cursors or loops
I Want the output as follows
MemberIDReceiptIDReceiptAmtConsumedAmtBALAmt
17111300012001800
1721118001600200
173112002000
17314500020003000
1741430002456544
175145445440
175161200600600
Thanx in advance!!!
June 20, 2013 at 10:09 pm
HI SQL biggies,
Please help me ! Still nobody replied...!
June 20, 2013 at 10:48 pm
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
MemberIDReceiptIDReceiptAmtConsumedAmtBALAmt
17111300012001800
1721118001600200
173112002000
17314500020003000
1741430002456544
175145445440
175161200600600
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 20, 2013 at 11:30 pm
Hi Dwain!
Thanx for the reply.
Yeah as I am in hurry , I made copy-paste mistake. Sorry!
Here is the output should be
MemberIDReceiptIDReceiptAmtConsumedAmtBALAmt
111300012001800
21118001600200
3112002000
314500020003000
41430002456544
5145445440
5161200600600
The logic is to consume every receipt based on premium amount of every member id.
Hope you will give the optimum solution without loops!!!
June 20, 2013 at 11:32 pm
Hi,
I am sorry again ...
Here is the updated output
MemberIDReceiptIDReceiptAmtConsumedAmtBALAmt
11300012001800
2118001600200
312002000
32500020003000
4230002456544
525445440
531200600600
If the premium amount of all members is greater than the total receipt amount then only the above output should display.
June 21, 2013 at 12:32 am
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 21, 2013 at 1:07 am
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.
June 21, 2013 at 1:13 am
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 21, 2013 at 1:45 am
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.
June 21, 2013 at 5:29 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 22, 2013 at 12:33 am
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.
June 22, 2013 at 6:51 am
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
June 23, 2013 at 12:34 am
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
June 23, 2013 at 7:53 am
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
June 23, 2013 at 6:52 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply