[ *  제가 드린 질문 ]
테이블은 아래와 같고 기본키는 (학교/학번/과목/시험방법) 입니다.
-------------------------------------------------------------------------------------
학교 /학번 /과목 /시험방법 /시험시작일 /시험상태
-------------------------------------------------------------------------------------
AAA   001   국어   A   2000-01-01   시험준비
AAA   001   국어   B   2000-03-03   시험준비
AAA   001   국어   C   2000-08-08   시험준비
AAA   001   국어   D   2000-11-11   시험준비
BBB   002   영어   A   2000-02-02   시험완료
BBB   002   영어   B   2000-04-01   시험준비
BBB   002   영어   C   2000-06-06   시험준비
BBB   002   영어   D   2000-08-08   시험준비
CCC   003   일어   A   2000-01-02   시험완료
CCC   003   일어   B   2000-01-03   시험준비
CCC   003   일어   C   2000-01-04   시험준비
CCC   003   일어   D   2000-01-05   시험준비
DDD   003   수학   A   2000-04-29   시험완료
DDD   003   수학   B   2000-05-04   시험완료
DDD   003   수학   C   2000-05-20   시험준비
DDD   003   수학   D   2000-05-30   시험준비
EEE   003   독어   A   2000-01-29   시험완료
EEE   003   독어   B   2000-02-01   시험완료
EEE   003   독어   C   2000-02-08   시험완료
EEE   003   독어   D   2000-02-28   시험완료
FFF   002   사회   A   2000-02-02   시험완료
FFF   002   사회   B   2000-04-01   시험완료
FFF   002   사회   C   2000-06-06   시험준비
FFF   002   사회   D   2000-08-08   시험준비
GGG   002   불어   A   2000-04-08   시험완료
GGG   002   불어   B   2000-05-09   시험완료
GGG   002   불어   C   2000-06-06   시험완료
GGG   002   불어   D   2000-08-08   시험완료
HHH   002   체육   A   2000-02-02   시험완료
HHH   002   체육   B   2000-04-01   시험중
HHH   002   체육   C   2000-06-06   시험준비
HHH   002   체육   D   2000-08-08   시험준비
------------------------------------------------------------------------------------

시험방법은 과목당 A, B, C, D 방법 네종류가 존재합니다.
따라서 한과목당 4개의 레코드가 항상존재합니다.
시험상태는 시험준비, 시험중, 시험완료 중 하나가 됩니다.
A방법 - (시험준비, 시험중, 시험완료) 중 하나
B방법 - (시험준비, 시험중, 시험완료) 중 하나 - A가 시험완료가 되어야 B가 시험중 , 시험완료로 입력가능
C방법 - (시험준비, 시험중, 시험완료) 중 하나 - B가 시험완료가 되어야 C가 시험중 , 시험완료로 입력가능
D방법 - (시험준비, 시험중, 시험완료) 중 하나 - C가 시험완료가 되어야 D가 시험중 , 시험완료로 입력가능
* 위 테이블에 국어 과목에 보시듯 테이블에 모든 레코드에 시작할때 4쌍에 대해
시험계획일자를 입력하고 상태는 시험준비상태로 모두 입력을 해 놓습니다.

이제 누군가 시험을 보기 시작했다면
위 테이블에 영어에서 보시듯 영어에 A시험방법을 완료했다면 상태값은 [시험완료]로 업데이트가 되고
B, C, D는 시험준비 그대로 입니다.
B시험이 진행중이라면 상태는 [시험중]이 됩니다. 아직 C, D는 아무것도 안했으니 시험준비로 남아 있게 됩니다.
이런 식으로 상태값은 진행을 합니다.
( * 중요한것은 A단계가 완료가 되어야 B단계가 진행 될 수 있고 C, D 역시 마찬가지입니다.
A시험방법이 완료가 안되었는데 B가 진행중이나 완료가 될 수 없고
B가 완료가 안되었는데 C가 진행중이나 완료가 될 수 없다는 뜻입니다.
각 시험방법은 순차적으로 진행이 됩니다.)

이제 하고 싶은작업은 어떤 사람이 특정일로 조회를 했을때
이 입력일을 기준으로 각 과목에 시험진행상태가 어떻게 되었는가 파악하는 부분입니다.
잘 진행되고 있는지 완료되었는지 지연되고 있는지입니다.
* 실제 시험의 완료란 시험방법 D 까지 완료가 되면 그것은 시험완료입니다.

