August 9, 2007 at 1:42 pm
I am trying to modify an existing view to convert it to a monthly view from a "bi-weekly" view (replace "3/1/04" with getdate()). The query looks similar to this very simple example:
use
AdventureWorks
GO
Select C.CustomerID, C.AccountNumber, SUM(SOH.TotalDue)
FROM Sales.Customer AS C
LEFT OUTER JOIN Sales.SalesOrderHeader AS SOH
ON C.CustomerID = SOH.CustomerID
WHERE (DATEDIFF([Day], SOH.OrderDate, '3/1/04') BETWEEN - 15 AND 15)
GROUP BY C.AccountNumber, C.CustomerID
I understand what the DateDiff is doing, but I am not sure how using the "BETWEEN..." works and what it is being referenced to? Does it apply to the SOH.OrderDate?
The OrderDate can range (in this DB) from 7/1/01 to 7/31/04, yet it is only returning records where OrderDate is +/- 15 days from "3/1/04"? (I think I just answered my one of my questions just now...). Isn't there a more efficient way to do this query w/o using the DateDiff?
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
August 9, 2007 at 1:58 pm
It would probably help more if you explained what it is you are try to accomplish besides just modifying the code. An example of the expected output would also help.
August 9, 2007 at 2:12 pm
If you want to use any present index, use
WHERE SOH.OrderDate BETWEEN DATEADD(DAY, -15, '3/1/04') AND DATEADD(DAY, 15, '3/1/04')
N 56°04'39.16"
E 12°55'05.25"
August 9, 2007 at 4:11 pm
One more thing you can do to pick up a little bit more speed is to create variables containing DATEADD(DAY, -15, '3/1/04') and DATEADD(DAY, 15, '3/1/04'). This is assuming of course that '3/1/04' is going to be something like GetDate().
DECLARE @StartRange DateTime
DECLARE @EndRange DateTime
SET @StartRange = DATEADD(DAY, -15, '3/1/04')
SET @EndRange = DATEADD(DAY, 15, '3/1/04')
WHERE SOH.OrderDate BETWEEN @StartRange AND @EndRange
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply