To find all of your activity for second quarter (months 4, 5 and
6),
you can create the following view:
CREATE VIEW vwSecondQuarter +
(Client_ID, Unit, Svc_Code, Svc_Date, Server_ID)
AS SELECT Client_ID, Unit, Svc_Code, Svc_Date, Server_ID +
FROM ServiceTable +
WHERE (IMON(Svc_Date)) IN (4,5,6)
CREATE VIEW vwThirdQuarter +
(Client_ID, Unit, Svc_Code, Svc_Date, Server_ID)
AS SELECT Client_ID, Unit, Svc_Code, Svc_Date, Server_ID +
FROM ServiceTable +
WHERE (IMON(Svc_Date)) IN (7,8,9)
Presumably there will not be any activity in the vwSecondQuarter unless
any service has been performed. Accordingly, the following will list
everyone for whom a svc_code of 122 must be performed in the third
quarter:
SELECT 'In 3rd Qtr, perform a Svc_Code 122 for Client ID: '=60,
Q2.Client_ID=8, Q2.Server_ID=8 +
FROM vwSecondQuarter Q2, vwThirdQuarter Q3 +
GROUP BY Q2.Client_ID, Q2.Server_ID +
WHERE (Q2.Client_ID = Q3.Client_ID) +
AND (Q2.Server_ID = Q3.Server_ID) +
AND ( (CTXT(Q2.Server_ID)) & (CTXT(Q2_ClientID)) NOT IN +
(SELECT (CTXT(Q3.Server_ID)) & (CTXT(Q3.Client_ID)) +
FROM vwThirdQuarter Q3 +
WHERE Q3.Svc_Code= '122' ) )
This code is a little more complex because it appears you have
Client_ID's which must be combined with the Server_ID in order to become
unique. In effect, your primary key is a combination of those two
columns.
------------------------------
The second set of information is a variation of the first:
SELECT 'In 3rd Qtr, perform a Svc_Code 150 for Unit 102 Client ID:
'=65, +
Q2.Client_ID=8, Q2.Server_ID=8 +
FROM vwSecondQuarter Q2, vwThirdQuarter Q3 +
GROUP BY Q2.Client_ID, Q2.Server_ID +
WHERE (Q2.Client_ID = Q3.Client_ID) +
AND (Q2.Server_ID = Q3.Server_ID) +
AND (Q2.Unit = 102) +
AND ( (CTXT(Q2.Server_ID)) & (CTXT(Q2_ClientID)) NOT IN +
(SELECT (CTXT(Q3.Server_ID)) & (CTXT(Q3.Client_ID)) +
FROM vwThirdQuarter Q3 +
WHERE Q3.Svc_Code= '150' ) )
See if this doesn't give you what you want, or enough ideas to
get your results.
Randy Peterson
"Plateautel.net" wrote:
> Can anyone help? Stupid question, huh? Please help! Working in a
> mental health facility, the clinicians are required to perform
> quarterly reviews on clients.I have a table [ServiceTable?] that
> contains Client_ID IntegerUnit IntegerSvc_Code
> textSvc_date dateServer_ID Integer A Svc_code of 122 is a
> quartely updateA svc_code of 150 is an ASI I need to find out two
> things If the client had any services, regardless of the unit, in the
> 2nd qtr, then a svc_code of 122 must be performed in the third qtr.I
> need the client_id and Server_id for any clients that have any
> services in the second qtr, but do not have a Svc_code of 122 in the
> third qtr. Also,If the client is assigned to unit 102 and has had any
> services in the 2nd qtr then a svc_code of 150 must be performed in
> the third qtr.I need the client_id and Server_id for any clients that
> are assigned to unit 102 and has services in the second qtr, but does
> not have a Svc_code of 150 in the third qtr. Please
