SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help with difficult situation


help with difficult situation

Author
Message
o1548324
o1548324
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86973 Visits: 45267
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 recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Ray M
Ray M
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2133 Visits: 1076
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
o1548324
o1548324
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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.
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16428 Visits: 19084
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
o1548324
o1548324
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 10
Why not use <= in the bottom phrase? Would this be considered a left outer join?
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16428 Visits: 19084
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
o1548324
o1548324
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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.
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16428 Visits: 19084
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
o1548324
o1548324
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search