Need help with SQL stmt using Group By

[johncook]

I thought I had this the other day when AL helped me but not quite there.

2 tables:
Inmates - 1 rec per inmate
Acct_Events - 1 rec per inmate per status change. Status change events are "OPEN" when the inmate is created, "CLOSED" when the inmate is released, "OPEN" when the inmate is un-released, etc.

I am using surrogate keys: Inmates.cid is primary key; acct_events.cinmates_id is foreign key into inmates.cid

Basically I need to write SQL to select all inmates recs where LATEST acct_events record is "OPEN"

Here is what I have but, of course, it is including all acct_events recs where status is "OPEN" rather than ONLY inmates recs where LATEST acct_events is "OPEN".

SELECT Inmates.cid, Inmates.cinmate_number,;
MAX(acct_events.tdatetm) as dstatusdate;
FROM inmatetrustfund!inmates, inmatetrustfund!acct_events;
WHERE acct_events.cinmates_id = inmates.cid AND acct_events.cstatus = "OPEN";
GROUP BY Inmates.cid;
ORDER BY cname;
INTO CURSOR mycursor

Thanks. I've tried everything over the last few hours. I do need HELP!
John

 

[baltman]

Perhaps you first need to make a cursor with a cid that comprises all with an open and then use that list as a base for your query...

sele dist cinmates_id from acct_events ;
into cursor OpenCIDs where cstatus = "OPEN"

 

[johncook]

baltman,
Wouldn't there still be too many recs?

Basically the problem is: How do you select recs from a table, when you want ONLY the 1 record with the LATEST datetime value in field named tdatetm?

There can be many recs in the table for the same inmate (cid) which are "OPEN". The latest rec is the one I need, then of course, I need to also filter on "If the latest one has "OPEN" in the cstatus field".

 

[torturedmind]

john,

maybe you can try again using your own SELECT statement but modifying it a little, like so:

SELECT Inmates.cid, Inmates.cinmate_number,;
acct_events.tdatetm;
FROM inmatetrustfund!inmates, ;
inmatetrustfund!acct_events;
WHERE acct_events.cinmates_id = inmates.cid AND ;
acct_events.cstatus = "OPEN";
GROUP BY Inmates.cid ;
ORDER BY cname, acct_events.tdatetm;
INTO CURSOR mycursor

let me know if this helped.

torturedmind

 

[johncook]

tortured,
i don't mind if i have to change all of it,
BUT,
I still do not see where i would get ONLY the LATEST rec from acct_events.

Try a simpler scenario:
Table A: All recs have unique ID
Table B: Child of Table A.

Table A:
recid name
1 john
2 jack

Table B:
tablearecid status eventdate
1 OPEN 04/01/03
1 CLOSED 04/02/03
1 OPEN 04/03/03
1 CLOSED 04/04/03
2 OPEN 04/01/03

I need to select:
from table A, ONLY "jack"'s record

WHY? Because "jack" is the only rec in Table A where the latest rec from Table B is "OPEN".

Gotta run for the evening. Will check in the AM.
Thanks,
John

 

[1oldfoxman]

What about creating a cursor to hold the cid's with the status for the latest acct_events record like:

SELECT MAX(tdatetm),cinmates_id, cstatus ;
FROM acct_events ;
GROUP BY cinmates_id INTO CURSOR MyCursor

Then select from this cursor only those cid's whose status is "OPEN" as a subquery in your SELECT statement.

Hope this helps,
dennis

 

[baltman]

The trick is 'adding' closed and open as 1 and -1 and taking only non-zeros... assuming your data conforms to this assumption.

Brian

CLOSE ALL
CREATE TABLE tablea (recid n(1), name c(5))
APPEND blank
REPLACE name with "John"
APPEND blank
REPLACE name with "Jack"
REPLACE all recid with RECNO()

