Self Join on distinct field

[greg85374]

Ok..basically I have a table with a duplicated field i need to work with. I need to select all the distinct memno's from this table with the related ContributionAmt AND ContributionDate

My table has 4 fields:
PacEntryId ('Pk'),MemNo ('Fk'),ContibutionAmt, and ContributionDate

Now..I knwo I have
--Select Distinct(Memno) FROM TblPacContributions--2527 records

ANd in My related table I have
--Select count(Memno) From TblProspectMember --30331 records

Wich to me tells me If I want All related member info for ppl whom have contributed I should return:
30331
ANd for ppl whom have not contibuted:
30331-2527 =27804 non contributors

So I want all the distinct memberno's from TblPacCOntibutions AND
I need to know how to decide whom did or did not contribute..
I believe I get the DId not contribute using a left outer join:


Here is the cod eI tried for creating a vw of distinct member numbers from TblPacContributions with the memno's last contribution date:

SELECT TOP 100 PERCENT a.Memno, a.ContributionDate, a.ContributionAmt
FROM (SELECT DISTINCT Memno, ContributionDate, ContributionAmt
FROM TblPacContributions) a INNER JOIN
(SELECT DISTINCT Memno, ContributionDate, ContributionAmt
FROM TblPacContributions) b ON a.Memno = b.Memno AND a.ContributionDate <= b.ContributionDate
GROUP BY a.Memno, a.ContributionDate, a.ContributionAmt
HAVING (COUNT(*) <= 1)
ORDER BY a.Memno DESC


but this returns 2507 records

 

[hmckillop]

If what you want is to return a list of all users and the amount they have contributed along with last date try

SELECT memno, lastContrib, TotalPosts from TblProspectMember tb2
left join (
SELECT  DISTINCT(tb1.Memno) as newmemno, max(tb1.ContributionDate) as lastContrib, count(*) as TotalPosts-- a.ContributionAmt
FROM  TblPacContributions tb1
group by tb1.Memno) tb3 on tb3.newmemno = tb2.memno

"I'm living so far beyond my income that we may almost be said to be living apart

 

[johnherman]

Contributors:
select distinct (memberno) from TblPacContributions

Non-contributors"

select distinct (memno) from TblProspectMembers where memno not in (select distinct (memberno) from TblPacContributions)


-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[greg85374]

Getting Very Close..lol..Im looking for the following
But when I added the additional field(contributionAmt)
I got incorrect results again...
Either Way thanks much!!!

SELECT memno, LastContributionDate,ContributionAmt, TimesContributed from TblProspectMember tb2
left join (
SELECT DISTINCT(tb1.Memno) as newmemno,ContributionAmt, max(tb1.ContributionDate) as LastContributionDate, count(*) as TimesContributed
FROM TblPacContributions tb1
group by tb1.Memno,ContributionAmt) tb3 on tb3.newmemno = tb2.memno

 

[hmckillop]

What is contributionAmt, what data does it contain?
I thought thats what TotalRows in my example or TimesContributed in your example represented but obviously not. Do you wish to sum this, if so look below?

SELECT memno, lastContrib,  TotalPosts, ContributionAmt from TblProspectMember tb2
left join (
SELECT  DISTINCT(tb1.Memno) as newmemno, max(tb1.ContributionDate) as lastContrib, count(*) as TotalPosts, sum(tb1.ContributionAmt) as ContributionAmt
FROM  TblPacContributions tb1
group by tb1.Memno) tb3 on tb3.newmemno = tb2.memno

"I'm living so far beyond my income that we may almost be said to be living apart

 

[greg85374]

Thats IT...Thanks much Guys..Id this post on another forum for a week now...50 views and no replies...much appreciated