June 10, 2010 at 7:12 am
Hi. I have a DueDate field in a table. I would like to set this value in a SELECT to be the current date if less than current date.
Users then filter by DueDate Between x AND y. At present DueDates older than date being searched are not shown.
Example:
Record 1 has a DueDate of 01/05/2010
Record 2 has a DueDate of 17/06/2010
User filters on dates between 10/06/2010 and 10/07/2010. At present only record 2 will be returned.
I would like record 1 date converted to GETDATE() value.
Hope that makes sense.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 10, 2010 at 7:19 am
OK, I have come up with:
DECLARE @DueDate smalldatetime
SET @DueDate = GETDATE();
SELECT ..............
CASE
WHEN DueDate < GETDATE() THEN @DueDate
ELSE DueDate
END AS DueDate,
If there is a better way please let me know.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 10, 2010 at 9:25 am
You've got it. You can do the same type of thing in the WHERE clause. For example, using the data you mentioned in your original q:
DECLARE @filterStart DATETIME
DECLARE @filterEnd DATETIME
SET @filterStart = '10/06/2010'
SET @filterEnd = '10/07/2010'
SELECT ...
WHERE CASE WHEN DueDate < GETDATE() THEN GETDATE() ELSE DueDate END
BETWEEN @filterStart AND @filterEnd
Scott Pletcher, SQL Server MVP 2008-2010
June 10, 2010 at 12:42 pm
2Tall (6/10/2010)
Hi. I have a DueDate field in a table. I would like to set this value in a SELECT to be the current date if less than current date.Users then filter by DueDate Between x AND y. At present DueDates older than date being searched are not shown.
Example:
Record 1 has a DueDate of 01/05/2010
Record 2 has a DueDate of 17/06/2010
User filters on dates between 10/06/2010 and 10/07/2010. At present only record 2 will be returned.
I would like record 1 date converted to GETDATE() value.
Hope that makes sense.
Kind Regards,
Phil.
If you are filtering records with DueDate between 2010-06-10 and 2010-07-10, why would you want to see a record with a DueDate of 2010-05-01? Sorry, doesn't make any sense to me. You could just have the query return all records where DueDate < '2010-07-10' and get the same result.
June 10, 2010 at 5:41 pm
I agree with Lynn, all that is needed to get the exact same records is:
DECLARE @filterEnd DATETIME
SET @filterEnd = '10/07/2010'
SELECT ...
WHERE CASE WHEN DueDate < @filterEnd
scott.pletcher (6/10/2010)
You've got it. You can do the same type of thing in the WHERE clause. For example, using the data you mentioned in your original q:
DECLARE @filterStart DATETIME
DECLARE @filterEnd DATETIME
SET @filterStart = '10/06/2010'
SET @filterEnd = '10/07/2010'
SELECT ...
WHERE CASE WHEN DueDate < GETDATE() THEN GETDATE() ELSE DueDate END
BETWEEN @filterStart AND @filterEnd
Also, doing a CASE within the WHERE like this results in a non-SARG-able query. This isn't a huge deal if the engine would significantly pear down the results on a preferable index (for instance, something like a borrowerID), but otherwise (such as in this case) this will result in a table scan even if there is an index on the DueDate.
June 11, 2010 at 3:49 am
Hi. I can understand why it doesn't make sense. The query is destined for a report to mirror information returned for production scheduling information. Production scheduling works on the wip record due date. If the wip due date has passed the application moves the works order to the current date in the production scheduling program. However the wip due date record in the table retains the original date.
Initially my query was not reflecting the records displayed in the production scheduling program.
Hope that explains things.
Thanks for taking the time to view my post and to comment.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 11, 2010 at 9:45 am
but otherwise (such as in this case) this will result in a table scan even if there is an index on the DueDate.
You could rephrase the query to avoid CASE but you must still include all records before the current date, so I imagine it's almost certain you will do a full scan of a covering index/the table anyway.
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 9:50 am
Another option:
DECLARE @filterStart DATETIME
DECLARE @filterEnd DATETIME
SET @filterStart = '10/06/2010'
SET @filterEnd = '10/07/2010'
SELECT ...
WHERE
DueDate BETWEEN @filterStart AND @filterEnd
OR DueDate <= GetDate();
You will probably still get a table or clustered index scan with this query.
June 11, 2010 at 10:36 am
Lynn Pettis (6/11/2010)
Another option:
DECLARE @filterStart DATETIME
DECLARE @filterEnd DATETIME
SET @filterStart = '10/06/2010'
SET @filterEnd = '10/07/2010'
SELECT ...
WHERE
DueDate BETWEEN @filterStart AND @filterEnd
OR DueDate <= GetDate();
You will probably still get a table or clustered index scan with this query.
You are correct as usual Lynn. I tested this on a table with a couple hundred dates just to be sure, but as soon as the 'OR' is added the execution plan goes back to a scan.
June 11, 2010 at 10:40 am
bteraberry (6/11/2010)
Lynn Pettis (6/11/2010)
Another option:
DECLARE @filterStart DATETIME
DECLARE @filterEnd DATETIME
SET @filterStart = '10/06/2010'
SET @filterEnd = '10/07/2010'
SELECT ...
WHERE
DueDate BETWEEN @filterStart AND @filterEnd
OR DueDate <= GetDate();
You will probably still get a table or clustered index scan with this query.
You are correct as usual Lynn. I tested this on a table with a couple hundred dates just to be sure, but as soon as the 'OR' is added the execution plan goes back to a scan.
Thought of another option, but there will still be a scan in the execution plan:
DECLARE @filterStart DATETIME
DECLARE @filterEnd DATETIME
SET @filterStart = '10/06/2010'
SET @filterEnd = '10/07/2010'
SELECT ...
WHERE
DueDate BETWEEN @filterStart AND @filterEnd
UNION ALL
SELECT ...
WHERE
DueDate <= GetDate();
June 11, 2010 at 10:47 am
scott.pletcher (6/11/2010)
but otherwise (such as in this case) this will result in a table scan even if there is an index on the DueDate.
You could rephrase the query to avoid CASE but you must still include all records before the current date, so I imagine it's almost certain you will do a full scan of a covering index/the table anyway.
You are incorrect. If you have a table with an index on DueDate ...
create table dbo.SARGtest
(
testIDINT IDENTITY PRIMARY KEY,
DueDateDATETIME
)
create index ix_SARGEtest_DueDate on SARGtest(DueDate)
and you load the table with a few hundred dates and execute the following code ...
declare @arbitraryDate datetime
set @arbitraryDate = '6/01/10'
select * from dbo.SARGtest
where DueDate <= @arbitraryDate
you will get an index seek. Unless you are hitting a small table, if you are getting scans in your execution plan you should be trying to figure out what you're doing wrong.
For some background information (for those reading this not understanding what I'm talking about), this is a quick wiki definition of SARG-ability:
In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query (using index seeks, not covering indexes). The term is derived from a contraction of Search ARGument Able.
*edit typo
June 12, 2010 at 6:41 am
Great work guys, I really appreciate your time and efforts.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 14, 2010 at 8:05 am
Unless you are hitting a small table, if you are getting scans in your execution plan you should be trying to figure out what you're doing wrong.
That's wrong. It's not just the size of the table that matters, it's also -- and usually primarily -- the selectivity of the rows you request.
Also, btw, SQL Server can, and sometimes does, rewrite your query to make it more SARG-able. SS's getting better at it, although it has a long ways to go before equalling (IBM's) UDB, which has a truly great optimizer.
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply