Return to Revisions

Here is the revised SQL I came up with

Below this code is a list of tips for writing better SQL.

ALTER PROCEDURE [dbo].[Stats] 
      @network VARCHAR(9) = NULL
    , @name VARCHAR(100) = NULL
    , @version VARCHAR(15) = NULL
    , @year INT = NULL
    , @month INT = NULL
    , @day INT = NULL
    , @from DATE = NULL
    , @to DATE = NULL
AS

BEGIN
    SET NOCOUNT ON;

    WITH
    contract_address_list
    AS
    (
        SELECT DISTINCT
            [contractaddress]
        FROM 
            [dbo].[game]
        WHERE 
            1=1
            AND [name] = @name
            AND [version] = @version
            AND [network] = @network
    )
    SELECT 
          GS.[contractaddress]
        , GS.[network]
        , GS.[rounds]
        , GS.[sessions]
        , GS.[handle]
        , GS.[hold]
        , GS.[year]
        , GS.[month]
        , GS.[day]
        , G.[name]
        , G.[version]
        , [Date] = DATEFROMPARTS(GS.[Year], GS.[Month], GS.[Day])
    FROM 
        [dbo].[gamestatsdaily] AS GS
        INNER JOIN [dbo].[game] AS G ON GS.[contractaddress] = G.[contractaddress]
        LEFT JOIN contract_address_list AS CAL ON CAL.[contractaddress] = GS.[contractaddress]
    WHERE 
        1=1
        AND (@name IS NULL OR CAL.[name] IS NOT NULL)
        AND (@network IS NULL OR (GS.network = UPPER(@network)) )
        AND (@year IS NULL OR GS.[Year] = @year )
        AND (@month IS NULL OR GS.[Month] = @month )
        AND (@day IS NULL OR GS.[Day] = @day )
        AND (
            @from IS NULL
            OR (
                    GS.[Year] >= DATEPART(year, @from)
                AND GS.[Year] <= COALESCE(DATEPART(year, @to), DATEPART(year, @from))
                AND GS.[Month] >= DATEPART(month, @from)
                AND GS.[Month] <= COALESCE(DATEPART(month, @to), DATEPART(month, @from))
                AND GS.[Day] >= DATEPART(day, @from)
                AND GS.[Day] <= DATEPART(day, @to)
                )
            )
    ORDER BY 
          GS.[year] ASC
        , GS.[month] ASC
        , GS.[day] ASC

END

Tips for your SQL

1. For formatting, I would download the following tool, Poor Man's T-Sql Formatter and on GitHub. I use it when I have to edit other developers code.

Here are the settings I used

2. I would put the commas in front to clearly define new columns. Versus code wrapped in multiple lines. It also makes trouble-shooting code easier.

3. If you put 1=1 at the top of a WHERE condition, it enables you to freely change the rest of the conditions when debugging a query. The SQL query engine will end up ignoring the 1=1 so it should have no performance impact. Reference

4. If you use common table expressions (CTE) in your SQL it helps with documentation. The expression name can then let other developers know why you used that expression e.g. contract_address_list or active_projects.

5. Use the COALESCE function to evaluate the expression in the WHERE clause for the DATEPARTS.

6. I find it more convenient to see column alias's referenced from the left side e.g. , [Date] = DATEFROMPARTS(GS.[Year], GS.[Month], GS.[Day])

7. Avoid using keywords as object names. Microsoft Reference

8. Check the Estimated Execution Plan Ctrl + L in Microsoft SQL Server Management Studio (SSMS) to check the performance of the query by pasting both SQL statements in a query window.

Here's an example.