i have a join with about ten tables. The following query works except i need one more filter or select statement to get this to work and i am unable to at this time.
in the table billing.sendbillto there is some data that is causing duplicates. it has a column called effectivedate. I need this query to return only the rows where the effective date is most current. ie... an account might have 3 or 4 sendbill to addresses. the effective date for the application tells the system which mailing address to use. I need this query to only pull one row per account and i need it to pull where i.effective date is the most recent date.
select b.accountnumber ,g.RESPONSIBLEPARTY ,i.addr1, i.addr2, i.city, i.state, i.zip, h.stno, h.stname, h.city AS City1, h.zip as zip1, f.dayphn,
e.fedtaxid, e.DRIVERLICENSENO, e.dob,a.delinquencyentrydate, sum(j.tranamt) as Balance_Owed, (select max(TRANDATE) from billing.ACCTTRAN z where z.ACCOUNTKEY = a.ACCOUNTKEY and TRANSACTIONDESIGNATOR = 10 and TRANSACTIONTYPE = 9)[lastpayment] , 'ACS', i.effectivedate
from Billing.ACCOUNTDELINQUENCY a
join billing.account b on a.accountkey = b.accountkey
join billing.delinquencymilestone c on a.MILESTONEKEY = c.MILESTONEKEY
join billing.acctcntc d on a.accountkey = d.accountkey
join resources.cntctid e on d.idkey = e.idkey
join resources.contact f on d.cntctkey = f.cntctkey
join billing.responsibleparty g on a.accountkey = g.accountkey
join property.address h on b.addrkey = h.addrkey
join billing.sendbillto i on a.accountkey = i.accountkey
join billing.accttran j on b.accountkey = j.accountkey
join billing.RESPONSIBLEPARTYLOG k on e.idkey = k.idkey
where a.DELINQUENCYLEVEL = '5'
group by b.accountnumber, g.RESPONSIBLEPARTY,i.addr1, i.addr2, i.city, i.state, i.zip,h.stno, h.stname, h.city, h.zip, f.dayphn, e.FEDTAXID, e.DRIVERLICENSENO, e.dob, a.DELINQUENCYENTRYdate, a.ACCOUNTKEY, i.EFFECTIVEDATE