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

jamesmetcalf74
jamesmetcalf74 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
You need to use a MAX function on that particular datetime column to get the top or latest row id then retrieve rest of the data based on that matching id
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
instead of this:
join billing.sendbillto i on a.accountkey = i.accountkey
do this:
cross apply (
    select top (1) i2.*
    from billing.sendbillto i2
    where a.accountkey = i2.accountkey
    order by i2.effectivedate desc
) as i
or this:
join (
    select *, row_number() over(partition by accountkey order by effectivedate desc) as row_num
    from billing.sendbillto
) as i on i.accountkey = a.accountkey and i.row_num = 1


Depending on the table indexing, either could perform better.  For example, if the table is indexed on ( accountkey, effectivedate ), the first query should perform (much) better.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Do you really need to GROUP BY all those columns, or are you having to do that just because of the sum()?  If just the sum(), then do this instead:

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, 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 (
    select *, row_number() over(partition by accountkey order by effectivedate desc) as row_num
    from billing.sendbillto
) as i on i.accountkey = a.accountkey and i.row_num = 1
join (
    select accountkey, sum(tranamt) as tranamt
    from billing.accttran
    group by accountkey
) as j on j.accountkey = b.accountkey
join billing.RESPONSIBLEPARTYLOG k on e.idkey = k.idkey
where a.DELINQUENCYLEVEL = '5'

Author

Commented:
Thanks Scott!
worked like a charm
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Great!  Thanks for the feedback.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial