January 18, 2016 at 10:13 am
If have the following code that is called by triggers to update data in a parent table. It is used to validate commission allocation is correct.
The triggers execute the code properly when child table updates are tested in SSMS, however when the triggers are enabled, and an update is performed from the application, I receive an error stating "Unable to update record. A cursor with the name 'crsr_RepRates' does not exist. The statement has been terminated'.
I'm investigating why this doesn't work when executed form the application, but in the mean time, is there a way to rewrite this code so that it doesn't use cursors?
DECLARE @CS_IDSMALLINT
DECLARE @Yr1RateDECIMAL(6,2)
DECLARE @RnwRateDECIMAL(6,2)
--Local variables
DECLARE @BaseCommMONEY= 1000.0000
DECLARE @RemCommMONEY= 1000.0000
DECLARE @CommRateDECIMAL(6,2)
IF EXISTS (SELECT name FROM sys.dm_exec_cursors(0) WHERE name='crsr_RepRates')
BEGIN
CLOSE crsr_RepRates
DEALLOCATE crsr_RepRates
END
SELECT @Alloc = 0.0000
DECLARE crsr_RepRates CURSOR FOR
SELECT
CS_ID,
RR_Yr1Rate,
RR_RenewRate
FROM tM_RepRateV2
WHERE PP_ID = @PP_IDParm-- passed from trigger
OPEN crsr_RepRates
FETCH NEXT FROM crsr_RepRates INTO
@CS_ID,
@Yr1Rate,
@RnwRate
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Rate = 'Y'
SELECT @CommRate = @Yr1Rate / 100.0000
ELSE
SELECT @CommRate = @RnwRate / 100.0000
IF @CS_ID = 1--Pre-GPS (may be several records)
BEGIN
SET @Alloc= @BaseComm * @CommRate-- Calculate the commission paid amount
SET @RemComm= @BaseComm - @Alloc-- Calculate comm rec'd amount remaining
END
IF @CS_ID = 2--GPS (should only ever be 1 record)
BEGIN
SET @Alloc= @Alloc + (@RemComm * @CommRate)-- Calculate the commission paid amount
SET @RemComm= @BaseComm - @Alloc-- Calculate comm rec'd amount remaining
END
IF @CS_ID = 3--Post-GPS (1 or more records)
BEGIN
SET @Alloc= @Alloc +(@RemComm * @CommRate)-- Calculate the commission paid amount
END
FETCH NEXT FROM crsr_RepRates INTO
@CS_ID,
@Yr1Rate,
@RnwRate
END
RETURN @Alloc
SET NOCOUNT OFF
CLOSE crsr_RepRates
DEALLOCATE crsr_RepRates
January 18, 2016 at 10:32 am
The code you posted appears to be incomplete.
The comments already indicate that @PP_IDParm is passed from the trigger, but you did not post the actual trigger code. The code you posted also uses a variable @Rate for which I see no declaration or assignment.
If you want us to help you rewrite the trigger, then you will need to provide more information. Ideally, post CREATE TABLE statements for all tables involved (you may omit columns not used in this context, but please do include all constraints and indexes on the remaining columns); INSERT statements with a representative set of sample data to show the "starting point" of the data, an example insert, update, or delete statement that should cause the trigger to fire, and the expected end result after the trigger completes. Do not forget to test your CREATE TABLE and INSERT statements before posting, and remember that we probably do not know many thiongs you take for granted - so adding an explanation of the logic as if you are explaining it to a 14-year old of average intelligence is not a bad idea.
As to the reason for your error, my first (wild) guess is that multiple instances of the stored procedure run in parallel and the second drops the cursor before the first one finishes. But I must admit that my experience with cursors is way too limited to know if this is actually possible at all. And you are absolutely right that the best way to fix this is to not use a cursor at all - especially for logic invoked from a trigger!
January 18, 2016 at 11:48 am
As requested, here's additional code related to this issue.
As to the statement "...that multiple instances of the stored procedure run in parallel and the second drops the cursor ..." I don't think that's the case since I'm the only user on the dev box. Nothing else is running. And this does work properly when running in SSMS.
All 3 triggers use similar code
CREATE TRIGGER [dbo].[RecalcOnDelete]
ON [dbo].[tM_RepRateV2]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Type VARCHAR(1)
DECLARE @PP_AllocnMONEY= 0.0000
DECLARE @PP_IDSMALLINT= (SELECT PP_ID FROM deleted)
SELECT @Type = 'Y'
EXEC dbo.usp_PP_Allocn @PP_ID, @Type, @PP_Allocn OUTPUT
UPDATE dbo.tM_PolProd SET PP_Yr1Allocn = @PP_Allocn WHERE PP_ID = @PP_ID
SELECT @Type = 'R'
EXEC dbo.usp_PP_Allocn @PP_ID, @Type, @PP_Allocn OUTPUT
UPDATE dbo.tM_PolProd SET PP_RnwAllocn = @PP_Allocn WHERE PP_ID = @PP_ID
END
Create tables showing relevant columns. I left off the FK constraints. In this case tM_PolProd would be considered the master; tM_RepRatev2 the child.
CREATE TABLE [dbo].[tM_RepRateV2](
[RR_ID] [smallint] IDENTITY(1,1) NOT NULL,
[PP_ID] [smallint] NOT NULL,
[RP_ID] [smallint] NOT NULL,
[CS_ID] [smallint] NOT NULL,
[RR_Yr1Rate] [decimal](6, 2) NOT NULL CONSTRAINT [DF_tM_RepRateV2_RR_Yr1Rate] DEFAULT ((0)),
[RR_RenewRate] [decimal](6, 2) NOT NULL,
CONSTRAINT [PK_tM_RepRateV2] PRIMARY KEY CLUSTERED
(
[RR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tM_PolProd](
[PP_ID] [smallint] IDENTITY(1,1) NOT NULL,
[PO_ID] [smallint] NOT NULL,
[PR_ID] [smallint] NOT NULL,
[CM_ID] [smallint] NOT NULL,
[PP_ComRate] [smallint] NULL,
[PP_MonComm] [money] NULL,
[PP_Yr1Allocn] [money] NOT NULL CONSTRAINT [DF_tM_PolProd_PP_Allocn] DEFAULT ((0)),
[PP_RnwAllocn] [money] NOT NULL CONSTRAINT [DF_tM_PolProd_PP_RnwAllocn] DEFAULT ((0)),
[PP_CreateDt] [date] NULL,
[PP_CreateBy] [smallint] NULL,
[PP_ModDt] [date] NULL,
[PP_ModBy] [smallint] NULL,
CONSTRAINT [PK_tM_PolProd] PRIMARY KEY CLUSTERED
(
[PP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Full code of stored procedure being called by the trigger.
ALTER PROCEDURE [dbo].[usp_PP_Allocn]
@PP_IDParmSMALLINT,--Policy Product Key
@RateNVARCHAR(1),--Rate to calculate (Y = Year 1; Else Renewal)
@AllocMONEYOUTPUT
AS
BEGIN
SET NOCOUNT ON
--Cursor variables
DECLARE @CS_IDSMALLINT
DECLARE @Yr1RateDECIMAL(6,2)
DECLARE @RnwRateDECIMAL(6,2)
--Local variables
DECLARE @BaseCommMONEY= 1000.0000
DECLARE @RemCommMONEY= 1000.0000
DECLARE @CommRateDECIMAL(6,2)
IF EXISTS (SELECT name FROM sys.dm_exec_cursors(0) WHERE name='crsr_RepRates')
BEGIN
CLOSE crsr_RepRates
DEALLOCATE crsr_RepRates
END
SELECT @Alloc = 0.0000
DECLARE crsr_RepRates CURSOR FOR
SELECT
CS_ID,
RR_Yr1Rate,
RR_RenewRate
FROM tM_RepRateV2
WHERE PP_ID = @PP_IDParm
OPEN crsr_RepRates
FETCH NEXT FROM crsr_RepRates INTO
@CS_ID,
@Yr1Rate,
@RnwRate
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Rate = 'Y'
SELECT @CommRate = @Yr1Rate / 100.0000
ELSE
SELECT @CommRate = @RnwRate / 100.0000
IF @CS_ID = 1--Pre-GPS
BEGIN
SET @Alloc= @BaseComm * @CommRate-- Calculate the commission paid amount
SET @RemComm= @BaseComm - @Alloc-- Calculate comm rec'd amount remaining
END
IF @CS_ID = 2--GPS
BEGIN
SET @Alloc= @Alloc + (@RemComm * @CommRate)-- Calculate the commission paid amount
SET @RemComm= @BaseComm - @Alloc-- Calculate comm rec'd amount remaining
END
IF @CS_ID = 3--Post-GPS
BEGIN
SET @Alloc= @Alloc +(@RemComm * @CommRate)-- Calculate the commission paid amount
END
FETCH NEXT FROM crsr_RepRates INTO
@CS_ID,
@Yr1Rate,
@RnwRate
END
RETURN @Alloc
SET NOCOUNT OFF
CLOSE crsr_RepRates
DEALLOCATE crsr_RepRates
END
January 18, 2016 at 1:46 pm
Thanks for that, but you did not provide all the elements I asked for. Could you also provide INSERT statements with sample data, a sample DELETE statement to set off the trigger, the expected results after the trigger finishes, and an explanation of the logic used in the computation of PP_Yr1Allocn and PP_RnwAllocn.
Also, though not related to the problem you report, this line of code in the trigger is an error waiting to happen:
DECLARE @PP_ID SMALLINT = (SELECT PP_ID FROM deleted)
A trigger fires ones per statement, not once per row. So if you perform a DELETE statement that deletes five rows, the trigger will execute just once. This statement will more or less randomly select one of the PP_ID values from the list of five; the other four will never be processed by your trigger.
January 18, 2016 at 5:48 pm
Delete is controlled by the application and there will only be one record deleted at a time.
Only one user uses this function so multiple concurrent deletes are unlikely, if not impossible.
When an Insert, Update or Delete is performed, the expected result is that PP_RnwAllocn and PP_Yr1Allocn in tM_PolProd would be updated based on the calculations of the allocations in tM_RepRatev2, based on a commission of $1,000.
For the logic in calculation. There are 3 commission classifications (CS_ID):
1) Pre-company (external referral source(s));
2) Company share (usually 35%); and
3) employee allocation with between 1 to 3 allocations at this time.
1) Pre-company allocation is based on the full commission amount (in these samples $1,000). If there is 1 referral source @ 10%, they would get $100.
2) Company share allocation is based on the commission remaining after Pre-company allocation (so in example above, company share would be 35% of $900 or $315)
3) Employee share allocation is based on the commission remaining after 1) and 2) so in the example above, commission remaining is $585. Each employee with a commission classification of 3 (CS_ID = 3 in the code), gets their share. The percentages may vary
To create some test data then
USE [test]
GO
drop table [dbo].[tM_PolProd]
drop table [dbo].[tM_RepRateV2]
GO
CREATE TABLE [dbo].[tM_PolProd](
[PP_ID] [smallint] IDENTITY(1,1) NOT NULL,
[PO_ID] [smallint] NOT NULL,
[PR_ID] [smallint] NOT NULL,
[CM_ID] [smallint] NOT NULL,
[PP_Yr1Allocn] [money] NOT NULL CONSTRAINT [DF_tM_PolProd_PP_Allocn] DEFAULT ((0)),
[PP_RnwAllocn] [money] NOT NULL CONSTRAINT [DF_tM_PolProd_PP_RnwAllocn] DEFAULT ((0)),
CONSTRAINT [PK_tM_PolProd] PRIMARY KEY CLUSTERED
(
[PP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[tM_PolProd] (PO_ID, PR_ID, CM_ID, PP_Yr1Allocn, PP_RnwAllocn) VALUES (1,2,3,0,0)
INSERT INTO [dbo].[tM_PolProd] (PO_ID, PR_ID, CM_ID, PP_Yr1Allocn, PP_RnwAllocn) VALUES (2,3,4,0,0)
CREATE TABLE [dbo].[tM_RepRateV2](
[RR_ID] [smallint] IDENTITY(1,1) NOT NULL,
[PP_ID] [smallint] NOT NULL,
[RP_ID] [smallint] NOT NULL,
[CS_ID] [smallint] NOT NULL,
[RR_Yr1Rate] [decimal](6, 2) NOT NULL CONSTRAINT [DF_tM_RepRateV2_RR_Yr1Rate] DEFAULT ((0)),
[RR_RenewRate] [decimal](6, 2) NOT NULL,
CONSTRAINT [PK_tM_RepRateV2] PRIMARY KEY CLUSTERED
(
[RR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[tM_RepRateV2] (PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate) VALUES (1,4,1, 10,10)
INSERT INTO [dbo].[tM_RepRateV2] (PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate) VALUES (1,1,2, 35,35)
INSERT INTO [dbo].[tM_RepRateV2] (PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate) VALUES (1,2,3, 50,50)
INSERT INTO [dbo].[tM_RepRateV2] (PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate) VALUES (1,3,3, 50,50)
INSERT INTO [dbo].[tM_RepRateV2] (PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate) VALUES (1,3,3, 20,20)
SELECT * FROM [dbo].[tM_PolProd]
SELECT * FROM [dbo].[tM_RepRateV2]
GO
This will result the following:
tM_PolProd display
PP_IDPO_IDPR_IDCM_IDPP_Yr1AllocnPP_RnwAllocn
1 1 2 3 1117 1117
tM_RepRateV2 display
RR_IDPP_IDRP_IDCS_IDRR_Yr1RateRR_RenewRate
5 1 4 1 10 10
6 1 1 2 35 35
7 1 2 3 50 50
8 1 3 3 50 50
9 1 3 3 20 20
If you then delete tM_RepRateV2 where RR_ID = 9 you should have the tM_PolProd recalculated to display
PP_IDPO_IDPR_IDCM_IDPP_Yr1AllocnPP_RnwAllocn
1 1 2 3 1000 1000
January 19, 2016 at 4:11 pm
Just a quick note to tell you that I did see the message. But I won't be able to look at it right now. Car broke down when I wanted to return from the office to home, and it took until 10:30PM before I finally was home. Need to sleep now.
January 20, 2016 at 12:39 pm
Good, I am working on this now, and I hope that I can post some ideas shortly. But first some first observations.
1. I am so happy to see a post with code that I can copy and paste and that then just works, without errors. So a huge thanks for that! 😉
2. The procedure starts with some code that checks if a cursor exists, and if so closes and deallocates it. This is not a normal coding pattern, and since the procedure ends by closing and deacllocating the cursor, should not be needed. I wonder if you added it because you had other errors first, or whether this is the result of "code by copy and paste" because your predecessor needed this once and thought it was handy to use all the time?
(If you used it to fix a previous issue where you got an error that the cursor already existed, then the issue you have now is a direct result of the "fix" - the code complained that the cursor already exists and instead of finding out why you just brute-force your way through - without considering the consequences of that for who- or what-ever was using that other version of the cursor)
3. Even when the error you encountered does not apply, the code you posted is not guaranteed to work correct. You have no ORDER BY on the cursor, which means that the rows it selects can be returned in any order. Yet, the rest of the cursor appears to assume that they will be processed in "proper" order: Pre-GPS first, then GPS, and Post-GPS last. With the current code I expect that this will indeed usually be the case, but it is not guaranteed.
4. It is not clear from your posts whether there will be always exactly one CS_ID 1 and exactly one CS_ID 2 row for a given PP_ID, nor whether there will be always at least one CS_ID 3 row. I will assume always 1 Pre-GPS, always 1 GPS, and always at least 1 Post-GPS. If that assumption is false, the code I try to write might be wrong.
January 20, 2016 at 1:41 pm
It is indeed possible to do this calculation without a cursor. I tried a few approaches and stuck with the one below. I think it's relatively easy to understand, and that you can reuse chunks of it for other purposes if you decide to change your architecture.
Speaking of which - I wonder how often the data changes (so that commissions need to be recomputed) versus how often the computed commissions are queried. Depending on that ratio, you might consider not storing the commission at all but computing it on the spot when needed, e.g. by encapsulating the logic for the computation in a view.
Anyway, on to the solution. Because I want you to understand it, I will show you the steps I took to get there. Step one was to compute the commission for each individual row, and for this I decided to use a recursive CTE. A lot of people don't like them and there are valid reasons for that, but it is a tool in our toolbelt to be used when appropriate.
WITH CTE_Recursive
AS (SELECT *,
CAST(1000.0 AS decimal(9,2)) AS BaseYr1,
CAST(1000.0 AS decimal(9,2)) AS BaseRnw,
CAST(1000.0 * RR_Yr1Rate / 100.0 AS decimal(9,2)) AS CommYr1,
CAST(1000.0 * RR_RenewRate / 100.0 AS decimal(9,2)) AS CommRnw
FROM dbo.tM_RepRateV2
WHERE CS_ID = 1
UNION ALL
SELECT r.*,
CAST(c.BaseYr1 - c.CommYr1 AS decimal(9,2)),
CAST(c.BaseRnw - c.CommRnw AS decimal(9,2)),
CAST((c.BaseYr1 - c.CommYr1) * r.RR_Yr1Rate / 100.0 AS decimal(9,2)),
CAST((c.BaseRnw - c.CommRnw) * r.RR_RenewRate / 100.0 AS decimal(9,2))
FROM CTE_Recursive AS c
INNER JOIN dbo.tM_RepRateV2 AS r
ON r.PP_ID = c.PP_ID
AND r.CS_ID = c.CS_ID + 1)
SELECT * -- For demo code only, never use SELECT * in production
FROM CTE_Recursive;
For the PolProd table, you need the total commission per PP_ID, so let's use this same CTE but now add on some aggregation to get that:
WITH CTE_Recursive
AS (SELECT *,
CAST(1000.0 AS decimal(9,2)) AS BaseYr1,
CAST(1000.0 AS decimal(9,2)) AS BaseRnw,
CAST(1000.0 * RR_Yr1Rate / 100.0 AS decimal(9,2)) AS CommYr1,
CAST(1000.0 * RR_RenewRate / 100.0 AS decimal(9,2)) AS CommRnw
FROM dbo.tM_RepRateV2
WHERE CS_ID = 1
UNION ALL
SELECT r.*,
CAST(c.BaseYr1 - c.CommYr1 AS decimal(9,2)),
CAST(c.BaseRnw - c.CommRnw AS decimal(9,2)),
CAST((c.BaseYr1 - c.CommYr1) * r.RR_Yr1Rate / 100.0 AS decimal(9,2)),
CAST((c.BaseRnw - c.CommRnw) * r.RR_RenewRate / 100.0 AS decimal(9,2))
FROM CTE_Recursive AS c
INNER JOIN dbo.tM_RepRateV2 AS r
ON r.PP_ID = c.PP_ID
AND r.CS_ID = c.CS_ID + 1)
SELECT PP_ID, SUM(CommYr1) AS TotCommYr1, SUM(CommRnw) AS TotCommRnw
FROM CTE_Recursive
GROUP BY PP_ID
ORDER BY PP_ID;
(Note that the above, but without the ORDER BY, would be the body of the view you could use if you decide to not store but compute on the spot the commissions)
The final step is to plug this computation into a trigger. So I embed the aggregation in a second CTE, join that to the PolProd table in an UPDATE (note that there are a lot of potential problems with joins in an UPDATE; only use this if you are aware of all the ramifications!), and then use an EXISTS to restrict this to PP_ID values that were affected by the DELETE statement:
CREATE TRIGGER [dbo].[RecalcOnDelete]
ON [dbo].[tM_RepRateV2]
AFTER DELETE
AS
BEGIN;
SET NOCOUNT ON;
WITH
CommissionPerRow AS
(SELECT *,
CAST(1000.0 AS decimal(9,2)) AS BaseYr1,
CAST(1000.0 AS decimal(9,2)) AS BaseRnw,
CAST(1000.0 * RR_Yr1Rate / 100.0 AS decimal(9,2)) AS CommYr1,
CAST(1000.0 * RR_RenewRate / 100.0 AS decimal(9,2)) AS CommRnw
FROM dbo.tM_RepRateV2
WHERE CS_ID = 1
UNION ALL
SELECT r.*,
CAST(c.BaseYr1 - c.CommYr1 AS decimal(9,2)),
CAST(c.BaseRnw - c.CommRnw AS decimal(9,2)),
CAST((c.BaseYr1 - c.CommYr1) * r.RR_Yr1Rate / 100.0 AS decimal(9,2)),
CAST((c.BaseRnw - c.CommRnw) * r.RR_RenewRate / 100.0 AS decimal(9,2))
FROM CommissionPerRow AS c
INNER JOIN dbo.tM_RepRateV2 AS r
ON r.PP_ID = c.PP_ID
AND r.CS_ID = c.CS_ID + 1
),
TotCommPerPP AS
(SELECT PP_ID, SUM(CommYr1) AS TotCommYr1, SUM(CommRnw) AS TotCommRnw
FROM CommissionPerRow
GROUP BY PP_ID)
UPDATE dbo.tM_PolProd
SET PP_Yr1Allocn = t.TotCommYr1,
PP_RnwAllocn = t.TotCommRnw
FROM TotCommPerPP AS t
WHERE t.PP_ID = dbo.tM_PolProd.PP_ID
AND EXISTS
(SELECT *
FROM deleted AS d
WHERE d.PP_ID = dbo.tM_PolProd.PP_ID);
END;
Note that as a bonus, this version of the trigger will also work correctly if you do a DELETE that affects multiple rows at once, or if you do a DELETE that affects no rows.
I do not expect this trigger to return valuable results if for a given PP_ID there are zero or more than one pre-GPS or GPS row. But I have not tested this (especially because I have no idea what results would be correct in such a case).
January 21, 2016 at 5:18 am
Thanks. I'll look at this in detail.
As to your comments from the previous post:
1) The deletion of the cursor was a practice from a previous DBA so I just maintained it.
2) I found the error with missing order by and had incorporated afterwards
3) The CS_ID values are assigned to the sales reps. As for existence of CS_ID types:
CS_ID = 1 will occur 0 to n times. I haven't seen it occur more than twice.
CS_ID = 2 will occur 0 (rarely) to 1 time (unless they miss assigned it)
CS_ID = 3 should occur 1 to n times, right now maximum is 3, but I wouldn't want to constrain it to that.
4) The intent of the calculation in the tM_PolProd table is to be able to show the user if they've allocated the commissions correctly. Based the value calculated, the application displays the values in different colours to more easily show which child tables need to be updated. The calculation is also used when commissions are received. The data is then recorded in a Commission Paid table.
January 21, 2016 at 5:36 am
Based on your reply to #3, my posted question is probably going to be wrong. I'll have to revise it (when I have time).
Can you help me by posting some extra sample data showing various scenarios of 0, 1, or more than 1 of each of the CS_ID types, and the expected result for each of those?
January 21, 2016 at 6:50 am
I've put the table creation back in case you dropped them and to make relating a bit easier
USE [test]
GO
/****** Object: Table [dbo].[tM_PolProd] Script Date: 1/21/2016 8:01:10 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE [dbo].[tM_PolProd]
GO
CREATE TABLE [dbo].[tM_PolProd](
[PP_ID] [smallint] NOT NULL,
[PO_ID] [smallint] NOT NULL,
[PR_ID] [smallint] NOT NULL,
[CM_ID] [smallint] NOT NULL,
[PP_Yr1Allocn] [money] NOT NULL,
[PP_RnwAllocn] [money] NOT NULL,
CONSTRAINT [PK_tM_PolProd] PRIMARY KEY CLUSTERED
(
[PP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tM_PolProd] ADD CONSTRAINT [DF_tM_PolProd_PP_Allocn] DEFAULT ((0)) FOR [PP_Yr1Allocn]
GO
ALTER TABLE [dbo].[tM_PolProd] ADD CONSTRAINT [DF_tM_PolProd_PP_RnwAllocn] DEFAULT ((0)) FOR [PP_RnwAllocn]
GO
/****** Object: Table [dbo].[tM_RepRateV2] Script Date: 1/21/2016 8:02:59 AM ******/
DROP TABLE [dbo].[tM_RepRateV2]
GO
/****** Object: Table [dbo].[tM_RepRateV2] Script Date: 1/21/2016 8:02:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tM_RepRateV2](
[RR_ID] [smallint] IDENTITY(1,1) NOT NULL,
[PP_ID] [smallint] NOT NULL,
[RP_ID] [smallint] NOT NULL,
[CS_ID] [smallint] NOT NULL,
[RR_Yr1Rate] [decimal](6, 2) NOT NULL,
[RR_RenewRate] [decimal](6, 2) NOT NULL,
CONSTRAINT [PK_tM_RepRateV2] PRIMARY KEY CLUSTERED
(
[RR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
select * from tM_PolProd
select * from tM_RepRateV2
GO
INSERT INTO [dbo].[tM_PolProd](PP_ID, PO_ID, PR_ID, CM_ID, PP_Yr1Allocn, PP_RnwAllocn)VALUES (1, 1, 2, 3, 0, 0)--No RepRateV2 records
INSERT INTO [dbo].[tM_PolProd](PP_ID, PO_ID, PR_ID, CM_ID, PP_Yr1Allocn, PP_RnwAllocn)VALUES (2, 2, 3, 4, 0, 0)--1 x CS_ID = 1; 1 x CS_ID = 2; 0 x CS_ID = 3
INSERT INTO [dbo].[tM_PolProd](PP_ID, PO_ID, PR_ID, CM_ID, PP_Yr1Allocn, PP_RnwAllocn)VALUES (3, 3, 3, 4, 0, 0)--0 x CS_ID = 1; 1 x CS_ID = 2; 0 x CS_ID = 3
INSERT INTO [dbo].[tM_PolProd](PP_ID, PO_ID, PR_ID, CM_ID, PP_Yr1Allocn, PP_RnwAllocn)VALUES (4, 4, 3, 4, 0, 0)--1 x CS_ID = 1; 1 x CS_ID = 2; 0 x CS_ID = 3
INSERT INTO [dbo].[tM_PolProd](PP_ID, PO_ID, PR_ID, CM_ID, PP_Yr1Allocn, PP_RnwAllocn)VALUES (5, 5, 3, 4, 0, 0)--2 x CS_ID = 1; 1 x CS_ID = 2; 2 x CS_ID = 3
INSERT INTO [dbo].[tM_PolProd](PP_ID, PO_ID, PR_ID, CM_ID, PP_Yr1Allocn, PP_RnwAllocn)VALUES (6, 6, 3, 4, 0, 0)--0 x CS_ID = 1; 1 x CS_ID = 2; 3 x CS_ID = 3
INSERT INTO [dbo].[tM_PolProd](PP_ID, PO_ID, PR_ID, CM_ID, PP_Yr1Allocn, PP_RnwAllocn)VALUES (7, 5, 4, 5, 0, 0)--1 x CS_ID = 1; 0 x CS_ID = 2; 2 x CS_ID = 3
select * from tM_PolProd
GO
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (2, 10, 1, 10, 10)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (2, 1, 2, 35, 35)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (3, 1, 1, 35, 35)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (4, 10, 1, 8, 8)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (4, 1, 2, 35, 35)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (5, 10, 1, 8, 8)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (5, 11, 1, 5, 5)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (5, 1, 2, 35, 35)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (5, 2, 3, 50, 50)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (5, 3, 3, 50, 50)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (6, 1, 2, 35, 35)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (6, 2, 3, 50, 50)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (6, 3, 3, 50, 50)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (6, 4, 3, 20, 20)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (7, 10, 1, 8, 8)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (7, 2, 3, 50, 50)
INSERT INTO [dbo].[tM_RepRateV2](PP_ID, RP_ID, CS_ID, RR_Yr1Rate, RR_RenewRate)VALUES (7, 3, 3, 50, 50)
GO
select * from tM_PolProd
select * from tM_RepRateV2
After the above insert, the tables should look like the attachment.
If you were to delete, this tM_RepRateV2 WHERE RR_ID = 14, the values in tM_PolProd for PP_ID = 6 should both be 1000.
The idea is that this is an audit check. If the allocations in tM_RepRateV2 is done correctly, the PP_Yr1Allocn and PP_RnwAllocn should be 1000. Anything more or less, the user needs to correct before the process actual commissions.
January 31, 2016 at 2:46 pm
First, my apologies for not coming back to you sooner. I can make all kinds of excuses, but you were waiting for me and I should not have kept you waiting for so long. My bad.
Second, I think I found a way to do the calculation as requested. This is even more flexible than you described: any of the CS levels 1, 2, and 3 can be present 0, 1, or more than 1 time, and it should always return the correct results. The code did become quite complex and if you are using this for deletes (or updates or inserts) of massive amounts of rows in massive tables, then I do not know if it will perform well. (But in such a case, the cursor you use now will probably not perform well either).
CREATE TRIGGER [dbo].[RecalcOnDelete]
ON [dbo].[tM_RepRateV2]
AFTER DELETE
AS
BEGIN;
SET NOCOUNT ON;
WITH
ALL_CS_Values AS
(SELECT CAST(1 AS smallint) AS CS_ID
UNION ALL
SELECT 2
UNION ALL
SELECT 3),
ALL_PP_Values AS
(SELECT DISTINCT PP_ID
FROM dbo.tM_PolProd),
RepRate_Per_CS_PP AS
(SELECT p.PP_ID, c.CS_ID,
SUM(COALESCE(RR_Yr1Rate, 0)) AS SumYr1Rate,
SUM(COALESCE(RR_RenewRate, 0)) AS SumRnwRate
FROM ALL_CS_Values AS c
CROSS JOIN ALL_PP_Values AS p
LEFT JOIN dbo.tM_RepRateV2 AS r
ON r.PP_ID = p.PP_ID
AND r.CS_ID = c.CS_ID
GROUP BY p.PP_ID, c.CS_ID),
CommissionPerCS AS
(SELECT PP_ID,
0 AS CS_ID,
CAST(1000.0 AS decimal(9,2)) AS BaseYr1,
CAST(1000.0 AS decimal(9,2)) AS BaseRnw,
CAST(0.0 AS decimal(9,2)) AS CommYr1,
CAST(0.0 AS decimal(9,2)) AS CommRnw
FROM ALL_PP_Values
UNION ALL
SELECT r.PP_ID,
r.CS_ID,
CAST(c.BaseYr1 - c.CommYr1 AS decimal(9,2)),
CAST(c.BaseRnw - c.CommRnw AS decimal(9,2)),
CAST((c.BaseYr1 - c.CommYr1) * COALESCE(r.SumYr1Rate, 0) / 100.0 AS decimal(9,2)),
CAST((c.BaseRnw - c.CommRnw) * COALESCE(r.SumRnwRate, 0) / 100.0 AS decimal(9,2))
FROM CommissionPerCS AS c
INNER JOIN RepRate_Per_CS_PP AS r
ON r.PP_ID = c.PP_ID
AND r.CS_ID = c.CS_ID + 1),
TotCommPerPP AS
(SELECT PP_ID, SUM(CommYr1) AS TotCommYr1, SUM(CommRnw) AS TotCommRnw
FROM CommissionPerCS
GROUP BY PP_ID)
UPDATE dbo.tM_PolProd
SET PP_Yr1Allocn = t.TotCommYr1,
PP_RnwAllocn = t.TotCommRnw
FROM TotCommPerPP AS t
WHERE t.PP_ID = dbo.tM_PolProd.PP_ID
AND EXISTS
(SELECT *
FROM deleted AS d
WHERE d.PP_ID = dbo.tM_PolProd.PP_ID);
END;
GO
I also reailzed (unfortunately after doing all the work on the query above) that there is a much simpler way to test your requirement. The total will always be 1000 when these two conditions are met:
1. The sum of all combined rates for any PP_ID / CS_ID combination should never be more than 100%.
2. The sum of all combined rates for any PP-ID / CS-ID combination should always be exactly 100% for the highest CS_ID used for that PP_ID. (Based on your description that should always be CS_ID 3).
February 18, 2016 at 12:10 pm
My turn to apologize now. I was on vacation when you posted the answer. I attempted to post a reply at that time but it looks like it didn't make it.
This solution works great.
Thanks.
February 18, 2016 at 12:49 pm
Thanks for posting back. I am glad that this works for you!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply