1
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ Can you post some example records? \$\endgroup\$ – aduguid Sep 23 '18 at 4:08
  • \$\begingroup\$ @aduguid Added test results! \$\endgroup\$ – Symon Sep 24 '18 at 13:26
1
\$\begingroup\$

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

screenshot

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;
\$\endgroup\$
  • \$\begingroup\$ I currently have it set to dynamic SQL due to the winForm that's ending parameters allows the user to select a month. Instead of creating a stored procedure for every month, I'm having it pull all the data dynamically, so it fills it on its own \$\endgroup\$ – Symon Sep 25 '18 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.