February 9, 2016 at 4:22 am
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/
February 9, 2016 at 5:18 am
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);
February 9, 2016 at 6:42 am
Select @day = max(day1)
FROM table1
WHERE date1 = ISNULL( @date, (SELECT max(date1) from table1))
_____________
Code for TallyGenerator
February 9, 2016 at 9:39 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply