Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

help with difficult situation Expand / Collapse
Author
Message
Posted Thursday, October 11, 2012 4:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

Post #1371819
Posted Thursday, October 11, 2012 4:41 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:32 AM
Points: 40,445, Visits: 36,899
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

Post #1371826
Posted Thursday, October 11, 2012 4:46 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, October 24, 2014 9:55 AM
Points: 1,485, Visits: 1,036
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

Post #1371827
Posted Thursday, October 11, 2012 5:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.
Post #1371830
Posted Thursday, October 11, 2012 5:39 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:44 PM
Points: 3,939, Visits: 8,936
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1371834
Posted Thursday, October 11, 2012 6:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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?
Post #1371842
Posted Thursday, October 11, 2012 6:42 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:44 PM
Points: 3,939, Visits: 8,936
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1371844
Posted Thursday, October 11, 2012 6:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.
Post #1371847
Posted Thursday, October 11, 2012 7:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:44 PM
Points: 3,939, Visits: 8,936
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1371849
Posted Thursday, October 11, 2012 7:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.
Post #1371850
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse