|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, October 17, 2012 3:49 PM
Points: 6,
Visits: 10
|
|
I am working on a project where i need to identify records from a "sales_log" table where the person who made the purchase did NOT have an active membership on the date of purchase, which is stored in the "membership" table. The problem i am having is that there are multiple enrollment segments for the member so my query will always be true because it matches at least one of the enrollment segments. I am having trouble describing my problem in words, if its not clear with the data and query please let me know and i will try to elaborate. Thanks.
Here is an example of some data:

Here is the query i have currently thats not working as desired:
select b.*, a.*
from membership_record_tbl a
INNER JOIN sales_log_tbl b on a.member_id = b.member_id and a.company_id = b.company_id where b.purchase_dt not between a.membership_effective_dt and a.membership_term_dt
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Yesterday @ 4:11 PM
Points: 37,741,
Visits: 30,020
|
|
For the data listed there, what should the results be?
Also, any chance you can post create table statements and the sample data as inserts (easier to use)?
Gail Shaw Microsoft Certified Master: SQL Server 2008, MVP SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter We stand on the bridge and no one may pass
|
|
|
|
|
UDP Broadcaster
      
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 8:25 AM
Points: 1,467,
Visits: 922
|
|
I think you need to re-arrange your query to find sales that had no valid Member, then list those memebers. but as GilaMonster states, Please create DDLs with temp tables, and a grid showing what you actually want. www.sqlservercentral.com/articles/Best+Practices/61537/ But this is going to return Multiple Rows and probably not what you want. SELECT z.*, t.purchase_dt FROM membership_record_tbl z INNER JOIN ( SELECT b.member_id, b.company_id, b.purchase_dt FROM sales_log_tbl b LEFT OUTER JOIN membership_record_tbl a ON 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 WHERE a.Member_id IS NULL)t ON z.member_id = t.member_id AND z.company_id = t.company_id
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, October 17, 2012 3:49 PM
Points: 6,
Visits: 10
|
|
The problem is it is company data so i do not feel comfortable posting it online. Here is the long version:
We received a list of "purchases" from ABC Inc. that they claim we owe them money for. The data they sent over has a member_id, company_id, purchase_dt, item_desc, units, billed_amt, paid_amt and some other fields which are not relevant. The members in the dataset they sent over must have an active membership with our company at the time of the purchase for us to actually owe ABC Inc. money. I have created a table which has all the enrollment records for members who use our company, similar to the membership_tbl in the picture of my first post but with some additional fields. I am trying to run through the dataset ABC Inc. gave us and tag all records where the member they claim made a purchase did not have an active membership with us at that time. This is what i was trying to accomplish with my query but the problem is; the date in the first record in the sales_log table is between the effective and term date in the first record of the membership table BUT it is also NOT between all the other records. I want the query to run the info in the sales_log table against the membership table records individually for each person. So..... if the sales log date is not between any of the members enrollment segement dates in the membership table, i want that record.
|
|
|
|
|
SSC Eights!
      
Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 960,
Visits: 1,924
|
|
From what I understood you need something like this:
SELECT a.* FROM sales_log_tbl a LEFT JOIN membership_record_tbl b ON a.member_id = b.member_id AND a.company_id = b.company_id AND a.purchase_dt >= b.membership_effective_dt AND a.purchase_dt < b.membership_term_dt + 1 --Do you know why you should use this? WHERE b.member_id IS NULL

I remember there are other ways you can do this but this is one way to accomplish.
Luis C. Please don't trust me, test the solutions I give you before using them. Forum Etiquette: How to post data/code on a forum to get the best help
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, October 17, 2012 3:49 PM
Points: 6,
Visits: 10
|
|
| Why not use <= in the bottom phrase? Would this be considered a left outer join?
|
|
|
|
|
SSC Eights!
      
Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 960,
Visits: 1,924
|
|
o1548324 (10/11/2012) Why not use <= in the bottom phrase? Because I'm adding one day to the membership_term_dt. I did this because I don't know the data types of your table. Usually, you would like to have the sales date with at least a datetime precision. Maybe you just want your membership with the date. So if you have a sales date like 2005-05-05 11:23:52.235 and a membership term date that is 2005-05-05. If you use between, the sales date won't be included because 2005-05-05 11:23:52.235 > (is greater than) 2005-05-05 00:00:00.000. If both columns are type date, then the BETWEEN will work, if not, you'll have problems (and I hope you're not using strings or numeric values ).
o1548324 (10/11/2012) Would this be considered a left outer join? Yes. OUTER is an optional keyword.
Luis C. Please don't trust me, test the solutions I give you before using them. Forum Etiquette: How to post data/code on a forum to get the best help
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, October 17, 2012 3:49 PM
Points: 6,
Visits: 10
|
|
| I tried your query but its not returning the right results, it seems like it is stopping after it hits the first records that meets the query conditions because the very first result is a member who had an active membership but it was the second enrollment segment when you query her ID in the table.
|
|
|
|
|
SSC Eights!
      
Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 960,
Visits: 1,924
|
|
o1548324 (10/11/2012) I tried your query but its not returning the right results, it seems like it is stopping after it hits the first records that meets the query conditions because the very first result is a member who had an active membership but it was the second enrollment segment when you query her ID in the table.
That's because I don't have anything to test with. With the sample data you posted, it should only return the row with the football. SQL Server won't "stop" after the first record that meets the conditions. It will bring all the set of rows that meet the conditions.
Luis C. Please don't trust me, test the solutions I give you before using them. Forum Etiquette: How to post data/code on a forum to get the best help
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, October 17, 2012 3:49 PM
Points: 6,
Visits: 10
|
|
| I'm sorry, the first record in the enrollment table did NOT meet the query criteria but the second row did, i think it is stopping on the first failure or at least thats the way it seems my results are coming back.
|
|
|
|