January 30, 2008 at 8:58 am
I'm writing a query comparing the join_date of our members with the subscriptions.payment_date. Technically they should be the same and I'm trying to find the records where they are not. The problem lies in that I'm getting all the payment dates and not the first payment date that should correspond to the join date. The existing query is pasted below.
So how do I get it to display the oldest subscriptions.payment date and not all the others?
Many thanks!
select name.id,name.join_date,name.paid_thru,subscriptions.payment_date from name,subscriptions
where member_type like '%m%'
and name.paid_thru is null
and name.id = subscriptions.bt_id
and subscriptions.product_code like 'duesm%'
or
member_type like 'm%'
and name.paid_thru <> subscriptions.payment_date
and name.id = subscriptions.bt_id
and subscriptions.product_code like 'duesm%'
order by name.id,subscriptions.payment_date,name.paid_thru
January 30, 2008 at 9:18 am
You can use "EXCEPT" do return all rows that exist in Name that do not exist in subscription. Using the following query will return all the Names and Join dates from [Name] where there are no matches in subscriptions.
SELECT Name.ID, Name.Join_Date
FROM Name
EXCEPT
SELECT subscriptions.bt_id, subscriptions.payment_date
FROM subscriptions
January 30, 2008 at 10:14 am
I've been working with the information you supplied, thank you but I'm just not getting what I think I need. I really only need this line:
and name.paid_thru <> subscriptions.payment_date
to work llike
name.paid_thru <> ((select min)(subscriptions.payment_date))
but that just doesn't seem to be working correclty.
Does that make sense?
January 30, 2008 at 10:38 am
Something like this?
SELECT *
FROM Name a
INNER JOIN subscriptions b ON a.NameID = b.bt_id AND a.Join_Date <>
(SELECT MIN(c.payment_date) FROM subscriptions c WHERE c.bt_id = a.NameId)
January 30, 2008 at 10:49 am
Another method:
SELECT a.NameId,
MIN(a.Join_Date) AS [Join_Date],
MIN(b.payment_date) AS [Payment_Date]
FROM Name a
INNER JOIN subscriptions b ON a.NameID = b.bt_id
GROUP BY a.NameId
HAVING MIN(a.Join_Date) <> MIN(b.payment_date)
January 30, 2008 at 11:05 am
that's got it, thank you for your help.
January 30, 2008 at 11:17 am
NP. Thanks for the feedback.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply