October 12, 2012 at 12:55 pm
Not like the OP cares much, but here's another option that might not have the best performance, but might be easier to understand for some people.
By the way, haiao, your query would show incorrect results.
SELECT *
FROM #sales_log
EXCEPT
SELECT s.*
FROM #sales_log s
JOIN #membership_record m ON s.member_id = m.member_id
AND s.company_id = m.company_id
AND s.purchase_dt >= m.membership_effective_dt
AND s.purchase_dt < m.membership_term_dt + 1
October 13, 2012 at 2:49 pm
haiao2000 thanks for your suggestion it ended up working. What i did was create a cursor and loaded it with each member_id then steped through the cursor and ran that query for each member.
October 13, 2012 at 3:21 pm
o1548324 (10/13/2012)
haiao2000 thanks for your suggestion it ended up working. What i did was create a cursor and loaded it with each member_id then steped through the cursor and ran that query for each member.
That's the same as the solution I posted - except, instead of an explicit cursor, it uses a table scan. Which will make it about a thousand times faster.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 14, 2012 at 9:11 am
It strikes me that maybe everyone who has answered so far has been trying to return the records not wanted, rather than the records wanted. So perhaps something like
select b.* from sales_log_tbl b
where not exists (
select * from membership_record_tbl a
where a.member_id = b.member_id and a.company_id = b.company_id
and b.purchase_dt between a.membership_effective_dt and a.membership_term_dt
)
That will list the purchases made when the purchaser didn't have membership.
Tom
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply