Avatar of Jim Metcalf
Jim Metcalf
Flag for United States of America

asked on 

help with query - selecting only the most recent row by a date field

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.

any thoughts

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
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
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
Microsoft SQL Server

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon