February 20, 2018 at 2:56 pm
The Update statement in last part of my query (updates columns [Net Losses Prev 12 Months] and [Risk Grading]),
takes 20 seconds for 33 records, for over 100 000 it takes forever. So looking for a faster solution, guess CTE would probably do the trick. Please run the attached queries in the order listed.
QUERY:
DECLARE
@i INT = 1
,@IncidentValueRecordCount INT
,@NetLossesListPrev12Months [dbo].[Net Losses List]
,@NetLossesSumPrev12Months FLOAT;
SELECT @IncidentValueRecordCount = MAX([Record Loop ID]) FROM [dbo].[Mytable];
WHILE @i <= @IncidentValueRecordCount
BEGIN
DELETE FROM @NetLossesListPrev12Months;
INSERT INTO @NetLossesListPrev12Months
SELECT
A.[Net Losses]
FROM
[dbo].[Mytable] A
,(
SELECT *
FROM [dbo].[Mytable]
WHERE [Record Loop ID] = @i
) B
WHERE A.[ADM_RISK_GRADING_ORG_UNIT_ID] = B.[ADM_RISK_GRADING_ORG_UNIT_ID]
AND A.[ADM_EVENT_TYPE_ID] = B.[ADM_EVENT_TYPE_ID]
AND A.[ADM_CAUSE_TYPE_ID] = B.[ADM_CAUSE_TYPE_ID]
AND A.[Effect Date YYYYMM] >= B.[Effect Date YYYYMM] - 100
AND A.[Effect Date YYYYMM] < B.[Effect Date YYYYMM];
WITH A AS
(
SELECT
ISNULL(SUM([Net Loss]),0) AS [Net Losses Prev 12 Months]
FROM @NetLossesListPrev12Months
)
SELECT
@NetLossesSumPrev12Months =
CASE
WHEN ABS([Net Losses Prev 12 Months]) < 0.0001 THEN 0
ELSE [Net Losses Prev 12 Months]
END
FROM A;
UPDATE [dbo].[Mytable]
SET
[Net Losses Prev 12 Months] = @NetLossesSumPrev12Months
,[Risk Grading] =
CASE
WHEN @NetLossesSumPrev12Months = 0 AND [Net Losses] = 0
THEN NULL
WHEN [Net Losses] <= [dbo].[Percentile](@NetLossesListPrev12Months, 0.2)
THEN 1
WHEN [Net Losses] <= [dbo].[Percentile](@NetLossesListPrev12Months, 0.4)
THEN 2
WHEN [Net Losses] <= [dbo].[Percentile](@NetLossesListPrev12Months, 0.6)
THEN 3
WHEN [Net Losses] <= [dbo].[Percentile](@NetLossesListPrev12Months, 0.8)
THEN 4
ELSE 5
END
WHERE [Record Loop ID] = @i;
SET @i = @i + 1;
END;
February 20, 2018 at 3:08 pm
It looks like the following will work.
SELECT SUM([Net Loss]) OVER( PARTITION BY ADM_RISK_GRADING_ORG_UNIT_ID, ADM_EVENT_TYPE_ID, ADM_CAUSE_TYPE_ID ORDER BY [Effect Date YYYYMM] ROWS BETWEEN 12 PRECEDING AND 1 PRECEDING
FROM dbo.MyTable
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 21, 2018 at 8:23 am
Thanks, works for column Net Losses Prev 12 months,
Now to get it working for Risk Grading column. Can you help me?
February 21, 2018 at 11:34 am
kevin_nikolai - Wednesday, February 21, 2018 8:23 AMThanks, works for column Net Losses Prev 12 months,
Now to get it working for Risk Grading column. Can you help me?
Once you understand the formula, it's a rather obvious extension. What have you tried?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 21, 2018 at 4:18 pm
Hi Drew, tried this ........
ALTER PROC MyTest1
AS
DECLARE
@i INT = 1
,@IncidentValueRecordCount INT
,@NetLossesListPrev12Months [dbo].[Net Losses List]
,@NetLossesSumPrev12Months FLOAT
SELECT @IncidentValueRecordCount = MAX([Record Loop ID]) FROM [dbo].[Mytable];
WHILE @i <= @IncidentValueRecordCount
BEGIN
DELETE FROM @NetLossesListPrev12Months;
INSERT INTO @NetLossesListPrev12Months
SELECT
A.[Net Losses]
FROM
[dbo].[Mytable] A
,(
SELECT *
FROM [dbo].[Mytable]
WHERE [Record Loop ID] = @i
) B
WHERE A.[ADM_RISK_GRADING_ORG_UNIT_ID] = B.[ADM_RISK_GRADING_ORG_UNIT_ID]
AND A.[ADM_EVENT_TYPE_ID] = B.[ADM_EVENT_TYPE_ID]
AND A.[ADM_CAUSE_TYPE_ID] = B.[ADM_CAUSE_TYPE_ID]
AND A.[Effect Date YYYYMM] >= B.[Effect Date YYYYMM] - 100
AND A.[Effect Date YYYYMM] < B.[Effect Date YYYYMM];
SET @i = @i + 1;
END
--------------------------------------------------------------------
SELECT
A.[Record Loop ID],
A.[Net Losses],
sum(A.[Net Losses]) over (partition by A.[ADM_RISK_GRADING_ORG_UNIT_ID],A.[ADM_EVENT_TYPE_ID],A.[ADM_CAUSE_TYPE_ID] order by A.[Effect Date YYYYMM] rows between 12 preceding and 1 preceding) [Net Losses Prev 12 Months]
INTO #A
FROM [dbo].[Mytable] A
SELECT
[Record Loop ID],
[Net Losses],
[Net Losses Prev 12 Months],
[Risk Grading] =
CASE
WHEN [Net Losses] = 0
THEN NULL
WHEN [Net Losses] <= [dbo].[Percentile](@NetLossesListPrev12Months, 0.2)
THEN 1
WHEN [Net Losses] <= [dbo].[Percentile](@NetLossesListPrev12Months, 0.4)
THEN 2
WHEN [Net Losses] <= [dbo].[Percentile](@NetLossesListPrev12Months, 0.6)
THEN 3
WHEN [Net Losses] <= [dbo].[Percentile](@NetLossesListPrev12Months, 0.8)
THEN 4
ELSE 5
END INTO #B
FROM #A
WHERE [Record Loop ID] between 109171 and 109182
ORDER BY [Record Loop ID]
---------------------------------------------------
UPDATE FROM [dbo].[Mytable] SET
[Net Losses Prev 12 Months] = C.[Net Losses Prev 12 Months]
,[Risk Grading] = C.[Risk Grading]
FROM #B C
JOIN FROM [dbo].[Mytable] D on D.[Record Loop ID] = C.[Record Loop ID]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy