SQL - Getting Percent Difference from dynamic SQL

user_8447562 0
Viewed 124

I currently have a dynamic stored procedure that takes every month in my database and averages out the values per day.

What I currently have (values are the overall average for that month): 

+-------------------------------------------------------+
| ID | CustName | 201501 | 201502 | 201503 | 201504 | ..|
+-------------------------------------------------------+
| 32 | CustOne  | 5852.25| 5847.50| 6542.98| 7585.25| ..|
| 56 | CustTwo  | 5452.45| 7852.50| 6985.41| 1245.21| ..|
| 89 | CustThree| 8520.25| 7410.01| 9630.36| 1245.32| ..|
| .. | ...      |   ..   |   ..   |   ..   |   ..   | ..|
+-------------------------------------------------------+

This is the stored procedure I'm using to create the above table:

DECLARE @Dates NVARCHAR(MAX);

SELECT @Dates = CONCAT(@Dates + ', ', QUOTENAME(BalMonth))
FROM vAvgMonBal
GROUP BY BalMonth
ORDER BY BalMonth;

DECLARE @DynSQL NVARCHAR(MAX),
    @months NVARCHAR(MAX);

SET @months = 'CONCAT( CONVERT(nvarchar(15), YEAR(BalDate)) , IIF(LEN(MONTH(BalDate)) > 1, CONVERT(nvarchar(15), MONTH(BalDate)), ''0'' + CONVERT(nvarchar(15), MONTH(BalDate)))) AS BalMonth'
SET @DynSQL = 'SELECT *
           FROM
               (SELECT 
                    a1.IDNbr, 
                    a2.CustName, ' + @months + ',
                    AVG(a1.Balance) as Balance
                FROM tblID a1 
                INNER JOIN tblCust a2 ON (a1.IDNbr = a2.IDNbr)
                WHERE a2.CustType != ''Inactive''
                GROUP BY 
                    a1.IDNbr, a2.CustName, CONCAT( CONVERT(nvarchar(15), YEAR(BalDate)) , IIF(LEN(MONTH(BalDate)) > 1, CONVERT(nvarchar(15), MONTH(BalDate)), ''0'' + CONVERT(nvarchar(15), MONTH(BalDate))))) as d1
PIVOT (
AVG(Balance)
FOR BalMonth IN (' + @Dates + ')
) piv';


EXECUTE sp_executesql @DynSQL

Question: how can I take the data from the previous stored procedure and get the percent difference, (Month1/Month2) * 100, from it like shown below? I'm expecting I'll need a new stored procedure or add on to the one I currently have.

What I'm needing (where each "PerDiff" is the percent difference of the previous month from the before table example):

+---------------------------------------------------------------+
| ID | CustName | PerDiff1 | PerDiff2 | PerDiff3 | PerDiff4 | ..|
+---------------------------------------------------------------+
| 32 | CustOne  |  100.00  |   68.12  |  654.25  |  483.36  | ..|
| 56 | CustTwo  |   58.21  |  154.54  |  932.45  |   58.45  | ..|
| 89 | CustThree|  965.25  |  951.58  |  689.12  |   32.50  | ..|
| .. | ...      |   ....   |   ....   |   ....   |   ....   | ..|
+---------------------------------------------------------------+

I've attempted to use something like:

DECLARE @PerDiff nvarchar(max);

SET @PerDiff = 'SELECT *
FROM (' + @DynSQL + ')'

EXECUTE sp_executesql @PerDiff

To at least try and get the data moving.

I'm no longer getting any error messages-- I'm generally just stuck on how to proceed with getting the math to be applied dynamically.

Any help or advice would be greatly appreciated!

EDIT1: Here is the result of finalized @DynSQL 

SELECT *
FROM
(
SELECT a1.DDANbr, 
a2.CustName, 
CONCAT( CONVERT(nvarchar(15), YEAR(BalDate)) , IIF(LEN(MONTH(BalDate)) > 1, CONVERT(nvarchar(15), MONTH(BalDate)), '0' + CONVERT(nvarchar(15), MONTH(BalDate))))AS BalMonth,
a1.Balance

FROM tblID a1 INNER JOIN tblCust a2 ON (a1.IDNbr = a2.IDNbr)

WHERE a2.CustType != 'Inactive'

GROUP BY a1.IDNbr, a2.CustName, BalDate, a1.Balance
) as d1
PIVOT (
AVG(Balance)
FOR BalMonth IN ([201501], [201502], [201503], [201504], [201505], [201506], [201507], [201508], [201509], [201510], [201511], [201512], [201601], [201602], [201603], [201604], [201605], [201606], [201607], [201608], [201609], [201610], [201611], [201612], [201701], [201702], [201703], [201704], [201705], [201706], [201707], [201708], [201709], [201710], [201711], [201712], [201801], [201802], [201803], [201804], [201805], [201806], [201807], [201808], [201809])
) piv
sql-server sql
2 Answers

When you take a query and make it a derived table, you have to give that table an alias.

Like this:

DECLARE @PerDiff nvarchar(max);

SET @PerDiff = 'SELECT IDNbr, CustName, ' + @months + '
FROM (' + @DynSQL + ') t1'

EXECUTE sp_executesql @DynSQL, @PerDiff
user_15075660 
DECLARE @Dates NVARCHAR(MAX);

SELECT @Dates = CONCAT(@Dates + ', ', QUOTENAME(BalMonth))
FROM vAvgMonBal
GROUP BY BalMonth
ORDER BY BalMonth;

DECLARE @DynSQL NVARCHAR(MAX),
        @months NVARCHAR(MAX);

SET @months = 'CONCAT( CONVERT(nvarchar(15), YEAR(BalDate)) , IIF(LEN(MONTH(BalDate)) > 1, CONVERT(nvarchar(15), MONTH(BalDate)), ''0'' + CONVERT(nvarchar(15), MONTH(BalDate)))) AS BalMonth'
SET @DynSQL = 'SELECT *
               FROM
                   (SELECT 
                        a1.IDNbr, 
                        a2.CustName, ' + @months + ',
                        AVG(a1.Balance) as Balance
                    FROM tlbID a1 
                    INNER JOIN tblCust a2 ON (a1.IDNbr = a2.IDNbr)
                    WHERE a1.Balance != 0.00 
                      AND a2.CustType != ''Inactive''
                    GROUP BY 
                        a1.IDNbr, a2.CustName, CONCAT( CONVERT(nvarchar(15), YEAR(BalDate)) , IIF(LEN(MONTH(BalDate)) > 1, CONVERT(nvarchar(15), MONTH(BalDate)), ''0'' + CONVERT(nvarchar(15), MONTH(BalDate))))) as d1
PIVOT (
    AVG(Balance)
    FOR BalMonth IN (' + @Dates + ')
) piv';


EXECUTE sp_executesql @DynSQL
user_15562420
Related