Can you do something like...
select u.mr_no, u.county, count(*) as cnt
from tblUndup u, tblTransactions t
where u.mr_no = t.unitmrnum(+)
group by u.mr_no, u.county
?
I did this
create table tbl1 (
mr_no number primary key,
acct varchar2(25),
firstadmin date,
county varchar2(25)
)
create table tbl2 (
unitmrnum number,
account_num varchar2(25),
service_Date date
)
insert into tbl1 values (123, 'account1', sysdate - 90, 'countyA');
insert into tbl1 values (456, 'account2', sysdate - 90, 'countyB');
insert into tbl1 values (789, 'account3', sysdate - 90, 'countyC');
insert into tbl2 values (123, '123456789', sysdate - 60);
insert into tbl2 values (123, '234567890', sysdate - 45);
insert into tbl2 values (123, '345678901', sysdate - 30);
insert into tbl2 values (123, '456789012', sysdate - 15);
commit;
select t1.mr_no, t1.county, count(*) as cnt
from tbl1 t1, tbl2 t2
where t1.mr_no = t2.UNITMRNUM(+)
group by t1.mr_no, t1.county
and here are my results
MR_NO COUNTY CNT
123 countyA 4
456 countyB 1
789 countyC 1
Is this what you were looking for?
On 3/27/06, Ms. Judith Taylor <[EMAIL PROTECTED]> wrote:
> I'm trying to create a query that will pull a distinct instance of a
> transaction from my 'many'
> table that matches up with my 'one' table by a unique identifier - and I keep
> getting stuck.
>
> First table (my 'one'):
>
> tblUndup - MR_No, Acct, FirstAdmit, County
>
> Second table (the 'many'):
>
> tblTransactions - UnitMRNum, Account_Num, Service_Date
>
> What I'm trying to do:
>
> SELECT DISTINCT Service_Date
> FROM tblTransations
> WHERE MR_No = UnitMRNum
>
> The purpose is to perform a count of all MR numbers broken down by county and
> only on their first
> service date.
>
> Example output:
>
> MR: E00001234
> County: Athens
> Service_Date: 7-24-04 <- This being the first ever transaction in the
> Transactions table.
>
> MR: E00003254
> County: Meigs
> Service_Date: 7-24-04
>
> Total Count for Athens County: 24
> Total Count for Meigs County: 5
>
> Thing is, because of the Transactions table, each MR_No has multiple
> transactions, sometimes more
> than one on the same date. What I need to be able to do, is simply count each
> MR_No once on the
> first service date recorded in the Transactions table.
>
> Judith
> --
> Ms. Judith Taylor
> Appalachian Community Visiting Nurse Assoc.,
> Hospice and Health Services, Inc. 740.594.8226 http://www.acvna.org
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:15:1735
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/15
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:15
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
