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.delinquencyentryda
from Billing.ACCOUNTDELINQUENCY
join billing.account b on a.accountkey = b.accountkey
join billing.delinquencymilesto
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.RESPONSIBLEPARTYLO
where a.DELINQUENCYLEVEL = '5'
group by b.accountnumber, g.RESPONSIBLEPARTY,i.addr1
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.delinquencyentryda
from Billing.ACCOUNTDELINQUENCY
join billing.account b on a.accountkey = b.accountkey
join billing.delinquencymilesto
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.RESPONSIBLEPARTYLO
where a.DELINQUENCYLEVEL = '5'
group by b.accountnumber, g.RESPONSIBLEPARTY,i.addr1
lcohan
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.delinquencyentryda
from Billing.ACCOUNTDELINQUENCY
join billing.account b on a.accountkey = b.accountkey
join billing.delinquencymilesto
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.RESPONSIBLEPARTYLO
where a.DELINQUENCYLEVEL = '5'
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.delinquencyentryda
from Billing.ACCOUNTDELINQUENCY
join billing.account b on a.accountkey = b.accountkey
join billing.delinquencymilesto
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.RESPONSIBLEPARTYLO
where a.DELINQUENCYLEVEL = '5'
ASKER
Thanks Scott!
worked like a charm
worked like a charm
Great! Thanks for the feedback.