something like this should work,
select t1.patnumbr, t1.rdate, t1.rtime +
from table1 t1, table2 t2 +
where t1.patnumbr = t2.patnumbr and t1.rdate = t2.rdate and t1.rtime =
t2.rtime +
group by patnumbr, rdate, rtime +
having t1.totalcharge <> Sum(t2.charge)
-- Dennis McGrath
mailto:[EMAIL PROTECTED]
-- Productivity Tools for R:Base Programmers
http://www.enteract.com/~mcgrath/dennis
-- Full time consultant with:
SQL Resources Group
Steve Hartmann
Oak Park, IL
mailto:[EMAIL PROTECTED]
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of MJS
Sent: Tuesday, September 18, 2001 1:36 PM
To: [EMAIL PROTECTED]
Subject: Attention Select Wizards
I have 2 tables. The first has 4 columns, patnumbr, rdate rtime, and
totalcharge
(integer, date, time, and currency). It is linked to a second table in a 1
to many
relationship, where the columns are nearly the same....patnumbr, rdate,
rtime, and
charge. Each row in the first table is linked to a group of rows in the
second
table. The matching columns are the 1st 3 columns. As you might guess, the
sum of
the charges in the second table is the value of totalcharge in the first
table. Now
say something happens to corrupt the data, where the sum of the charges in
the second
table does not match the value in totalcharge, and I need to find out which
row in the
first table does not have the right value for totalcharge. Is there some
really cool
select statement, with group by and having or other forms of SQL magic that
will find
all off the rows in the first table that don't have the correct value in the
totalcharge column???? It's easy to do with a declare cursor, but I was
hoping that a
select statement would work!
TIA!
Mike Sinclair