CREATE TABLE tableb (recid n(1), status c(7), eventdate d)
FOR x=1 to 5
APPEND BLANK
IF MOD(x,2)=0
REPLACE status with "Closed"
ENDIF
IF MOD(x,2)#0
REPLACE status with "Open"
ENDIF
REPLACE eventdate with {4/1/2003}+RECNO()-1
REPLACE recid with 1
ENDFOR
REPLACE eventdate with {4/1/2003}
REPLACE recid with 2

SELECT distinct tableb.recid,sum(IIF(ALLTRIM(tableb.status)=="Open",1,-1)) as currentstatus,;
MAX(eventdate) as eventdate group by 1 from tableb having currentstatus=1 into cursor temp

SELECT tablea.name,tablea.recid,temp.eventdate from tablea,temp ;
into cursor final where tablea.recid=temp.recid

BROWSE nowait

 

[baltman]

On a note other than your direct question, if I were you, I'd use datetime instead of date so that there'd be no chance of duplicates and then take the last status entry instead of relying on an open ALWAYS being matched to a closed...

Brian

 

[andre65]

I think this is what you want:

SELECT im.cid;
, im.cinmate_number;
, ae1.tdatetm;
FROM inmatetrustfund!inmates im;
, inmatetrustfund!acct_events ae1;
WHERE ae1.cinmates_id = im.cid;
AND ae1.tdatetm =;
( SELECT MAX(ae2.tdatetm);
FROM inmatetrustfund!acct_events ae2;
WHERE ae2.cinmates_id = im.cid;
AND ae2.cstatus="OPEN";
);
INTO CURSOR mycursor

 

[johncook]

I believe andre has the right approach. It does seem logical BUT I can not get it to work. I have tried about 50 variations but I get the error:
SQL: Queries of this type are not supported

????
John

 

[johncook]

Going on 2 days with no solution. Perhaps there is someone on this board that is a SQL guru for hire to help me get thru this one. If so, let me know.
John

 

[baltman]

If you copy and paste the code I posted, you'll see that it does exactly what you asked for. The whole 1st part simply makes your sample data tables. The real 'meat' are the two queries at the end.

You could likely make it one SQL statement instead of two using Andre's syntax, but there's no real benefit of that that I can see.

If you need something else, you'll need to make it clearer, or perhaps supply a larger data set and desired results.

Brian

 

[mkrausnick]

If you are applying the SQL to Foxpro tables rather than SQL Server or Oracle, you can't use a sub-select - VFP 7 and prior versions don't support it.

However, baltman's 2-statement approach is correct, and Andre65's SQL is also correct. So combine the two:

Perform Andre65's sub-select (the SELECT inside the parentheses), output to a cursor, then then the main SELECT selects from the cursor. You have to add the foreign key "cinmates_id " to the first select so you can match the dates with the inmates:

SELECT ae2.cinmates_id ,MAX(ae2.tdatetm) AS tdatetm;
FROM inmatetrustfund!acct_events ae2;
WHERE ae2.cinmates_id = im.cid;
AND ae2.cstatus="OPEN" ;
INTO CURSOR C1

SELECT im.cid;
, im.cinmate_number;
, c1.tdatetm;
FROM inmatetrustfund!inmates im, C1;
WHERE c1.cinmates_id = im.cid;

This should work. To avoid this mess in the future, you might consider keeping the current status (open or closed) in the inmate record, updating it every time you change the status, so you don't have to search the detail for it. Yes, it's not 3rd Normal Form, but it would considerably simplify processing.







Mike Krausnick

 

[johncook]

I am going to try this approach. thanks mike, brian and all. As for the "mess", it was NOT in 3rd normal form and I am changing it to 3rd normal form. Why? I did not have the audit trail necessary for some reporting. That's OK, we all have opinions. HAHA! I was a bit overwhelmed by Brian's approach but as I look at it more carefully, I do understand. I'll give it my best shot. Thanks again for all the assistance,
John

 

[andre65]

John,

The select statement in the WHERE clause is a valid statement supported by VFP. I just tried it again in the command window with some tables I have, and the statement was executed succesfully.

Gr,
Andre