SQL Stored Procedure to export specified data

Question

I'm currently using a stored procedure(sproc) to export to Excel. The sproc is being passed two parameters for @month and @year. The parameters are then passed to a dynamic pivot table in order to display all the days of the month for the specified month and year.

It does currently work, however are there better ways I could improve my code in order for it be more efficient? Any help or advice would be greatly appreciated as I'm still a bit of a beginner with SQL (and C# for that matter).

Alter Procedure stpDateRange
-- Add parameters
@month int , @year int
As
Begin
Set NoCount ON

    DECLARE @Dates NVARCHAR(max);

SELECT @Dates = CONCAT(@Dates + ', ', QUOTENAME(BalDate)) 
FROM tblID
WHERE YEAR(BalDate) = @year
  AND MONTH(BalDate) = @month 
GROUP BY BalDate
ORDER BY BalDate;

DECLARE @DynSql NVARCHAR(max);
SET @DynSql = 'SELECT *
FROM
(
 SELECT a1.IDNbr , a2.[CustName] as [Name] , (CAST(a1.BalDate AS date)) as BalDate, a1.Balance
 FROM tblID a1
 RIGHT JOIN tblCust a2 ON (a1.IDNbr = a2.IDNbr)
 WHERE MONTH(a1.BalDate) = @month
   AND YEAR(a1.BalDate) = @year AND a2.CustType != ''Inactive'') as d1
PIVOT (
 Sum(Balance)
 FOR BalDate IN ('+ @Dates +')
) piv';

DECLARE @Params NVARCHAR(500) = N'@year int, @month int';  

-- SELECT @DynSql AS DynSql;

 EXECUTE sp_executesql @DynSql, @Params, @year = @year, @month=@month;

End

Test results (dates will continue until the last day of the month):

+-----------------------------------------------------------------+
| IDNbr | Name    | 1/1/2018 | 1/2/2018 | 1/3/2018 | 1/4/2018 | ..|
|-----------------------------------------------------------------|
| 52852 | CustOne | 52028.52 | 52038.59 | 52048.69 | 52058.89 | ..|
| 39512 | CustTwo | 95125.75 | 95225.75 | 95325.75 | 95425.75 | ..|
| 52852 | CustThr | 86225.95 | 87225.95 | 88225.95 | 89225.95 | ..|
| 52852 | CustFor | 12533.12 | 12543.12 | 12553.12 | 12563.12 | ..|
| 52852 | CustFiv | 69585.36 | 69685.36 | 69785.36 | 69885.36 | ..|
| ..... | ....... | ........ | ........ | ........ | ........ | ..|

Readability for the results isn't awful , so that wouldn't be a concern for me, especially with how small the program is that is running this. My main concern is the performance of the stored procedure (sproc).

Answer 1

You could always have 31 days by day number as column headers in the pivot. That way it would always be a consistent header and you wouldn't need to use dynamic SQL. Side note: Go K-State!

---

Results

Example SQL

DECLARE @month AS INT = 1;
DECLARE @year AS INT = 2018;

WITH
tblCust
AS
(
    SELECT tblCust.* FROM (VALUES
      ( 52852, 'CustOne', 'Active')
    , ( 39512, 'CustTwo', 'Active')
    , ( 52852, 'CustThr', 'Active')
    , ( 52853, 'CustFor', 'Active')
    , ( 52854, 'CustFiv', 'Active')
    ) tblCust ([IDNbr], [CustName], [CustType]) 
)
, 
tblID
AS
(
    SELECT tblID.* FROM (VALUES
      ( 52852, '01-Jan-2018', 52028.52)
    , ( 52852, '02-Jan-2018', 52038.59)
    , ( 52852, '03-Jan-2018', 52048.69)
    , ( 52852, '04-Jan-2018', 52058.89)
    , ( 39512, '01-Jan-2018', 95125.75)
    , ( 39512, '02-Jan-2018', 95225.75)
    , ( 39512, '03-Jan-2018', 95325.75)
    , ( 39512, '04-Jan-2018', 95425.75)
    , ( 52852, '01-Jan-2018', 86225.95)
    , ( 52852, '02-Jan-2018', 87225.95)
    , ( 52852, '03-Jan-2018', 88225.95)
    , ( 52852, '04-Jan-2018', 89225.95)
    , ( 52853, '01-Jan-2018', 12533.12)
    , ( 52853, '02-Jan-2018', 12543.12)
    , ( 52853, '03-Jan-2018', 12553.12)
    , ( 52853, '04-Jan-2018', 12563.12)
    , ( 52854, '01-Jan-2018', 69585.36)
    , ( 52854, '02-Jan-2018', 69685.36)
    , ( 52854, '03-Jan-2018', 69785.36)
    , ( 52854, '04-Jan-2018', 69885.36)
    ) tblID ([IDNbr], [BalDate], [Balance]) 
)
SELECT *
FROM
(
    SELECT 
           a1.[IDNbr] 
         , [Name] = a2.[CustName]
         , [BalDateMonth] = DATEFROMPARTS(@year, @month, 1)
         , [DayOfMonth] = DATEPART(d, a1.[BalDate])
         , a1.[Balance]
    FROM 
        tblID AS a1 
        RIGHT JOIN tblCust AS a2 ON (a1.[IDNbr] = a2.[IDNbr])
    WHERE 
        1=1
        AND MONTH(a1.[BalDate]) = @month
        AND YEAR(a1.[BalDate]) = @year 
        AND a2.[CustType] != 'Inactive') as d1
PIVOT 
(
    Sum([Balance])
    FOR [DayOfMonth] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) piv;