I need to group a person with enterdates,entercodes,with
drawal dates and withdrawal codes in correct sequential (date) order. One person may have 10 records with sequential dates. See file. The 'Days Attended' field may not look right if you calculate the date diff...that's ok, I just need to put the number on the correct line for now. P.S. I don't create these files, just inherit them...
I started the query below, but don't have a clue how to put the E code & W codes on one line (see Results table example in xls file).
select a.lastname,a.firstname,a.middlename, effdate,entercode,leavecode,
(Select MIN(effdate) from Table1 b
where b.lastname=a.lastname and b.firstname=a.firstname and b.effdate > a.effdate)) NoDays
from Table1 a
order by a.lastname,a.firstname,a.middlename