결과값은 4쌍의 내용을 가로로 펼치고 전체상태만 판단해서 보여줍니다.
---------------------------------------------------------------------------------------------
학교...학번...과목...시험방법A상태...시험방법B상태...시험방법C상태...시험방법D상태...전체상태
---------------------------------------------------------------------------------------------
AAA...001...국어...시험준비...시험준비...시험준비...시험준비...지연
BBB...002...영어...시험완료...시험준비...시험준비...시험준비...시험준비
CCC...003...일어...시험완료...시험준비...시험준비...시험준비...지연
DDD...003...수학...시험완료...시험완료...시험준비...시험준비...시험준비
EEE...003...독어...시험완료...시험완료...시험완료...시험완료...시험완료
FFF...003...사회...시험완료...시험완료...시험준비...시험준비...시험준비
GGG...003...불어...시험완료...시험완료...시험완료...시험완료...시험완료
HHH...003...체육...시험완료...시험중...시험완료...시험완료...시험중
----------------------------------------------------------------------------------------------


어떤 사람이 2000-04-04 을 기준으로 조회를 했다면
국어 과목의 경우 "지연" 입니다.
A시험방법은 2000-01-01 ~ 2000-03-02 시험준비   <-- 2000-04-04기준으로 이미 완료여야 하는데 시험준비므로 "지연"
B시험방법은 2000-03-03 ~ 2000-08-07 시험준비   <-- 2000-04-04기준
C시험방법은 2000-08-08 ~ 2000-11-10 시험준비
D시험방법은 2000-11-11 ~  시험준비

위에 영어 과목은 "시험준비" 상태입니다. 
A시험방법은 2000-02-02 ~ 2000-03-31 시험완료   <-- 2000-04-04기준으로 차질없이 이미 완료되었으며
B시험방법은 2000-04-01 ~ 2000-06-05 시험준비   <-- 2000-04-04기준으로 A는 시험이끝났음. 차질없이 "시험준비"   
C시험방법은 2000-06-06 ~ 2000-08-08 시험준비
D시험방법은 2000-08-08 ~  시험준비

일어의 경우 "지연" 입니다.
A시험방법은 2000-01-02 ~ 2000-01-02 시험완료  
B시험방법은 2000-01-03 ~ 2000-01-03 시험준비   
C시험방법은 2000-01-04 ~ 2000-01-04 시험준비
D시험방법은 2000-01-05 ~  시험준비     <-- 2004-04-04 한참 전인데 아직도 완료되지 않았으므로 "지연"

수학의 경우 "시험준비" 입니다.
A시험방법은 2000-04-29 ~ 2000-05-03 시험완료  <-- 2004-04-04 이후이고 C, D 단계가 아직 준비중인게 있으므로 "시험준비" 
B시험방법은 2000-05-04 ~ 2000-05-19 시험완료   
C시험방법은 2000-05-20 ~ 2000-05-29 시험준비
D시험방법은 2000-05-30 ~  시험준비
  

독어의 경우 "시험완료" 입니다.
A시험방법은 2000-01-29 ~ 2000-01-31 시험완료 
B시험방법은 2000-02-01 ~ 2000-02-07 시험완료   
C시험방법은 2000-02-08 ~ 2000-02-27 시험완료
D시험방법은 2000-02-28 ~  시험완료    <-- 2004-04-04 모두 이전이고 D까지 완료이므로 "시험완료" 

사회의 경우는 시험준비중이겠지요
A시험방법은 2000-02-02 ~ 2000-03-31 시험완료
B시험방법은 2000-04-01 ~ 2000-06-05 시험완료  <-- 2004-04-04 기준으로완료되었고 C, D가 준비이므로 "시험준비"
C시험방법은 2000-06-06 ~ 2000-08-07 시험준비
D시험방법은 2000-08-08 ~  시험준비    

불어의 경우 "시험완료"입니다.
A시험방법은 2000-04-08 ~ 2000-05-08 시험완료  <-- 2004-04-04 이후 D까지 완료이므로 "시험완료"
B시험방법은 2000-05-09 ~ 2000-06-05 시험완료
C시험방법은 2000-06-06 ~ 2000-08-07 시험완료
D시험방법은 2000-08-08 ~  시험완료

체육 과목은 "시험중" 상태입니다. 
A시험방법은 2000-02-02 ~ 2000-03-31 시험완료   <-- 2000-04-04기준으로 차질없이 이미 완료되었으며
B시험방법은 2000-04-01 ~ 2000-06-05 시험중   <-- 2000-04-04기준으로 A는 시험이끝났음. 차질없이 "시험중"   
C시험방법은 2000-06-06 ~ 2000-08-08 시험준비
D시험방법은 2000-08-08 ~  시험준비


위와 같이 질문을 드렸더니 ANSI 표준으로 답을 받았습니다.
[ * 마농님이 주신 답변 ] ************************************************************************************
WITH t(학교, 학번, 과목, 시험방법, 시험시작일, 시험상태) AS
(
SELECT 'AAA', '001', '국어', 'A', '2000-01-01', '시험준비'
UNION ALL SELECT 'AAA', '001', '국어', 'B', '2000-03-03', '시험준비'
UNION ALL SELECT 'AAA', '001', '국어', 'C', '2000-08-08', '시험준비'
UNION ALL SELECT 'AAA', '001', '국어', 'D', '2000-11-11', '시험준비'
UNION ALL SELECT 'BBB', '002', '영어', 'A', '2000-02-02', '시험완료'
UNION ALL SELECT 'BBB', '002', '영어', 'B', '2000-04-01', '시험준비'
UNION ALL SELECT 'BBB', '002', '영어', 'C', '2000-06-06', '시험준비'
UNION ALL SELECT 'BBB', '002', '영어', 'D', '2000-08-08', '시험준비'
UNION ALL SELECT 'CCC', '003', '일어', 'A', '2000-01-02', '시험완료'
UNION ALL SELECT 'CCC', '003', '일어', 'B', '2000-01-03', '시험준비'
UNION ALL SELECT 'CCC', '003', '일어', 'C', '2000-01-04', '시험준비'
UNION ALL SELECT 'CCC', '003', '일어', 'D', '2000-01-05', '시험준비'
UNION ALL SELECT 'DDD', '003', '수학', 'A', '2000-04-29', '시험완료'
UNION ALL SELECT 'DDD', '003', '수학', 'B', '2000-05-04', '시험완료'
UNION ALL SELECT 'DDD', '003', '수학', 'C', '2000-05-20', '시험준비'
UNION ALL SELECT 'DDD', '003', '수학', 'D', '2000-05-30', '시험준비'
UNION ALL SELECT 'EEE', '003', '독어', 'A', '2000-01-29', '시험완료'
UNION ALL SELECT 'EEE', '003', '독어', 'B', '2000-02-01', '시험완료'
UNION ALL SELECT 'EEE', '003', '독어', 'C', '2000-02-08', '시험완료'
UNION ALL SELECT 'EEE', '003', '독어', 'D', '2000-02-28', '시험완료'
UNION ALL SELECT 'FFF', '002', '사회', 'A', '2000-02-02', '시험완료'
UNION ALL SELECT 'FFF', '002', '사회', 'B', '2000-04-01', '시험완료'
UNION ALL SELECT 'FFF', '002', '사회', 'C', '2000-06-06', '시험준비'
UNION ALL SELECT 'FFF', '002', '사회', 'D', '2000-08-08', '시험준비'
UNION ALL SELECT 'GGG', '002', '불어', 'A', '2000-04-08', '시험완료'
UNION ALL SELECT 'GGG', '002', '불어', 'B', '2000-05-09', '시험완료'
UNION ALL SELECT 'GGG', '002', '불어', 'C', '2000-06-06', '시험완료'
UNION ALL SELECT 'GGG', '002', '불어', 'D', '2000-08-08', '시험완료'
UNION ALL SELECT 'HHH', '002', '체육', 'A', '2000-02-02', '시험완료'
UNION ALL SELECT 'HHH', '002', '체육', 'B', '2000-04-01', '시험중'
UNION ALL SELECT 'HHH', '002', '체육', 'C', '2000-06-06', '시험준비'
UNION ALL SELECT 'HHH', '002', '체육', 'D', '2000-08-08', '시험준비'
)
SELECT 학교, 학번, 과목
, MIN(시험상태_A) 시험상태_A
, MIN(시험상태_B) 시험상태_B
, MIN(시험상태_C) 시험상태_C
, MIN(시험상태_D) 시험상태_D
, ISNULL(MAX(
   CASE WHEN 시험상태 != '시험완료'
    THEN CASE WHEN 시험종료일 <= '2000-04-04' THEN '지연'
  WHEN 시험종료일 > '2000-04-04' THEN 시험상태
  END
    END
   ), '시험완료') AS 시험상태
  FROM (
    SELECT 학교, 학번, 과목
, CASE 시험방법 WHEN 'A' THEN 시험상태 END AS 시험상태_A
, CASE 시험방법 WHEN 'B' THEN 시험상태 END AS 시험상태_B
, CASE 시험방법 WHEN 'C' THEN 시험상태 END AS 시험상태_C
, CASE 시험방법 WHEN 'D' THEN 시험상태 END AS 시험상태_D
, 시험상태
, 시험방법
, 시험시작일
, ISNULL(
   (SELECT TOP 1 b.시험시작일
FROM t b
    WHERE b.학교 = a.학교
  AND b.학번 = a.학번
  AND b.과목 = a.과목
  AND b.시험시작일 > a.시험시작일
    ORDER BY 과목
    ), '9999-12-31') AS 시험종료일
  FROM t a
    ) x
 GROUP BY 학교, 학번, 과목
 ORDER BY 학교, 학번, 과목
