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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply