troubleshooting Question

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

Avatar of jamesmetcalf74
jamesmetcalf74Flag for United States of America asked on
Microsoft SQL Server
5 Comments1 Solution112 ViewsLast Modified:
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
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros