Gfrktyl

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).

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

DECLARE @year AS INT

SET @month = 1;

SET @year = 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;