October 27, 2015 at 7:57 am
Hi, this query below is giving product join for me, is there a way to avoid this?
SELECT DISTINCT a.RevID, indexdate, transadate
FROM temp1 AS a
INNER JOIN temp2 AS d ON transdate BETWEEN indexdate-60 AND indexdate+60
)
October 27, 2015 at 8:02 am
Guessing its your between statement.
Any chance of table definitions, sample data and expected outcome?
October 27, 2015 at 8:13 am
i dont have much info but index date is nothing but minimum of transdate if that helps. what other alternatives i have for between if that is the one causing problem?
October 27, 2015 at 8:17 am
I'm guessing its due to the range, but could really do with sample data and expected outcome so that I can confirm
October 27, 2015 at 9:14 am
Tara-1044200 (10/27/2015)
Hi, this query below is giving product join for me, is there a way to avoid this?SELECT DISTINCT a.RevID, indexdate, transadate
FROM temp1 AS a
INNER JOIN temp2 AS d ON transdate BETWEEN indexdate-60 AND indexdate+60
)
As already stated you should provide some details so we aren't guessing. You also should use DATEADD instead of just using math on a datetime column. It ensures it will always work AND it more clear what you are doing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 30, 2015 at 2:05 pm
You didn't even indicate which table every columns comes from.
Is indexdate in temp1 or temp2?
Is transdate in temp1 or temp2?
Why do you need DISTINCT?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply