lbarnett419
asked on
Consolidate Codes and Dates in proper order
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...
Thank you!
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).
Thank you!
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,
DATEDIFF(day,effdate,
(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
Enterdateleavedates.xls
There is my blind shot code.
Without testing I can only guess that it works. Test it and come back with result if you want.
BTW: Structure you have looks wery strange. Start thinking about database redesign if you want to avoid problems in future.
Patrik
Without testing I can only guess that it works. Test it and come back with result if you want.
BTW: Structure you have looks wery strange. Start thinking about database redesign if you want to avoid problems in future.
Patrik
select a.lastname,a.firstname,a.middlename, a.effdate,a.entercode,b.effdate), b.leavecode,
DATEDIFF(day,b.effdate,a.effdate) AS NoDays
from Table1 a
JOIN Table1 b
ON b.lastname=a.lastname and b.firstname=a.firstname and b.effdate =(SELECT MIN(effdate) FROM Table1 c WHERE c.effdate>a.effdate)
order by a.lastname,a.firstname,a.middlename
ASKER
I tried the query(THANK YOU!) & changed the DATEDIFF(day,b.effdate,a.e ffdate) to (day,a.effdate,b.effdate) so the days would be positive days. Either way, I am only getting a subset of the records for each person. The John Smith record originally had:
LN FN EffDate EnterCode LeaveCode
Abbott Joseph 11/13/2006 E NULL
Abbott Joseph 12/19/2006 NULL W20
Abbott Joseph 2/6/2007 E NULL
Abbott Joseph 2/16/2007 NULL W10
Abbott Joseph 2/19/2007 E NULL
Abbott Joseph 3/22/2007 NULL W20
The query result was:
LN FN EffDate EnterCode EffDate LeaveCode NoofDays
Smith John 2/16/2007 NULL 2/19/2007 NULL 3
Any ideas?
LN FN EffDate EnterCode LeaveCode
Abbott Joseph 11/13/2006 E NULL
Abbott Joseph 12/19/2006 NULL W20
Abbott Joseph 2/6/2007 E NULL
Abbott Joseph 2/16/2007 NULL W10
Abbott Joseph 2/19/2007 E NULL
Abbott Joseph 3/22/2007 NULL W20
The query result was:
LN FN EffDate EnterCode EffDate LeaveCode NoofDays
Smith John 2/16/2007 NULL 2/19/2007 NULL 3
Any ideas?
I'll look on it.
But you send wrong example, mixed Joseph Abbot with John Smith.
Patrik
But you send wrong example, mixed Joseph Abbot with John Smith.
Patrik
I think mz query is ok, but restriction on start row is missing.
I suppose Enter row has LeaveCode NULL and Leave row has EnterCode NULL.
Try this one:
I suppose Enter row has LeaveCode NULL and Leave row has EnterCode NULL.
Try this one:
select a.lastname,a.firstname,a.middlename, a.effdate,a.entercode,b.effdate), b.leavecode,
DATEDIFF(day,b.effdate,a.effdate) AS NoDays
from Table1 a
JOIN Table1 b
ON b.lastname=a.lastname and b.firstname=a.firstname and a.LeaveCode IS NULL AND b.effdate =(SELECT MIN(effdate) FROM Table1 c WHERE c.effdate>a.effdate AND c.LeaveCode IS NOT NULL)
order by a.lastname,a.firstname,a.middlename
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please write comment why it is graded B. What was wrong on solution?
1) Use "fake" agregate. If you GROUP BY a.lastname,a.firstname,a.m
2) Better one. Convert subquery to JOIN and extract leave code there. I'll try to write it for you, give me a sec.
Patrik