Rewrite the statement

  • HI all,

    Decalre @day int

    Declare @date datetime = NULL

    SET @day = ( Select max(day1) FROM table1 WHERE (( @date IS NULL and date1 = (SELECT max(date1) from table1))

    OR (( @date IS NOT NULL and date1 = @date))

    ))

    When I execute this querey it runs around for more than 30 minutes but when I commented the code part

    OR (( @date IS NOT NULL and date1 = @date))

    It runs quickly.

    Is there any other method to rewrite this?

    Please suggest

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • IF (@date IS NULL)

    SET @day = (SELECT MAX(day1) FROM table1 WHERE date1 = (SELECT MAX(date1) FROM table1));

    ELSE

    SET @day = (SELECT MAX(day1) FROM table1 WHERE date1 = @date);

  • Select @day = max(day1)

    FROM table1

    WHERE date1 = ISNULL( @date, (SELECT max(date1) from table1))

  • Ken McKelvey (2/9/2016)


    IF (@date IS NULL)

    SET @day = (SELECT MAX(day1) FROM table1 WHERE date1 = (SELECT MAX(date1) FROM table1));

    ELSE

    SET @day = (SELECT MAX(day1) FROM table1 WHERE date1 = @date);

    I believe that this should perform better, but without data to test on....

    IF (@date IS NULL)

    SET @day = (SELECT TOP(1) day1 FROM table1 ORDER BY date1 DESC, day1 DESC);

    ELSE

    SET @day = (SELECT MAX(day1) FROM table1 WHERE date1 = @date);

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply