Best Update

[lamago]

Hi,

I have a table with duplicate records of each time a student took a test. I want to group each student into a single record table, but I need it in a specific sequence. If they ever have a Pass then use that, else if they did not pass use the last not pass, else if they never took it, enter not taken into one field.

Im confused about the best way to do this, any suggestions?

 

[bborissov]

Can you post some data and desired results?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[lamago]

This is my Data

StudentID ELAPassed ELATestDate
1997000153.0 N 2001-03-13 00:00:00
1997000153.0 N 2002-03-05 00:00:00
1997000153.0 P 2003-05-13 00:00:00
1997000153.0 N 2002-07-23 00:00:00
1997000153.0 N 2002-11-05 00:00:00
1997000162.0 N 2005-09-13 00:00:00
1997000162.0 N 2005-05-10 00:00:00
1997000162.0 A 2005-03-15 00:00:00
1997000163.0 A 2005-03-15 00:00:00
1997000173.0 P 2004-03-16 00:00:00
1997000179.0 N 2005-03-15 00:00:00
1997000179.0 N 2005-09-13 00:00:00

I would like it to show,

StudentID ELASTATUS TestDate

1997000153.0 P 2003-05-13 'He's Passed
1997000162.0 N 2005-09-13 'Last time Not Passed
1997000163.0 A 2005-03-15 'No N or P, last Code
1997000173.0 P 2004-03-16 'He's Passed
1997000179.0 N 2005-09-13 'Last time Not Passed


Basically they are only cosidered as having taking it, if they have a code N or P. If they ever Passed, then I want the first time they passed. If they have never passed I want the last time they didnt pass. And finally if they have never Passed or not Passed I want the last code, A means Absent. This way I can tell why they never took it.

 

[bborissov]

create table #Test (StidendId INT, Passed char(1), TestDate DateTime)
insert into #Test values (1997000153,'N','2001-03-13 00:00:00')
insert into #Test values (1997000153,'N','2002-03-05 00:00:00')
insert into #Test values (1997000153,'P','2003-05-13 00:00:00')
insert into #Test values (1997000153,'N','2002-07-23 00:00:00')
insert into #Test values (1997000153,'N','2002-11-05 00:00:00')
insert into #Test values (1997000162,'N','2005-09-13 00:00:00')
insert into #Test values (1997000162,'N','2005-05-10 00:00:00')
insert into #Test values (1997000162,'A','2005-03-15 00:00:00')
insert into #Test values (1997000163,'A','2005-03-15 00:00:00')
insert into #Test values (1997000173,'P','2004-03-16 00:00:00')
insert into #Test values (1997000179,'N','2005-03-15 00:00:00')
insert into #Test values (1997000179,'N','2005-09-13 00:00:00')

SELECT DISTINCT #Test.StidendId, COALESCE(MyTest.Passed, NotPassed.Passed, NoInfo.Passed) AS Passed1,
				        COALESCE(MyTest.TestDate, NotPassed.TestDate, NoInfo.TestDate) AS TestDate1
FROM #Test
LEFT JOIN #Test MyTest    ON #Test.StidendId = MyTest.StidendId AND MyTest.Passed = 'P'
LEFT JOIN (SELECT StidendId, Passed, MAX(TestDate) AS TestDate FROM #Test WHERE Passed = 'N' GROUP BY StidendId, Passed)
          NotPassed ON #Test.StidendId = NotPassed.StidendId
LEFT JOIN #Test NoInfo ON #Test.StidendId = NoInfo.StidendId AND NoInfo.Passed = 'A'
DROP TABLE #Test

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[lamago]

Thanks SOOO much this is perfect