September 25, 2017 at 3:12 pm
I want to write a query to calculate the year to date value for a given date:
Example: If the given orderdate is 9/18/2015,
I want the year to date range to be between 1/1/15 - 9/18/15.
I am writing a query where it is filtered by OrderDate:
Select Count(Qty)
From tblOrders
Where OrderDate = @OrderDate or any better way of doing it.
I need help pls!!!!
September 25, 2017 at 3:46 pm
rosarozina - Monday, September 25, 2017 3:12 PMI want to write a query to calculate the year to date value for a given date:
Example: If the given orderdate is 9/18/2015,
I want the year to date range to be between 1/1/15 - 9/18/15.
I am writing a query where it is filtered by OrderDate:
Select Count(Qty)
From tblOrders
Where OrderDate = @OrderDate or any better way of doing it.
I need help pls!!!!
There are some handy date functions at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/. This one gets you the start of the year for a give date.
SELECT DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0);
You said in your post that you wanted to query orders for a date range, but then your example showed where the order date was equal to a specific date. Using the description of what you wanted for a range, something like this should get you pointed in the right direction.
DECLARE @dtm Datetime = '09/18/2015';
SELECT COUNT(*)
FROM dbo.Orders
WHERE OrderDate >= DATEADD(year, DATEDIFF(year, 0, @dtm), 0)
AND OrderDate < @dtm;
September 25, 2017 at 4:03 pm
Note that it is CRITICALLY IMPORTANT that you do NOT wrap a function around a column in the WHERE clause!!! Do filtering like Ed shows, not by using things like YEAR(), MONTH(), etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 25, 2017 at 6:21 pm
TheSQLGuru - Monday, September 25, 2017 4:03 PMNote that it is CRITICALLY IMPORTANT that you do NOT wrap a function around a column in the WHERE clause!!! Do filtering like Ed shows, not by using things like YEAR(), MONTH(), etc.
Excellent point, Kevin. I never thought to bring it up. Thanks for making it.
September 26, 2017 at 8:14 am
@ Kevin,
You can't leave a statement like that hanging 🙂 You need to explain WHY it is a bad idea.
September 26, 2017 at 10:28 am
aaron.reese - Tuesday, September 26, 2017 8:14 AM@ Kevin,You can't leave a statement like that hanging 🙂 You need to explain WHY it is a bad idea.
Nah. Everyone should know who I am and just push the "I Believe" button!! 😎
Functions around columns in the WHERE clause carry several often horrifically bad performance implications. Among them are preventing acquisition of valid statistics, and when the optimizer is guessing BAD things can happen with your query plan. They also void the use of an index seek where it is appropriate, and the corresponding index scan (sometimes even a full table scan when combined with bad estimated row counts) carry several BAD things such as hammering the IO system, flushing useful hot pages out of the buffer pool (potentially crushing the entire SQL Server's performance - not just your database application), and the scan taking a shared read lock on the entire index thus dropping your concurrency in the toilet for that object. Add in the CPU burn from actually computing said function(s) on ALL rows, often combined with large HASH joins and your CPU takes a pounding as well.
Hopefully that is enough for those few that DON'T know who I am and push the "I Believe" button to jump through any hoops necessary to avoid functions around columns in the WHERE clause. Just in case it isn't, I will add that in my almost 25 years of consulting on SQL Server (and ONLY SQL Server) this issue is the #2 performance problem I have seen in aggregate (with mismatched datatypes being #1 and suboptimal indexing being a close #3).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 26, 2017 at 1:38 pm
TheSQLGuru - Tuesday, September 26, 2017 10:28 AMaaron.reese - Tuesday, September 26, 2017 8:14 AM@ Kevin,You can't leave a statement like that hanging 🙂 You need to explain WHY it is a bad idea.
Nah. Everyone should know who I am and just push the "I Believe" button!! 😎
Functions around columns in the WHERE clause carry several often horrifically bad performance implications. Among them are preventing acquisition of valid statistics, and when the optimizer is guessing BAD things can happen with your query plan. They also void the use of an index seek where it is appropriate, and the corresponding index scan (sometimes even a full table scan when combined with bad estimated row counts) carry several BAD things such as hammering the IO system, flushing useful hot pages out of the buffer pool (potentially crushing the entire SQL Server's performance - not just your database application), and the scan taking a shared read lock on the entire index thus dropping your concurrency in the toilet for that object. Add in the CPU burn from actually computing said function(s) on ALL rows, often combined with large HASH joins and your CPU takes a pounding as well.
Hopefully that is enough for those few that DON'T know who I am and push the "I Believe" button to jump through any hoops necessary to avoid functions around columns in the WHERE clause. Just in case it isn't, I will add that in my almost 25 years of consulting on SQL Server (and ONLY SQL Server) this issue is the #2 performance problem I have seen in aggregate (with mismatched datatypes being #1 and suboptimal indexing being a close #3).
Agreed. This is called a non-SARGable predicate and can occur in any predicate - WHERE or JOIN. If there's an NCI that would be available for use on the column, it's rendered unusable because the function's return value isn't stored in the index - the column's value is. The NCI could be used to save reads, but it must be a scan to read the entire leaf level before performing the function. The normally simple switching of logic can some with a huge savings in reads. Kevin already mentioned the additional CPU and flushing of the buffer pool.
September 26, 2017 at 3:44 pm
...If there's an NCI that would be available for use on the column, it's rendered unusable...
That is a common misstatement/misinterpretation Ed. The NCI is still usable, it just can't be SEEKed when it is appropriate to do so. The scan/hash plan that often results can be upwards of 5 or even 6 ORDERS OF MAGNITUDE less efficient than a seek/nested loop plan when few rows are actually affected/retrieved out of many.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 27, 2017 at 5:21 am
TheSQLGuru - Tuesday, September 26, 2017 3:44 PM...If there's an NCI that would be available for use on the column, it's rendered unusable...
That is a common misstatement/misinterpretation Ed. The NCI is still usable, it just can't be SEEKed when it is appropriate to do so. The scan/hash plan that often results can be upwards of 5 or even 6 ORDERS OF MAGNITUDE less efficient than a seek/nested loop plan when few rows are actually affected/retrieved out of many.
DOH! I even said that in my next statement. :crazy: Your correction is quite correct. Thanks, Kevin.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply