help with difficult situation
-
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
-
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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/[/url]
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
-
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.
-
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.
-
Why not use <= in the bottom phrase? Would this be considered a left outer join?
-
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.
-
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.
-
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.
-
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.
-
Please post your DDL, sample data and expected results based on the sample data.
It doesn't have to be actual data or the real names of your tables and fields. Just something that can help us to help you.
I don't see what you see and I don't know what is the first or second record.
-
How about this
--test ddl
declare @sales_log table(member_id varchar(50),company_id varchar(50),purchase_dt datetime,purchase_amt money)
insert into @sales_log(member_id,company_id,purchase_dt,purchase_amt)
select 'Jen9921', '222141','1/2/2011','11.99' union all
select 'Jen9921', '222141','2/2/2011','11.99' union all
select 'Jen9921', '222141','3/2/2011','11.99' union all
select 'Jen9921', '222141','4/2/2011','11.99' union all
select 'Jen9921', '222141','5/2/2011','11.99'
declare @membership_record table(member_id varchar(50),company_id varchar(50),enroll_id int,membership_effective_dt datetime,membership_term_dt datetime)
insert into @membership_record(member_id,company_id,enroll_id, membership_effective_dt,membership_term_dt)
select 'Jen9921', '222141',1,'1/1/2011','1/31/2011' union all
select 'Jen9921', '222141',2,'2/1/2011','2/28/2011' union all
select 'Jen9921', '222141',3,'3/1/2011','3/31/2011' union all
select 'Jen9921', '222141',4,'8/1/2011','8/30/2011' union all
select 'Jen9921', '222141',5,'9/1/2011','9/30/2011'
select * from @sales_log
where member_id ='Jen9921' and company_id='222141'
and purchase_dt not in(
select distinct T1.purchase_dt
from @sales_log T1
inner join @membership_record T2 on t1.member_id=t2.member_id and t1.company_id=t2.company_id
where T1.purchase_dt between T2.membership_effective_dt and T2.membership_term_dt
)
-
Yeah, that helps.
Now what, exactly do you want to see returned from that set of data (list rows and columns)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Here's a guess based upon the information folks have already extracted from the OP:
--test ddlDROP TABLE #sales_log
CREATE TABLE #sales_log (member_id varchar(50),company_id varchar(50),purchase_dt datetime,purchase_amt money)
insert into #sales_log(member_id,company_id,purchase_dt,purchase_amt)
select 'Jen9921', '222141','1/2/2011','11.99' union all
select 'Jen9921', '222141','2/2/2011','12.99' union all
select 'Jen9921', '222141','3/2/2011','13.99' union all
select 'Jen9921', '222141','4/2/2011','14.99' union all
select 'Jen9921', '222141','5/2/2011','15.99'
DROP TABLE #membership_record
CREATE TABLE #membership_record (member_id varchar(50),company_id varchar(50),enroll_id int,membership_effective_dt datetime,membership_term_dt datetime)
insert into #membership_record(member_id,company_id,enroll_id, membership_effective_dt,membership_term_dt)
select 'Jen9921', '222141',1,'1/1/2011','1/31/2011' union all
select 'Jen9921', '222141',2,'2/1/2011','2/28/2011' union all
select 'Jen9921', '222141',3,'3/1/2011','3/31/2011' union all
select 'Jen9921', '222141',4,'8/1/2011','8/30/2011' union all
select 'Jen9921', '222141',5,'9/1/2011','9/30/2011'
SELECT s.*, x.enroll_id
FROM #sales_log s
OUTER APPLY (
SELECT enroll_id
FROM #membership_record r
WHERE r.company_id = s.company_id
AND r.member_id = s.member_id
AND s.purchase_dt BETWEEN r.membership_effective_dt AND r.membership_term_dt
) x
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
I guess the sittuation is not so difficult to him anymore 🙂 oh well....
On the side note, GilaMonster in indonesian it means "Crazy Monster" as Gila=Crazy what you mean?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply