오라클의 LEAD,LAG함수 MSSQL에선 이렇게 응용

사번	구분	일자	시간
32413	출근	2011-04-01	05:37
32413	퇴근	2011-04-01	18:10
32413	출근	2011-04-02	06:33
32413	퇴근	2011-04-02	14:12
32413	출근	2011-04-04	17:32
32413	퇴근	2011-04-05	06:14
32413	출근	2011-04-05	17:32
32413	퇴근	2011-04-06	06:13
32413	출근	2011-04-06	17:31
32413	퇴근	2011-04-07	06:14
32413	출근	2011-04-07	17:30
32413	퇴근	2011-04-08	06:15
32413	출근	2011-04-12	05:33
32413	퇴근	2011-04-12	18:10
32413	퇴근	2011-04-13	18:10
32413	출근	2011-04-14	05:34
32413	퇴근	2011-04-14	18:10
32413	출근	2011-04-15	05:35
32413	퇴근	2011-04-15	18:12
32413	출근	2011-04-16	05:27
32413	퇴근	2011-04-16	16:13
32413	출근	2011-04-18	17:28
32413	퇴근	2011-04-19	06:11
32413	퇴근	2011-04-19	18:25
32413	퇴근	2011-04-20	06:15
32413	출근	2011-04-20	17:34
32413	퇴근	2011-04-21	06:12
32413	출근	2011-04-21	17:28
32413	퇴근	2011-04-22	06:15
32413	퇴근	2011-04-22	06:15
32413	출근	2011-04-22	17:30
32413	퇴근	2011-04-23	06:12
32413	출근	2011-04-25	05:34
32413	퇴근	2011-04-25	16:21
32413	출근	2011-04-27	05:34
32413	퇴근	2011-04-27	18:11
32413	출근	2011-04-28	05:35
32413	퇴근	2011-04-28	18:09
32413	출근	2011-04-29	05:32
32413	퇴근	2011-04-29	18:08
32413	출근	2011-04-30	05:35

위와같이 표가 있을때 실수로 출근을 두번 찍거나 빠트린 경우와 반대로 퇴근을 두번찍거나 빠트린경우 ,즉 예외상황을 가정하여
사번,출근일,출근시간,퇴근일,퇴근시간 으로 데이터를 출력해보자.
여기서 예외상황이 발생하기 때문에 데이터가 빠진경우엔 널값으로 공백으로 출력하자.

결과는 이렇다.

사번	출근일	출근시간	퇴근일	퇴근시간
32413	NULL	NULL	2011-04-13	18:10
32413	NULL	NULL	2011-04-19	18:25
32413	NULL	NULL	2011-04-20	06:15
32413	NULL	NULL	2011-04-22	06:15
32413	2011-04-01	05:37	2011-04-01	18:10
32413	2011-04-02	06:33	2011-04-02	14:12
32413	2011-04-04	17:32	2011-04-05	06:14
32413	2011-04-05	17:32	2011-04-06	06:13
32413	2011-04-06	17:31	2011-04-07	06:14
32413	2011-04-07	17:30	2011-04-08	06:15
32413	2011-04-12	05:33	2011-04-12	18:10
32413	2011-04-14	05:34	2011-04-14	18:10
32413	2011-04-15	05:35	2011-04-15	18:12
32413	2011-04-16	05:27	2011-04-16	16:13
32413	2011-04-18	17:28	2011-04-19	06:11
32413	2011-04-20	17:34	2011-04-21	06:12
32413	2011-04-21	17:28	2011-04-22	06:15
32413	2011-04-22	17:30	2011-04-23	06:12
32413	2011-04-25	05:34	2011-04-25	16:21
32413	2011-04-27	05:34	2011-04-27	18:11
32413	2011-04-28	05:35	2011-04-28	18:09
32413	2011-04-29	05:32	2011-04-29	18:08
32413	2011-04-30	05:35	NULL	NULL

널값은 정렬이 안되는 관계로 맨 앞 또는 맨 뒤에 위치한다.
오라클인경우 데이터를 정렬하여 LEAD,LAG함수를 응용하여 SQL을 작성하면 되겠지만
MSSQL인경우 LEAD,LAG함수가 없기때문에 SQL로 작성하기에 난해한 면이 있다.
결과를 만들어낸 SQL문을 보자.

SELECT V.사번,V.출근일,V.출근시간,V.퇴근일,V.퇴근시간
FROM
 (
 SELECT A.사번,A.구분 출근,A.일자 출근일,A.시간 출근시간,B.구분 퇴근,B.일자 퇴근일,B.시간 퇴근시간
 FROM
 (
 select 사번,구분,일자,시간,ROW_NUMBER() OVER(PARTITION BY 사번 ORDER BY 일자,시간) 순번
 from 근태출력
 )A LEFT OUTER JOIN
 (
 select 사번,구분,일자,시간,ROW_NUMBER() OVER(PARTITION BY 사번 ORDER BY 일자,시간) 순번
 from 근태출력
 )B ON A.사번 = B.사번
 AND A.순번 + 1=B.순번
 AND B.구분 = '퇴근'
 WHERE A.구분 = '출근'
 AND A.사번 = '32413'
 
 UNION
 
 SELECT A.사번,B.구분,B.일자,B.시간,A.구분,A.일자,A.시간
 FROM
 (
 select 사번,구분,일자,시간,ROW_NUMBER() OVER(PARTITION BY 사번 ORDER BY 일자,시간) 순번
 from 근태출력
 )A LEFT OUTER JOIN
 (
 select 사번,구분,일자,시간,ROW_NUMBER() OVER(PARTITION BY 사번 ORDER BY 일자,시간) 순번
 from 근태출력
 )B ON A.사번 = B.사번
 AND A.순번 - 1=B.순번
 AND B.구분 = '출근'
 WHERE A.구분 = '퇴근'
 AND A.사번 = '32413'
 )V
 
ORDER BY V.사번,V.출근일,V.출근시간,V.퇴근일,V.퇴근시간

ROW_NUMBER함수를 이용해 순번을 정하여 outer조인을 하였다.
출근 또는 퇴근이 둘다 없거나 연속으로 데이터가 들어가 있는경우가 있기때문에 출근기준으로 outer조인을 한 SQL과 퇴근기준으로 outer조인을 한 SQL을  union 하였다. union은 중복행을 distinct 하기 때문에 모든 행이 다 나오게 된다. 맨밑에 order by 절은 사용하지 않아도 데이터는 정렬되어서 나온다. union을 사용하면 자동으로 정렬을 하기 때문이다.

근데 문제는...
null값이 있는 행들을 맨앞,맨뒤가 아닌 해당 일자와 시간에 맞도록 들어가있어야 할 위치에 나오게 하는 방법은 없을까... 하고 생각을 하고 있는중인데 답이 안나온다.ㅡㅡ

order by 문제를 해결했다.
역시나 순번을 가지고 응용을 했다.

SELECT V.사번,V.출근일,V.출근시간,V.퇴근일,V.퇴근시간
FROM
 (
 SELECT A.사번,A.구분 출근,A.일자 출근일,A.시간 출근시간,B.구분 퇴근,B.일자 퇴근일,B.시간 퇴근시간,A.순번
 FROM
 (
 select 사번,구분,일자,시간,ROW_NUMBER() OVER(PARTITION BY 사번 ORDER BY 일자,시간) 순번
 from 근태출력
 )A LEFT OUTER JOIN
 (
 select 사번,구분,일자,시간,ROW_NUMBER() OVER(PARTITION BY 사번 ORDER BY 일자,시간) 순번
 from 근태출력
 )B ON A.사번 = B.사번
 AND A.순번 + 1=B.순번
 AND B.구분 = '퇴근'
 WHERE A.구분 = '출근'
 AND A.사번 = '32413'
 
 UNION
 
 SELECT A.사번,B.구분,B.일자,B.시간,A.구분,A.일자,A.시간,A.순번
 FROM
 (
 select 사번,구분,일자,시간,ROW_NUMBER() OVER(PARTITION BY 사번 ORDER BY 일자,시간) 순번
 from 근태출력
 )A LEFT OUTER JOIN
 (
 select 사번,구분,일자,시간,ROW_NUMBER() OVER(PARTITION BY 사번 ORDER BY 일자,시간) 순번
 from 근태출력
 )B ON A.사번 = B.사번
 AND A.순번 - 1=B.순번
 AND B.구분 = '출근'
 WHERE A.구분 = '퇴근'
 AND A.사번 = '32413'
 )V
where (V.순번%2=0 or V.출근일 is null or V.퇴근일 is null)
ORDER BY V.순번

결과는 ..

사번	출근일	출근시간	퇴근일	퇴근시간
32413	2011-04-01	05:37	2011-04-01	18:10
32413	2011-04-02	06:33	2011-04-02	14:12
32413	2011-04-04	17:32	2011-04-05	06:14
32413	2011-04-05	17:32	2011-04-06	06:13
32413	2011-04-06	17:31	2011-04-07	06:14
32413	2011-04-07	17:30	2011-04-08	06:15
32413	2011-04-12	05:33	2011-04-12	18:10
32413	NULL	NULL	2011-04-13	18:10
32413	2011-04-14	05:34	2011-04-14	18:10
32413	2011-04-15	05:35	2011-04-15	18:12
32413	2011-04-16	05:27	2011-04-16	16:13
32413	2011-04-18	17:28	2011-04-19	06:11
32413	NULL	NULL	2011-04-19	18:25
32413	NULL	NULL	2011-04-20	06:15
32413	2011-04-20	17:34	2011-04-21	06:12
32413	2011-04-21	17:28	2011-04-22	06:15
32413	NULL	NULL	2011-04-22	06:15
32413	2011-04-22	17:30	2011-04-23	06:12
32413	2011-04-25	05:34	2011-04-25	16:21
32413	2011-04-27	05:34	2011-04-27	18:11
32413	2011-04-28	05:35	2011-04-28	18:09
32413	2011-04-29	05:32	2011-04-29	18:08
32413	2011-04-30	05:35	NULL	NULL

완성이다.