David,
Try this SELECT:
SELECT COUNT(*) INTO vCountMoreThanOnce +
FROM YP t1, SERVICE t2 +
WHERE t1.YPID = t2.YPID +
AND t2.COSTCENT = .GVPROG +
AND t2.DSERV BETWEEN .GVDATE1 AND .GVDATE2 +
AND t1.YPID IN +
(SELECT DISTINCT YPID +
FROM EV +
GROUP BY YPID +
HAVING COUNT(*) > 1)
Regards,
Rommel
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of David Ebert
Sent: Wednesday, March 27, 2002 12:57 PM
To: [EMAIL PROTECTED]
Subject: Relational counting
I have a parent table of clients with a primary key of ypid. There is a
child table of client admissions with a foreign key of ypid, and a second
child table of services also with a foreign key of ypid. I'm trying to
count the number of clients appearing in the admission table more than once
by the program value for individuals receiving services in a date range from
the service table.
This gives me the initial value (total clients):
SELECT COUNT(*) INTO VCOUNTALL INDICATOR vctallind +
FROM YP WHERE YPID IN (SELECT YPID FROM SERVICE WHERE COSTCENT = .GVPROG +
AND DSERV BETWEEN .GVDATE1 AND .GVDATE2)
This is how I'm asking the second part:
SELECT COUNT(ypid) INTO VCOUNT2 INDICATOR VINDmult +
FROM yp WHERE ypid IN (SELECT ypid FROM service +
WHERE costcent = .gvprog AND dserv BETWEEN .gvdate1 AND .gvdate2) +
--This is where I think my query falls down.
and ypid IN (SELECT count(ypid) FROM ev +
HAVING COUNT(ypid) > 1 )
My goal being to subtract vcount2 from vcountall to get the number of
individuals admitted more than once in a period. The problem is that the
second query always returns 0.
Am I missing a third step? How can I identify the record count for a parent
table where the FK appears more than once in the Admissions table (EV) with
services in a date range in the Service table?
tia
Dave
[EMAIL PROTECTED]
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
