I broke a query - why?

1

Here's my before query which works, albeit not best performance:

DECLARE @sd AS VARCHAR (8) = '20150713'
DECLARE @ed AS VARCHAR (8) = CONVERT (VARCHAR, GETDATE(), 112)
DECLARE @sql NVARCHAR (MAX)

WHILE @sd < @ed
BEGIN

SET @sql = 
(
'SELECT TransactionDate, [HierarchyKey], Insurer, SUM (Polcount) AS CountX, SUM (GWP)
FROM
(
SELECT @sd AS TransactionDate, [HierarchyKey], ''CDL1_'' + [InsurerCode] AS Insurer, COUNT (DISTINCT PolicyReference) AS Polcount, (-TransactionValue) AS GWP
FROM [CDL].[PolicyBoard]
WHERE PolicyReference NOT IN (SELECT PolicyReference FROM [CDL].[PolicyBoard_Reversed_Cancellations] WHERE TransactionDate <= @sd GROUP BY PolicyReference HAVING SUM (TOTAL) <> 0)
AND ComponentType = ''Policy''
AND ActivityType = ''GWP''
AND TransactionTypeCode IN (''NEWBUS'', ''REN'', ''RENALT'')
AND TransactionDate <= @sd
GROUP BY TransactionDate, [HierarchyKey], InsurerCode, TransactionValue
) x

GROUP BY TransactionDate, [HierarchyKey], Insurer'
)

INSERT INTO CDL.PolicyBoard_Live_Policy_Metrics EXEC sp_executesql @sql, N'@sd INT OUT', @sd OUT

SET @sd = CASE WHEN @sd IN ('20150731', '20150831', '20151031', '20160131', '20160331', '20160531', '20160731', '20160831', '20161031') 
THEN @sd + 70
WHEN @sd IN ('20150930', '20151130', '20160430', '20160630', '20160930', '20161130') THEN @sd + 71
WHEN @sd IN ('20160229') THEN @sd + 72
WHEN @sd IN ('20151231', '20161231') THEN @sd + 8870
ELSE 
@sd + 1
END
END

My after query fails, telling me I have to declare variable @yd:

DECLARE @sd AS VARCHAR (8) = '20150713'
DECLARE @yd AS VARCHAR (8) = '20150713'
DECLARE @ed AS VARCHAR (8) = CONVERT (VARCHAR, GETDATE(), 112)
DECLARE @sql NVARCHAR (MAX)

WHILE @sd < @ed
BEGIN

SET @sql = 
(
'SELECT TransactionDate, [HierarchyKey], Insurer, SUM (Polcount) AS CountX, SUM (GWP)
FROM
(
SELECT @sd AS TransactionDate, [HierarchyKey], ''CDL1_'' + [InsurerCode] AS Insurer, COUNT (DISTINCT PolicyReference) AS Polcount, (-TransactionValue) AS GWP
FROM [CDL].[PolicyBoard]
WHERE PolicyReference NOT IN (SELECT PolicyReference FROM [CDL].[PolicyBoard_Reversed_Cancellations] WHERE TransactionDate <= @sd GROUP BY PolicyReference HAVING SUM (TOTAL) <> 0)
AND ComponentType = ''Policy''
AND ActivityType = ''GWP''
AND TransactionTypeCode IN (''NEWBUS'', ''REN'', ''RENALT'')
AND TransactionDate >= @yd
AND TransactionDate <= @sd
GROUP BY TransactionDate, [HierarchyKey], InsurerCode, TransactionValue
) x

GROUP BY TransactionDate, [HierarchyKey], Insurer'
)

INSERT INTO CDL.PolicyBoard_Live_Policy_Metrics EXEC sp_executesql @sql, N'@sd INT OUT', @sd OUT

SET @sd = CASE WHEN @sd IN ('20150731', '20150831', '20151031', '20160131', '20160331', '20160531', '20160731', '20160831', '20161031') 
THEN @sd + 70
WHEN @sd IN ('20150930', '20151130', '20160430', '20160630', '20160930', '20161130') THEN @sd + 71
WHEN @sd IN ('20160229') THEN @sd + 72
WHEN @sd IN ('20151231', '20161231') THEN @sd + 8870
ELSE 
@sd + 1
END
SET @yd = CASE WHEN @sd IN ('20160229') 
THEN @sd - 9928 
ELSE @sd - 10000
END
END

What am I missing?

As always, thanks in advance for your words of wisdom.

Incidentally, if anyone can improve my code, please share the love :slight_smile:

3

How is field TransactionDate defined in tables PolicyBoard and PolicyBoard_Reversed_Cancellations?

1 Like
4 
INSERT INTO CDL.PolicyBoard_Live_Policy_Metrics
EXEC sp_executesql @sql
    --,N'@sd int OUTPUT, @yd int OUTPUT' -- if @yd is an int
    ,N'@sd int OUTPUT, @yd date OUTPUT' -- if @yd is a date
    ,@sd OUTPUT
    ,@yd OUTPUT;
1 Like
5

Thanks Ifor, works a treat.