Finding most recent date?

Hi,
Using query analyzer on a SQL 2000 server. I'm having trouble even
describing what I want here... Let's try this, here's the query:
select distinct pt.patient_id, payer_org_id as 'Payer ID', org.name as
'Payer Description',
pp.plan_name as 'Plan Name', pt_policy.policy_number as 'Policy Number',
Rank, claim_number, posted_transaction_date
from patient as pt
left join pt_policy on pt_policy.patient_id = pt.patient_id
left join organizations as org on org.org_id = pt_policy.payer_org_id
left join payer_plan as pp on pp.sys_id = pt_policy.payer_plan_sys_id
left join chg_item on chg_item.pt_policy_sys_id = pt_policy.sys_id
left join charge_on_claim as coc on coc.chg_item_sys_id = chg_item.sys_id
right join claim on claim.sys_id = coc.claim_sys_id
where pt_policy.discontinued = 'F' and pt.patient_id = '100561'
order by claim_number, pt.patient_id, rank
For each claim_number there are multiple chg_items but I would like to
return just the most recent chg_item based on the posted_transaction_date.
I can't quite figure it out though, I tried a subquery on the where clause
but that reduced it to one item being returned. This query should actually
return 13 records but there are three repeats. Basically I want one record
for each claim_number.
I hope this makes sense and is enough information. Any help is greatly
appreciated.
Thanks in advance,
LinnPlease post a simplified version of table structures, sample data & expected
results. For details refer to: www.aspfaq.com/5006
In general, you can have an approach similar to:
( based on guesswork )
SELECT
FROM claim_tbl t1
WHERE t1._col = ( SELECT TOP 1 t2._col
FROM change_item_tbl t2
WHERE t2.claim_nbr = t1.claim_nbr
ORDER BY posted_transaction_date DESC ) ;
Anith|||you have to use a correlated subquery that has a where clause joining it bac
k
to the main query.
select id, patient, org, a_date
from patient a
where a_date=(select max(a_date) from patient b where b.patient=a.patient)
--
If it aint broke don't brake it.
"Linn Kubler" wrote:

> Hi,
> Using query analyzer on a SQL 2000 server. I'm having trouble even
> describing what I want here... Let's try this, here's the query:
> select distinct pt.patient_id, payer_org_id as 'Payer ID', org.name as
> 'Payer Description',
> pp.plan_name as 'Plan Name', pt_policy.policy_number as 'Policy Number',
> Rank, claim_number, posted_transaction_date
> from patient as pt
> left join pt_policy on pt_policy.patient_id = pt.patient_id
> left join organizations as org on org.org_id = pt_policy.payer_org_id
> left join payer_plan as pp on pp.sys_id = pt_policy.payer_plan_sys_id
> left join chg_item on chg_item.pt_policy_sys_id = pt_policy.sys_id
> left join charge_on_claim as coc on coc.chg_item_sys_id = chg_item.sys_id
> right join claim on claim.sys_id = coc.claim_sys_id
> where pt_policy.discontinued = 'F' and pt.patient_id = '100561'
> order by claim_number, pt.patient_id, rank
> For each claim_number there are multiple chg_items but I would like to
> return just the most recent chg_item based on the posted_transaction_date.
> I can't quite figure it out though, I tried a subquery on the where clause
> but that reduced it to one item being returned. This query should actuall
y
> return 13 records but there are three repeats. Basically I want one recor
d
> for each claim_number.
> I hope this makes sense and is enough information. Any help is greatly
> appreciated.
> Thanks in advance,
> Linn
>
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eCsxw%23DWGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Please post a simplified version of table structures, sample data &
> expected results. For details refer to: www.aspfaq.com/5006
> In general, you can have an approach similar to:
> ( based on guesswork )
> SELECT
> FROM claim_tbl t1
> WHERE t1._col = ( SELECT TOP 1 t2._col
> FROM change_item_tbl t2
> WHERE t2.claim_nbr = t1.claim_nbr
> ORDER BY posted_transaction_date DESC ) ;
> --
> Anith
>
Thanks much Anith and Joe, both solutions worked once I figured out the
from/join clauses I needed.
For those of you playing along at home, here's what I have at this point:
select distinct claim_number as 'Claim',
RTRIM(pt.last_name) + ', ' + ISNULL(RTRIM(pt.first_name), ' ') AS 'Patient',
payer_org_id as 'Payer ID', org.name as 'Payer Description',
pp.plan_name as 'Plan Name', pt_policy.policy_number as 'Policy Number',
( select min(start_date)
from claim as c1
left join charge_on_claim as coc on coc.claim_sys_id = c1.sys_id
left join chg_item on chg_item.sys_id = coc.chg_item_sys_id
where c1.claim_number = claim.claim_number ) as 'Start Date',
( select max(end_date)
from claim as c1
left join charge_on_claim as coc on coc.claim_sys_id = c1.sys_id
left join chg_item on chg_item.sys_id = coc.chg_item_sys_id
where c1.claim_number = claim.claim_number ) as 'End Date',
( select max(posted_transaction_date)
from claim as c1
left join charge_on_claim as coc on coc.claim_sys_id = c1.sys_id
left join chg_item on chg_item.sys_id = coc.chg_item_sys_id
where c1.claim_number = claim.claim_number ) as 'Transaction Date'
from patient as pt
left join pt_policy on pt_policy.patient_id = pt.patient_id
left join organizations as org on org.org_id = pt_policy.payer_org_id
left join payer_plan as pp on pp.sys_id = pt_policy.payer_plan_sys_id
left join chg_item on chg_item.pt_policy_sys_id = pt_policy.sys_id
left join charge_on_claim as coc on coc.chg_item_sys_id = chg_item.sys_id
right join claim on claim.sys_id = coc.claim_sys_id
where pt_policy.discontinued = 'F'
Once I figured out the posted_transaction_date it was easy to apply it to
two other date fields.
There is only one more wrinkle in my query and that is to total all the
amounts related to each claim. I have successfully compiled another query:
select claim.claim_number, sum(amount) as 'Claim Balance'
from claim
left join ar_detail on ar_detail.claim_sys_id = claim.sys_id
group by claim.claim_number
order by claim.claim_number
But my attempts to introduce this directly into the above query have so far
been unsuccessfull. If I make these queries into views I can easily combine
them but I'm suspecting there is a more elegant solution. My problem is
that this is a purchased database product and I'm not allowed to add my own
views or tables to the DB. So I'm using either Visual FoxPro or Excel to
build these reports.
I tried following the etiquette rules you referenced Anith but I prefer
making timely responses to follow up posts and with all the one-to-many
relations in this query I'll be assembling adequate sample data for a w.
I'll start working on it but if you have any suggestions based on what I've
already provided I'm willing to give them a try.
I thought it was possible to do a subquery in a join statement but I can't
figure out the syntax. Now that I write that I don't think that's the
correct approach anyways. When I added it to the fields list, similar to
the date fields, it simply totalled up all of the amounts instead of total
by claim number. Any suggestions are welcome.
Thanks again,
Linn