;

[ * 문제점 ] : 각 시험방법 A, B, C, D 의  일자가 겹치지 않을 경우 위에 로직은 잘 맞았습니다.
그런데 문제는 사용자가 일자를 같은 날로 모두 한꺼번에 넣을수도 있고 ,
A, B, C, D 시험방법에 일자가 중복될수도 있다고 합니다.

위에 주신 답변 쿼리에 데이타를 아래와 같이 바꾸어서 해 보면

WITH t(학교, 학번, 과목, 시험방법, 시험시작일, 시험상태) AS
(
  SELECT 'AAA', '001', '국어', 'A', '2000-01-01', '시험준비'  --> '2000-01-01'
UNION ALL SELECT 'AAA', '001', '국어', 'B', '2000-01-01', '시험준비'  --> '2000-01-01' 
UNION ALL SELECT 'AAA', '001', '국어', 'C', '2000-01-01', '시험준비'  --> '2000-01-01' 
UNION ALL SELECT 'AAA', '001', '국어', 'D', '2000-01-01', '시험준비'  --> '2000-01-01' 
UNION ALL SELECT 'BBB', '002', '영어', 'A', '2000-01-01', '시험완료'
UNION ALL SELECT 'BBB', '002', '영어', 'B', '2000-02-01', '시험중'  --> '2000-02-01'
UNION ALL SELECT 'BBB', '002', '영어', 'C', '2000-02-01', '시험준비'  --> '2000-02-01'
UNION ALL SELECT 'BBB', '002', '영어', 'D', '2000-02-01', '시험준비'  --> '2000-02-01'
UNION ALL SELECT 'CCC', '003', '일어', 'A', '2000-01-02', '시험완료'
UNION ALL SELECT 'CCC', '003', '일어', 'B', '2000-01-02', '시험완료'
UNION ALL SELECT 'CCC', '003', '일어', 'C', '2000-01-03', '시험중'
UNION ALL SELECT 'CCC', '003', '일어', 'D', '2000-01-03', '시험준비'
)

1.  조회 기준일을 '2000-04-04' 일로 했을 경우
-- 국어의 경우 4가지 시험방법일이 모두 '2000-01-01' 이므로 '지연' 이 나와야 하는데 '시험준비'가 나옵니다.
-- 영어의 경우 B, C, D 시험방법일이 '2000-02-01' 이므로 '지연'이 나와야 하는데 '시험중'이 나옵니다.
-- 일어의 경우도 '지연' 이 나와야 하는데 '시험중'가 나옵니다.

2. 조회기준일을 '2000-02-01' 일로 했을 경우
-- 국어의 경우 4가지 시험방법일을 모두 '2000-01-01' 이므로 '지연' 이 나와야 하는데 '시험준비'가 나옵니다.
-- 영어의 경우 '2000-02-01'로 이므로 '시험중' 이라고 제대로 나옵니다.
-- 일어의 경우 '지연'이 나와야 하는데 '시험중'이 나옵니다.

3. 조회기준일을 '2000-01-03' 로 했을 경우
-- 국어의 경우 4가지 시험방법일을 모두 '2000-01-01' 이므로 '지연' 이 나와야 하는데 '시험준비'가 나옵니다.
-- 영어의 경우 '2000-02-01'로 이므로 '시험중' 이라고 제대로 나옵니다.
-- 일어의 경우 '시험중'라고 제대로 나옵니다.

4. 검색일자를 '1999-01-01' 로 했을 경우는 이상이 없습니다.
검색일자 이전에 데이타 일자가 중복이 되는 경우 문제가 있는것 같습니다.

어떻게 고쳐보려고 했는데 답이 안보이네요 . 조언 부탁드립니다.